PostgreSQL數據庫的參數設置有哪些

這篇文章主要講解了“PostgreSQL數據庫的參數設置有哪些”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“PostgreSQL數據庫的參數設置有哪些”吧！

假設數據庫主機OS為Linux 64bit,內存為8G,存儲陣列使用RAID 5(帶寬約為200MB/s,IOPS約為200),主機沒有其他服務。

一、常規部分

listen_addresses

#PG默認監聽本地連接,如需接受其他Client的連接請求,需修改為*
listen_addresses = '*'

max_connections

#最大連接數,默認為100,根據業務應用情況和主機配置設置
#由于PG使用多進程模式,如連接數大于一定數量(與機器配置相關)時,會因為進程上下文的頻繁切換導致性能降低
#如需要支持數千個連接,可以考慮使用分庫讀寫分離的方式,以及使用連接池組件(如PgBouncer)
max_connections=256

查看當前連接數的SQL:

testdb=# SELECT sum(numbackends) FROM pg_stat_database;
 sum 
-----
   1
(1 row)

二、內存相關

假設機器內存為N,則需滿足以下要求:
N > max_connections x work_mem + shared_buffers + temp_buffers + maintenance_work_mem + OS運行最小要求的內存

shared_buffers

#共享緩沖區,用于配置可用于Cache Data(包括Hot Data/Index等等)的內存大小
#一般設置為主機內存的25%-40%
shared_buffers=3G

effective_cache_size

#剔除操作系統本身和其他應用程序可用的內存后,期望操作系統和數據庫本身可用于緩存數據的內存大小
#該參數僅用于優化的estimate(評估)階段,如果設置有誤會影響優化器的判斷,得出不合理的執行計劃
#比如在只有512M的主機上,設置該參數為4G,查詢一張有索引的大表時,如果訪問的索引大小<4G,那么執行計劃有可能會使用該索引,但實際上主機內存并不支持容納這么大的索引,導致實際執行SQL時出現性能問題;同樣的,如果設置的過小,執行計劃可能不會選擇用索引.
effective_cache_size=4G

work_mem

#每個Session執行排序和建立散列表等操作時可使用的內存大小,默認1M
#如需執行較大/復雜的排序或連接操作,建議增大此參數
#注意:示例中最大連接數為256,如此參數設置為4M,那么在滿載情況下使用的內存為4Mx256=1G
work_mem=2M

maintenance_work_mem

#VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY等操作可使用的內存大小,默認64M
#由于這類操作并發數不會很大,增大此參數相對較為安全,如希望提升這些操作的性能,可適當加大此參數
maintenance_work_mem=256M

wal_buffers

#用于緩存WAL data,默認為-1(約為shared_buffers的1/32),最小為64KB,最大為WAL segment大小(典型為16MB)
#由于WAL data在事務commit時寫入到日志文件中,在典型的主機配置下,假設每次Buffer都全滿,寫入的延遲約為wal_buffers/200,在0.32ms(64K)至81.92ms(16M)之間
wal_buffers=4M

三、性能相關

max_wal_size/min_wal_size
wal日志占用的最大值和最小值,默認最大為1G,最小為80M,用以替換先前的參數checkpoint_segments

checkpoint_segments
在參數在9.5+后已廢棄

#1.該參數定義了寫了多少個WAL Segment執行一次checkpoint,默認為3(典型的,即48M)
#checkpoint最主要的功能是把緩存中臟數據寫入到磁盤中(注意:這里的寫是隨機寫,不是WAL的順序寫!)
#頻繁的checkpoint會影響數據庫性能,在常規的場景下,使用默認值會極大的降低數據庫性能
#假設該參數值為n,那么粗略上來說,每間隔(n*16MB/200MB)秒就會執行一次checkpoint
#2.該參數會影響Recover,數值越大,人出現問題Recover的時間可能越長
#假設該參數值為n,那么極端情況下在產生n*16MB個WAL segment時出現宕機,下次啟動時,粗略來說就需要n*16MB個WAL segment進行Recover
checkpoint_segments=32

checkpoint_timeout

#checkpoint超時時間,默認為5分鐘,最大1d
#在checkpoint_completion_target*checkpoint_timeout超時或者產生的WAL Segment超過checkpoint_segments時,執行checkpoint
#增大此參數,會減少磁盤IO壓力,但會延長Recover時間
checkpoint_timeout=8min

checkpoint_completion_target

#指定checkpoint的完成"目標",默認值為0.5,取值區間為0.0-1.0
#通過兩個checkpoint間隔時間的百分比來衡量,也就是checkpoint_completion_target*checkpoint_timeout
checkpoint_completion_target=0.9

random_page_cost

#隨機讀取Page的代價,以順序讀取為基準,默認值為4.0
#該參數會影響優化器選擇執行計劃,隨機讀取數據一般發生在通過Index訪問數據的時候
#如數據存儲在SSD這類隨機讀寫友好的設備上,可以降低至2.0甚至更低
random_page_cost=4.0

四、運維相關

autovacuum

#是否啟動自動vacuum,默認為on,常規情況下設置為on
#vacuum的詳細解析請參見先前章節,此處不再累述
autovacuum=on

log_XX

#log_destination:日志類型,包括stderr, csvlog, syslog, eventlog
#建議設置為csvlog
log_destination='csvlog'

#logging_collector:如log_destination配置為csvlog,則該參數要求配置為on
logging_collector=on

#log_directory:日志存儲路徑,可使用相對路徑,在$PGDATA目錄下
log_directory='pg_log'

#log_rotation_age:每個多長時間產生一個日志,如設置為1d,則每隔一天產生一個日志文件 
#logging_collector=on時生效
log_rotation_age=1d

#log_rotation_size:單個文件的最大大小,超過此值,重新生成日志文件
#logging_collector=on時生效
log_rotation_size=16MB

#log_min_duration_statement:記錄執行時長>此值定義的SQL語句
log_min_duration_statement=1s

五、謹慎使用

synchronous_commit

#是否等待WAL數據寫入到磁盤后才返回成功,可選的值為on, remote_apply, remote_write, local, off,默認為on
#synchronous_commit設置為off,可能會導致雖然成功但實際上沒有持久化的事務
synchronous_commit=on

commit_delay/commit_siblings

#commit_delay:事務提交后，日志寫到wal_buffer至wal_buffer中的內容寫入磁盤的時間間隔
#commit_siblings:觸發commit_delay等待的并發事務數,如并發事務數<該值,則commit_delay無用
#這兩個參數用于提升在高并發非只讀事務的情況下的性能,可以讓buffer一次可以刷出較多的事務(Bulk Flush的效果)
#但同樣的,如果出現極端情況,buffer來不及持久化的時候出現崩潰,將會導致數據丟失
commit_delay=0
commit_siblings=5

fsync

#設置為on時，日志緩沖區刷盤時確認已經寫入磁盤才會返回;
#設置為off時，由OS負責調度，能更好利用OS的緩存機制，提高IO性能。
#利用異步寫入的機制提升性能,但同樣存在數據丟失的風險
fsync=on

感謝各位的閱讀，以上就是“PostgreSQL數據庫的參數設置有哪些”的內容了，經過本文的學習后，相信大家對PostgreSQL數據庫的參數設置有哪些這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！