這篇文章主要介紹了postgresql 中的參數查參數查看和修改方式，具有很好的和修參考價(jià)值，希望對大家有所幫助。改方一起跟隨小編過(guò)來(lái)看看吧。參數查

1.查看參數文件的和修位置

使用show 命令查看,比較常用的show config_file.此還可以(???)查看pg_settings數據字典.

test=# show config_file;
confi??g_file
------------------------------
/data/pgdata/p(′?_?`)ostgresql.conf
(1 row)
test=#ヾ(＾-＾)ノ show hba_file
test-# ;
hba_file
--------------------------
/data/pgdata/pg_hba.conf
(1 row)
t(′▽?zhuān)?est=# show ident_file ;
ident_file
--------(′▽?zhuān)?)--------------------
/data/pgdata/pg_(◎_◎;)ident.conf

2.查看當前會(huì )(hui)話(huà)的參數值

可以使用show命令或者查看pg_settings字典.

使用show all可以查看全部的參??數值.show 參數名查看指定參數

test=# show?? all;
------??----(′▽?zhuān)?-----------------(◎_◎;)----------+--------------------------------??----------------------+----------------------------------???-----------------------------------------------------------------------------------------ヽ(′ー｀)ノ----
allow_syste(′-ι_-｀)m_table_mods | off | Allows modifications of the structure of system tables.
app??lication_name | psql | Sets the application name to be( ?▽?) reported in?? statistics and lo(′?_?`)gs.
archive_command | test ! -f /data/archive/%f &??& cp %p /data/archive/%f | Sets the shell com??mand?? that will be called to archive a WAL file.
archive_mode | on | Allows archiving of WAL files using archive_command.
archive_timeout | 0 | Forces a switch to the next WAL file if a new file has?? not been( ???) started within N secon?ds.
array_nulls | on | Enable input of NULL elements in arrays.
...

test??=# show work_mem;
work_mem
----------
4MB
(1 row)

test=# \x
Expanded display is on.
test=# select * from pg_settings where name in ('work_mem')
te??st-# ;
-[ RECORD 1 ]---+------(′ω｀)----------------------------------------------------(′?ω?`)-----------------------------------(°ロ°) !---------ˉ\_(ツ)_/ˉ----------------
name | work_mem
setting | 4096
unit | kB
category | Resource Usage / Memory
short_desc | Sets the maximum memory to be used for query workspaces.
extra_desc | This much memory ca??n be used by each internal sort operation and hash table before switching to temporary disk files.
context | user
vヾ(′ω｀)?artype | integ??er
source | default
min_val | 64
max_val | 2147483647
enumvals |
boot_val | 4096
reset_val | 4096
sourcefile |
sourceline |
pending_( ?▽?)restart | f

3.修改pg的參數值

1.全局修改pg的參數.

有些參數只有當pg服務(wù)重啟的時(shí)候才生效,典型的例子就是shared_buffers,定義了共享內存的(?⊿?)大小.

對于9.5以后的版本,可以通過(guò)查看pg_file_settings查看你設置的參數是否生效.例如如果你設置了一個(gè)參數需要重啟數據庫才能生效或者設置錯誤,那么在此字典中會(huì )出現報錯.

tesヽ(′▽?zhuān)?ノt=# select * from pg_file_setting?s where error is not null;
sourcefile | sourceline | seqno | name | setting | applied | error
-----------------------------------+------------+----??---+--ヽ(′ー｀)ノ---------------+---------+---------+------------------------------
/data/pgdata/postgresql.auto.conf | 4 | 22 | max_connections | 10000 | f | setting could not be applied
(1 row)

對于9.4以后的版本,你還可以使用 alter system 命令修改參數.使用alter system命令將修改postgresql.??auto.conf文件,ヾ(?■_■)ノ而不ヾ(＾-＾)ノ是postgresql.conf,這樣可以很好的保護postgresql.conf文件,加入你使用很多alter system命令后搞的一團糟,那么你只需要刪除postgresql.auto.conf,再重新加載即可.

test=# show work_mem;
work_mem
-------(T_T)---
4MB
(ヽ(′ー｀)ノ1 row)
test=# alter system set work_mem='8MB';
ALTER SY(′_ゝ`)S??TEM
test=# show work_mem;
work_mem
----------
4MB
(1 row)

by the ALTER SYSTEM comman??d.
work_mem = '8MB'

使用pg_ctl reload重新load配置文件,再查看參數值:

test=# show work_mem ;
work_mem
----------
8MB
(1 row)

2.直接使(shi)用set命令,在會(huì )話(huà)層修改,修改之后將被用于未來(lái)的每一個(gè)(ge)事務(wù),只對當前會(huì )話(huà)有效:

test=#
test=# set work_mem='16MB';
SET
test=# show work_mem;
work_mem
----------
16MB
(1 row)

我們打開(kāi)另外一個(gè)會(huì )話(huà),查看work_mem參數,??可以發(fā)現work_mem還是4MB

