postgresql 中的參數(shù)查看和修改方式
1.查看參數(shù)文件的位置
使用show 命令查看,比較常用的show config_file.此還可以查看pg_settings數(shù)據(jù)字典.
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.查看當(dāng)前會(huì)話的參數(shù)值
可以使用show命令或者查看pg_settings字典.
使用show all可以查看全部的參數(shù)值.show 參數(shù)名查看指定參數(shù)
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的參數(shù)值
1.全局修改pg的參數(shù).
有些參數(shù)只有當(dāng)pg服務(wù)重啟的時(shí)候才生效,典型的例子就是shared_buffers,定義了共享內(nèi)存的大小.
許多參數(shù)在pg服務(wù)運(yùn)行的時(shí)候就能修改.再更改之后像服務(wù)器執(zhí)行一個(gè)reload操作,強(qiáng)制pg重新讀取postgresql.conf,因此你只需要編輯postgresql.conf文件,再執(zhí)行 pg_ctl reload 即可 . 對(duì)于需要重啟的,在修改完postgresql后需要執(zhí)行 pg_ctl restart
對(duì)于9.5以后的版本,可以通過(guò)查看pg_file_settings查看你設(shè)置的參數(shù)是否生效.例如如果你設(shè)置了一個(gè)參數(shù)需要重啟數(shù)據(jù)庫(kù)才能生效或者設(shè)置錯(cuò)誤,那么在此字典中會(huì)出現(xiàn)報(bào)錯(cuò).
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)
對(duì)于9.4以后的版本,你還可以使用 alter system 命令修改參數(shù).使用alter system命令將修改postgresql.auto.conf文件,而不是postgresql.conf,這樣可以很好的保護(hù)postgresql.conf文件,加入你使用很多alter system命令后搞的一團(tuán)糟,那么你只需要?jiǎng)h除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配置文件,再查看參數(shù)值:
test=# show work_mem ; work_mem ---------- 8MB (1 row)
2.直接使用set命令,在會(huì)話層修改,修改之后將被用于未來(lái)的每一個(gè)事務(wù),只對(duì)當(dāng)前會(huì)話有效:
test=# test=# set work_mem='16MB'; SET test=# show work_mem; work_mem ---------- 16MB (1 row)
我們打開(kāi)另外一個(gè)會(huì)話,查看work_mem參數(shù),可以發(fā)現(xiàn)work_mem還是4MB
postgres=# show work_mem; work_mem ---------- 4MB (1 row)
3.set命令后添加 local關(guān)鍵字, 只在當(dāng)前事務(wù)中修改,只在當(dāng)前事務(wù)內(nèi)有效:
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恢復(fù)參數(shù)的默認(rèn)值
再pg_settings字典reset_val字段表示了如果使用reset,則此參數(shù)恢復(fù)的默認(rèn)值為多少
使用 reset 參數(shù)名 來(lái)恢復(fù)某個(gè)參數(shù)的默認(rèn)值,使用 reset all來(lái)恢復(fù)所有的參數(shù)值.
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.為特定的用戶組設(shè)置參數(shù)
一.為特定的數(shù)據(jù)庫(kù)里的所有的用戶設(shè)置參數(shù),例如為test數(shù)據(jù)庫(kù)所有的連接設(shè)置work_mem為16MB:
test=# alter database test set work_mem='16MB'; ALTER DATABASE
二.為數(shù)據(jù)庫(kù)中的某個(gè)特定用戶設(shè)置參數(shù).例如為brent用戶,設(shè)置work_mem為2MB:
postgres=# alter role brent set work_mem='2MB'; ALTER ROLE
經(jīng)過(guò)測(cè)試發(fā)現(xiàn),如果你同時(shí)為數(shù)據(jù)庫(kù)和用戶設(shè)置了特定參數(shù),那么以用戶為準(zhǔn).例如上面的,如果我用brent用戶連接到test數(shù)據(jù)庫(kù),那么我的work_mem應(yīng)該為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è)特定用戶連接到特定的數(shù)據(jù)庫(kù)設(shè)置參數(shù).例如為用戶brent在數(shù)據(jù)庫(kù)test中設(shè)置work_mem為8MB
test=# alter role brent in database test set work_mem='8MB'; ALTER ROLE
上面說(shuō)的三種設(shè)置,優(yōu)先級(jí)遞增,也就是說(shuō),如果設(shè)置了1,2,3那么就以第3個(gè)為準(zhǔn),如果設(shè)置了1,2那么就是以2為準(zhǔn),以此類推.
pg對(duì)此的實(shí)現(xiàn)方法和當(dāng)用戶連接數(shù)據(jù)庫(kù)的時(shí)候,立刻手動(dòng)執(zhí)行set命令的效果完全相同
查看你當(dāng)前的參數(shù)值是從何處指定,可以通過(guò)查詢pg_setttings中的source字段獲取,例如如果設(shè)置了database級(jí)別的參數(shù).那么查詢結(jié)果應(yīng)該如下:
test=# select name,setting,source from pg_settings where name='work_mem'; name | setting | source ----------+---------+---------- work_mem | 16384 | database
其它的,例如設(shè)置了第三種:
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
補(bǔ)充：postgresql重要參數(shù)解析及優(yōu)化
1，max_connections 200
最大客戶端連接數(shù)。每個(gè)連接在后端都會(huì)對(duì)應(yīng)相應(yīng)的進(jìn)程，耗費(fèi)一定的內(nèi)存資源。如果連接數(shù)上千，需要使用連接池工具。
2，shared_buffers 25% of total memory
數(shù)據(jù)庫(kù)用于緩存數(shù)據(jù)的內(nèi)存大小。該參數(shù)默認(rèn)值很低（考慮不同的系統(tǒng)平臺(tái)），需要調(diào)整。不宜太大，很多實(shí)踐表明，大于1/3的內(nèi)存會(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è)參數(shù)只在查詢優(yōu)化器選擇時(shí)使用，并不是實(shí)際分配的內(nèi)存，該參數(shù)越大，查詢優(yōu)化器越傾向于選擇索引掃描。
4，checkpoint_segments 256 checkpoint_completion_target 0.9
checkponit_segments wal個(gè)數(shù)達(dá)到多少個(gè)數(shù)checkponit，還有一個(gè)參數(shù)checkponit_timeout,控制最長(zhǎng)多長(zhǎng)時(shí)間checkpoint。對(duì)于寫入比較大的數(shù)據(jù)庫(kù)，該值越大越好。但是值越大，執(zhí)行恢復(fù)的時(shí)間越長(zhǎng)。
checkpoint_completion_target 控制checkponit write 分散寫入，值越大越分散。默認(rèn)值0.5，0.9是一個(gè)比較合適的值。
5，work_mem
用于排序，默認(rèn)值即可。每個(gè)連接都會(huì)分配一定work_mem,這個(gè)是會(huì)實(shí)際分配的內(nèi)存，不宜過(guò)大，默認(rèn)值即可。如果要使用語(yǔ)句中有較大的排序操作，可以在會(huì)話級(jí)別設(shè)置該參數(shù)，set work_men = ‘2GB',提高執(zhí)行速度。
6，maintanance_work_mem
維護(hù)性操作使用的內(nèi)存。例如：vacuum ,create index,alter table add foreign key,restoring database dumps.做這些操作時(shí)可以臨時(shí)設(shè)置該值大小，加快執(zhí)行速度。set session maintanance_work_mem = ‘2GB';
7,random_page_cost (默認(rèn)值 4) seq_page_cost(默認(rèn)值 1)
設(shè)置優(yōu)化器獲取一個(gè)隨機(jī)頁(yè)的cost，相比之下一個(gè)順序掃描頁(yè)的cost為1.
當(dāng)使用較快的存儲(chǔ)，如raid arrays，scsi，ssd時(shí)，可以適當(dāng)調(diào)低該值。有利于優(yōu)化器懸著索引掃描。ssd 時(shí)，可以設(shè)置為2.
8，autovacuum
—maintenance_work_mem 1-2GB
—autovacuum_max_workers
如果有多個(gè)小型表，分配更多的workers，更少的mem。
大型表，更多的men，更少的workers。
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。
相關(guān)文章
postgresql 中的幾個(gè) timeout參數(shù) 用法說(shuō)明
這篇文章主要介紹了postgresql中的幾個(gè)timeout參數(shù)用法說(shuō)明，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
PostgreSQL長(zhǎng)事務(wù)與失效的索引查詢淺析介紹
pg中的長(zhǎng)事務(wù)會(huì)影響表中垃圾回收，導(dǎo)致表的年齡增長(zhǎng)無(wú)法freeze。能消耗事務(wù)的只有當(dāng)執(zhí)行了一些DML或者DDL操作后才能算是我們通常說(shuō)的長(zhǎng)事務(wù)。否則只能算是我們常說(shuō)的長(zhǎng)連接，當(dāng)然長(zhǎng)連接也有很多弊端，例如占用內(nèi)存、cpu等資源2022-09-09
什么是索引失效？如果where過(guò)濾條件設(shè)置不合理，即使索引存在，且where過(guò)濾條件中包含索引列，也會(huì)導(dǎo)致全表掃描，索引不起作用。什么條件下會(huì)導(dǎo)致索引失效呢2022-09-09
postgresql 實(shí)現(xiàn)字符串分割字段轉(zhuǎn)列表查詢
這篇文章主要介紹了postgresql 實(shí)現(xiàn)字符串分割字段轉(zhuǎn)列表查詢，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-02-02
這篇文章主要介紹了postgresql中的ctid使用及說(shuō)明,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-11-11
Postgresql去重函數(shù)distinct的用法說(shuō)明
這篇文章主要介紹了Postgresql去重函數(shù)distinct的用法說(shuō)明，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
PostgreSQL數(shù)據(jù)庫(kù)管理系統(tǒng)快速入門
這篇文章主要介紹了PostgreSQL數(shù)據(jù)庫(kù)快速入門,PostgreSQL是一個(gè)功能強(qiáng)大的開(kāi)源對(duì)象關(guān)系型數(shù)據(jù)庫(kù)系統(tǒng)，他使用和擴(kuò)展了SQL語(yǔ)言，并結(jié)合了許多安全存儲(chǔ)和擴(kuò)展最復(fù)雜數(shù)據(jù)工作負(fù)載的功能,需要的朋友可以參考下2023-07-07
PostgreSQL向數(shù)據(jù)庫(kù)表中添加行數(shù)據(jù)的操作
這篇文章主要介紹了PostgreSQL向數(shù)據(jù)庫(kù)表中添加行數(shù)據(jù)的操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-02-02
這篇文章主要介紹了PostgreSQL教程(十八)：客戶端命令（2）,本文講解了pg_dump、pg_restore、psql、內(nèi)置命令應(yīng)用示例等內(nèi)容,需要的朋友可以參考下2015-05-05
postgresql通過(guò)索引優(yōu)化查詢速度操作
這篇文章主要介紹了postgresql通過(guò)索引優(yōu)化查詢速度操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-12-12