PostgreSQL DBA(2) - 資料庫引數設定#2

本節簡單介紹了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

六、小結

本節簡單介紹了PG的部分資料庫引數,包括常規/記憶體相關等引數,其中部分引數可以提升效能,但需要謹慎使用.
參考文件:
Tuning_Your_PostgreSQL_Server