p(′?ω?`)ostgres=# show work_mem;
w??o??rk_mem
----------
4M??B
(1 row)

3.set命令后添加 local關(guān)鍵字, 只在(zai)當前事務(wù)中修改,只在當前事務(wù)內有效??:

test=# show work_mem;
wor??k_mem
-(?_?;)---------
16MB
(1 row)
test=# begin;
BEGIN
test=# set local work_m??em='8MB';
SET
test=# show work_mem;
work_mem
--------(?⊿?)--
8MB
(1 row)
test=# commit;
COMMIT
test=# show work_mem;
work_mem
----------
16MB

4.使用 reset恢復參數的默認值

再pg_settings字典reset_val字段表示了如果使用reset,則此參數恢復的默認值為多少

使用 reset 參數名 來(lái)恢復某個(gè)參數的默??認值,使用 reset all來(lái)恢復所有的參數值(zhi).

test=# show work_mem;
work_mem
----------
16MB
(1 row)
test=# reset work_me??m;
RESET
test=# show work_mem;
work_mem
----------
4MB
(1 row)

test=# reset all;
RESET

5.為特定的用戶(hù)組設置參數

一.為特定的數據庫里的所有的用戶(hù)設置參數,例如為test數據庫所有的連接??設置work_mem為16MB:

test=# alter database test set work_mem='16MB';ALTER DATABASE

二.為數據庫中的某個(gè)特定用戶(hù)設置參數.例如為brent用戶(hù),設置work_mem為2MB:

postgres=# alter role brent set work_mem='2MB';ALTER ROLE

經(jīng)過(guò)測試發(fā)現,如果你同時(shí)為數據庫和用戶(hù)設置了特定參(can)數,那么以用戶(hù)為準.例如上面的,如果我用brent用戶(hù)??連接到test數據庫,那么我的work_mem應該為2MB:

postgres=# \c test brent
You are now connected to database "test" as user "brent".
test=??>
test=>
test=> show work_mem;
work_mem
----------
2MB

三.為某個(gè)特定用戶(hù)連接到特定的數據庫設置參數.例如為用戶(hù)brent在數據庫test中設置work_mem為8MB

test=# alter role brent in database test set work_mem='8MB';
ALTER ROLE

上(shang)面說(shuō)的三種設置,優(yōu)(O_O)先級遞增,也就是說(shuō),如果設置了1,2,3那么就以第3個(gè)為準,如果設置了1,2那么就是以2為準,以此類(lèi)推.

pg對此的實(shí)現方法和當用(yong)戶(hù)連接數據庫的時(shí)候,立刻手動(dòng)執行set命令的效果完全相同

test=# select name,setting,s(′?｀)ource from pg_settings where name='work_mem';
name | setting | source
----ヽ(′▽?zhuān)?ノ------+---------+----------
work_mem | 16384 | database

tes(′?ω?`)t=# \c test brent
You?? are nowヽ(′ー｀)ノ connected to database "test" as user "brent".
test=> select name,setting,source from pg_settings where name='work_mem';
name | setting | source
----------+---------+-------------(′ω｀)--
work_mem | 8192 | database use(°□°)r

補充：postgre(′；ω；`)sql重要參數解析及優(yōu)化

1，max_co??nnections 200

最大客戶(hù)端連接數。改方每個(gè)連接在后端都會(huì )對應相應的參數查進(jìn)程，耗費一定的和修內存資源。(′?｀)如果連接數上千，改方(fang)需要使用連接池工具。參數查

2，和修shared_buffers 25% of total memヽ(′ー｀)ノory

數據庫用于緩存數據的改方內存大小。該參數默認值很低（考??慮不同的參數查系統平臺），需要(′?ω?`)調整。和修不宜太大，改方很???多實(shí)踐表明，大于┐(′д｀)┌1/3的內存會(huì )降低性能。

3，effective_cache_size 50%-75% of total(?????) memory

This is a guideline for how much memory you expect to?? be available in the OS and PostgreSQL buffer caches, not an??? allocation! 這個(gè)參數只在(?_?;)查ヽ(′ー｀)ノ詢(xún)優(yōu)??化器選擇時(shí)使用，并不是實(shí)際分配的內存，該參數越大，查詢(xún)優(yōu)化器越傾向于選擇索引掃描。ヾ(?■_■)ノ

4，checkpoint_segments 256 checkpoint_completion_target 0.9

checkponit_segments wal個(gè)數達到多少個(gè)數checkponit，還有一個(gè)參數checkponit_timeout,控制最長(cháng)多長(cháng)時(shí)間checkpoint。對于寫(xiě)入比較大的(de)數據庫，該值越大越好。但是值越大，執行恢復的時(shí)間越長(cháng)。

checkpoint_completion_tar(?_?;)g??et 控制(zhi)checkponit write 分散寫(xiě)入，值越大越分散。默認值0.5，0.9是一個(gè)??比較合適的值。

5，work_mem

用于排序，默認值即可。每個(gè)連接都會(huì )分配一定work_mem,這個(gè)是會(huì )實(shí)際分配的內存，不宜(???)過(guò)大，默認值即可。如果要使用語(yǔ)句中有較大的排序操作，可以在會(huì )話(huà)級別設置??該參數，set work_men = ‘2GB',提高執行速度。

6，maintanance_work_mem

維護性操作使用的內存。例如(ru)：vacuum ,create index,alter table add foreign key,resto??ring database dumps.做這些操作時(shí)可以臨時(shí)設置該值大小，加快執行速度。set session mainta??nance_work_mem = ‘2GB';(′▽?zhuān)?

當使用較快的(de)存儲，如raid arrays，scsi，ssd時(shí)，可以適當調低該值。有利于優(yōu)化器懸??著(zhù)索引掃描。ssd(′_｀) 時(shí)，可以設置為2.

8，autovacuum

—maintenance_work_mem 1-2GB

—autovacuum_max_workers

如果有多個(gè)小型表，分配更多的workers，更少的mem。

大型表，更多的men，更少的workers。

文章來(lái)源：腳本之家

來(lái)源地址：https://www.jb51.net/article/204220.htm