postgresql 中的參數查看和修改方式

Posted on by

1.查看參數文件的位置

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

test=# show config_file;
     config_file     
------------------------------
 /data/pgdata/postgresql.conf
(1 row)
test=# show hba_file 
test-# ;
     hba_file     
--------------------------
 /data/pgdata/pg_hba.conf
(1 row)
test=# show ident_file ;
     ident_file     
----------------------------
 /data/pgdata/pg_ident.conf

2.查看當前會話的參數值

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

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

test=# show all;
-------------------------------------+------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------
 allow_system_table_mods       | off                         | Allows modifications of the structure of system tables.
 application_name          | psql                         | Sets the application name to be reported in statistics and logs.
 archive_command           | test ! -f /data/archive/%f && cp %p /data/archive/%f | Sets the shell command 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 seconds.
 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')
test-# ;
-[ 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 can be used by each internal sort operation and hash table before switching to temporary disk files.
context     | user
vartype     | integer
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服務重啟的時候才生效,典型的例子就是shared_buffers,定義瞭共享內存的大小.

許多參數在pg服務運行的時候就能修改.再更改之後像服務器執行一個reload操作,強制pg重新讀取postgresql.conf,因此你隻需要編輯postgresql.conf文件,再執行 pg_ctl reload 即可 . 對於需要重啟的,在修改完postgresql後需要執行 pg_ctl restart

對於9.5以後的版本,可以通過查看pg_file_settings查看你設置的參數是否生效.例如如果你設置瞭一個參數需要重啟數據庫才能生效或者設置錯誤,那麼在此字典中會出現報錯.

test=# select * from pg_file_settings 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 
----------
 4MB
(1 row)
test=# alter system set work_mem='8MB';
ALTER SYSTEM
test=# show work_mem;
 work_mem 
----------
 4MB
(1 row)

查看postgresql.auto.conf:

[postgres@postgresql1 pgdata]$ cat postgresql.auto.conf 
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
work_mem = '8MB'

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

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

2.直接使用set命令,在會話層修改,修改之後將被用於未來的每一個事務,隻對當前會話有效:

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

我們打開另外一個會話,查看work_mem參數,可以發現work_mem還是4MB

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

3.set命令後添加 local關鍵字, 隻在當前事務中修改,隻在當前事務內有效:

test=# show work_mem;
 work_mem 
----------
 16MB
(1 row)
test=# begin;
BEGIN
test=# set local work_mem='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 參數名 來恢復某個參數的默認值,使用 reset all來恢復所有的參數值.

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

5.為特定的用戶組設置參數

一.為特定的數據庫裡的所有的用戶設置參數,例如為test數據庫所有的連接設置work_mem為16MB:

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

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

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

經過測試發現,如果你同時為數據庫和用戶設置瞭特定參數,那麼以用戶為準.例如上面的,如果我用brent用戶連接到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

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

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

上面說的三種設置,優先級遞增,也就是說,如果設置瞭1,2,3那麼就以第3個為準,如果設置瞭1,2那麼就是以2為準,以此類推.

pg對此的實現方法和當用戶連接數據庫的時候,立刻手動執行set命令的效果完全相同

查看你當前的參數值是從何處指定,可以通過查詢pg_setttings中的source字段獲取,例如如果設置瞭database級別的參數.那麼查詢結果應該如下:

test=# select name,setting,source from pg_settings where name='work_mem';
  name  | setting | source 
----------+---------+----------
 work_mem | 16384  | database

其它的,例如設置瞭第三種:

test=# \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 user

補充：postgresql重要參數解析及優化

1，max_connections 200

最大客戶端連接數。每個連接在後端都會對應相應的進程，耗費一定的內存資源。如果連接數上千，需要使用連接池工具。

2，shared_buffers 25% of total memory

數據庫用於緩存數據的內存大小。該參數默認值很低（考慮不同的系統平臺），需要調整。不宜太大，很多實踐表明，大於1/3的內存會降低性能。

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! 這個參數隻在查詢優化器選擇時使用，並不是實際分配的內存，該參數越大，查詢優化器越傾向於選擇索引掃描。

4，checkpoint_segments 256 checkpoint_completion_target 0.9

checkponit_segments wal個數達到多少個數checkponit，還有一個參數checkponit_timeout,控制最長多長時間checkpoint。對於寫入比較大的數據庫，該值越大越好。但是值越大，執行恢復的時間越長。

checkpoint_completion_target 控制checkponit write 分散寫入，值越大越分散。默認值0.5，0.9是一個比較合適的值。

5，work_mem

用於排序，默認值即可。每個連接都會分配一定work_mem,這個是會實際分配的內存，不宜過大，默認值即可。如果要使用語句中有較大的排序操作，可以在會話級別設置該參數，set work_men = ‘2GB’,提高執行速度。

6，maintanance_work_mem

維護性操作使用的內存。例如：vacuum ,create index,alter table add foreign key,restoring database dumps.做這些操作時可以臨時設置該值大小，加快執行速度。set session maintanance_work_mem = ‘2GB’;

7,random_page_cost (默認值 4) seq_page_cost(默認值 1)

設置優化器獲取一個隨機頁的cost，相比之下一個順序掃描頁的cost為1.

當使用較快的存儲，如raid arrays，scsi，ssd時，可以適當調低該值。有利於優化器懸著索引掃描。ssd 時，可以設置為2.

8，autovacuum

—maintenance_work_mem 1-2GB

—autovacuum_max_workers

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

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

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方，望不吝賜教。

推薦閱讀：