PostgreSQL數(shù)據(jù)庫(kù)的參數(shù)設(shè)置有哪些

這篇文章主要講解了“PostgreSQL數(shù)據(jù)庫(kù)的參數(shù)設(shè)置有哪些”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“PostgreSQL數(shù)據(jù)庫(kù)的參數(shù)設(shè)置有哪些”吧！

假設(shè)數(shù)據(jù)庫(kù)主機(jī)OS為L(zhǎng)inux 64bit,內(nèi)存為8G,存儲(chǔ)陣列使用RAID 5(帶寬約為200MB/s,IOPS約為200),主機(jī)沒(méi)有其他服務(wù)。

一、常規(guī)部分

listen_addresses

#PG默認(rèn)監(jiān)聽本地連接,如需接受其他Client的連接請(qǐng)求,需修改為*
listen_addresses = '*'

max_connections

#最大連接數(shù),默認(rèn)為100,根據(jù)業(yè)務(wù)應(yīng)用情況和主機(jī)配置設(shè)置
#由于PG使用多進(jìn)程模式,如連接數(shù)大于一定數(shù)量(與機(jī)器配置相關(guān))時(shí),會(huì)因?yàn)檫M(jìn)程上下文的頻繁切換導(dǎo)致性能降低
#如需要支持?jǐn)?shù)千個(gè)連接,可以考慮使用分庫(kù)讀寫分離的方式,以及使用連接池組件(如PgBouncer)
max_connections=256

查看當(dāng)前連接數(shù)的SQL:

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

二、內(nèi)存相關(guān)

假設(shè)機(jī)器內(nèi)存為N,則需滿足以下要求:
N > max_connections x work_mem + shared_buffers + temp_buffers + maintenance_work_mem + OS運(yùn)行最小要求的內(nèi)存

shared_buffers

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

effective_cache_size

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

work_mem

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

maintenance_work_mem

#VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY等操作可使用的內(nèi)存大小,默認(rèn)64M
#由于這類操作并發(fā)數(shù)不會(huì)很大,增大此參數(shù)相對(duì)較為安全,如希望提升這些操作的性能,可適當(dāng)加大此參數(shù)
maintenance_work_mem=256M

wal_buffers

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

三、性能相關(guān)

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

checkpoint_segments
在參數(shù)在9.5+后已廢棄

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

checkpoint_timeout

#checkpoint超時(shí)時(shí)間,默認(rèn)為5分鐘,最大1d
#在checkpoint_completion_target*checkpoint_timeout超時(shí)或者產(chǎn)生的WAL Segment超過(guò)checkpoint_segments時(shí),執(zhí)行checkpoint
#增大此參數(shù),會(huì)減少磁盤IO壓力,但會(huì)延長(zhǎng)Recover時(shí)間
checkpoint_timeout=8min

checkpoint_completion_target

#指定checkpoint的完成"目標(biāo)",默認(rèn)值為0.5,取值區(qū)間為0.0-1.0
#通過(guò)兩個(gè)checkpoint間隔時(shí)間的百分比來(lái)衡量,也就是checkpoint_completion_target*checkpoint_timeout
checkpoint_completion_target=0.9

random_page_cost

#隨機(jī)讀取Page的代價(jià),以順序讀取為基準(zhǔn),默認(rèn)值為4.0
#該參數(shù)會(huì)影響優(yōu)化器選擇執(zhí)行計(jì)劃,隨機(jī)讀取數(shù)據(jù)一般發(fā)生在通過(guò)Index訪問(wèn)數(shù)據(jù)的時(shí)候
#如數(shù)據(jù)存儲(chǔ)在SSD這類隨機(jī)讀寫友好的設(shè)備上,可以降低至2.0甚至更低
random_page_cost=4.0

四、運(yùn)維相關(guān)

autovacuum

#是否啟動(dòng)自動(dòng)vacuum,默認(rèn)為on,常規(guī)情況下設(shè)置為on
#vacuum的詳細(xì)解析請(qǐng)參見(jiàn)先前章節(jié),此處不再累述
autovacuum=on

log_XX

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

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

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

#log_rotation_age:每個(gè)多長(zhǎng)時(shí)間產(chǎn)生一個(gè)日志,如設(shè)置為1d,則每隔一天產(chǎn)生一個(gè)日志文件 
#logging_collector=on時(shí)生效
log_rotation_age=1d

#log_rotation_size:單個(gè)文件的最大大小,超過(guò)此值,重新生成日志文件
#logging_collector=on時(shí)生效
log_rotation_size=16MB

#log_min_duration_statement:記錄執(zhí)行時(shí)長(zhǎng)>此值定義的SQL語(yǔ)句
log_min_duration_statement=1s

五、謹(jǐn)慎使用

synchronous_commit

#是否等待WAL數(shù)據(jù)寫入到磁盤后才返回成功,可選的值為on, remote_apply, remote_write, local, off,默認(rèn)為on
#synchronous_commit設(shè)置為off,可能會(huì)導(dǎo)致雖然成功但實(shí)際上沒(méi)有持久化的事務(wù)
synchronous_commit=on

commit_delay/commit_siblings

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

fsync

#設(shè)置為on時(shí)，日志緩沖區(qū)刷盤時(shí)確認(rèn)已經(jīng)寫入磁盤才會(huì)返回;
#設(shè)置為off時(shí)，由OS負(fù)責(zé)調(diào)度，能更好利用OS的緩存機(jī)制，提高IO性能。
#利用異步寫入的機(jī)制提升性能,但同樣存在數(shù)據(jù)丟失的風(fēng)險(xiǎn)
fsync=on

感謝各位的閱讀，以上就是“PostgreSQL數(shù)據(jù)庫(kù)的參數(shù)設(shè)置有哪些”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)PostgreSQL數(shù)據(jù)庫(kù)的參數(shù)設(shè)置有哪些這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！