PostgreSQL配置最佳化

標籤：

硬體和系統配置

作業系統	Ubuntu13.04
系統位元	64
CPU	Intel(R) Core(TM)2 Duo CPU
記憶體	4G
硬碟	Seagate ST2000DM001-1CH164
測試載入器	PostgreSQL-9.1.11

測試載入器

工具名稱	pgbench
資料量	200W（整個資料庫大小約為300M）
類比用戶端數	4
線程數	4
測試時間	60秒

• 準備命令：pgbench -i -s 20 pgbenchdb

• 測試命令：pgbench -r -j4 -c4 -T60 testdb

設定檔

預設的配置設定檔是儲存在/etc/postgresql/VERSION/main目錄下的postgresql.conf檔案

• 如果想查看參數修改是否生效，可以用psql串連到資料庫後，用<show 選項名> 來查看。

• 如果要修改shared_buffers， 在ubuntu下可能需要執行命令<sysctl -w>Managing Kernel Resources

主要選項

選項	預設值	說明	是否最佳化	原因
max_connections	100	允許用戶端串連的最大數目	否	因為在測試的過程中，100個串連已經足夠
fsync	on	強制把資料同步更新到磁碟	是	因為系統的IO壓力很大，為了更好的測試其他配置的影響，把改參數改為off
shared_buffers	24MB	決定有多少記憶體可以被PostgreSQL用於快取資料（推薦記憶體的1/4)	是	在IO壓力很大的情況下，提高該值可以減少IO
work_mem	1MB	使內部排序和一些複雜的查詢都在這個buffer中完成	是	有助提高排序等操作的速度，並且減低IO
effective_cache_size	128MB	最佳化器假設一個查詢可以用的最大記憶體，和shared_buffers無關（推薦記憶體的1/2)	是	設定稍大，最佳化器更傾向使用索引掃描而不是順序掃描
maintenance_work_mem	16MB	這裡定義的記憶體只是被VACUUM等耗費資源較多的命令調用時使用	是	把該值調大，能加快命令的執行
wal_buffer	768kB	日誌緩衝區的大小	是	可以降低IO，如果遇上比較多的並發短事務，應該和commit_delay一起用
checkpoint_segments	3	設定wal log的最大數量數（一個log的大小為16M）	是	預設的48M的緩衝是一個嚴重的瓶頸，基本上都要設定為10以上
checkpoint_completion_target	0.5	表示checkpoint的完成時間要在兩個checkpoint間隔時間的N%內完成	是	能降低平均寫入的開銷
commit_delay	0	事務提交後，日誌寫到wal log上到wal_buffer寫入到磁碟的時間間隔。需要配合commit_sibling	是	能夠一次寫入多個事務，減少IO，提高效能
commit_siblings	5	設定觸發commit_delay的並發事務數，根據並發事務多少來配置	是	減少IO，提高效能

測試資料

• 測試的資料是運行3次，取平均值。

• 關閉fsync是為了更好的體現出其他參數對PostgreSQL的影響。

參數	修改值	事務總數	tps(包括建立串連)	tps(不包括建立串連)
預設設定		8464	140.999792	141.016182
fsync	off	92571	1479.969755	1480.163355
shared_buffers	1GB	100055	1635.759275	1635.977823
work_mem	10MB	101209	1665.804812	1666.04082
effective_cache_size	2GB	98209	1636.733152	1636.970271
maintenance_work_mem	512MB	92930	1548.029233	1548.223108
checkpoint_segments	32	195982	3265.995	3266.471064
checkpoint_completion_target	0.9	194390	3239.406493	3239.842596
wal_buffer	8MB	198639	3310.241458	3310.724067
恢複fsync	off	11157	185.883542	185.909849
commit_delay && commit_siblings	10 && 4	11229	187.103538	187.131747

總結

	事務總數	tps(包括建立串連)	tps(不包括建立串連)
最佳化前	8464	140.999792	141.016182
最佳化後(fsync=on)	11229	187.103538	187.131747
最佳化後(fsync=off)	198639	3310.241458	3310.724067

在fsync開啟的情況下，最佳化後效能能夠提升30%左右。因為有部分最佳化選項在預設的SQL測試語句中沒有體現出它的優勢，如果到實際測試中，提 升應該不止30%。 測試的過程中，主要的瓶頸就在系統的IO，如果需要減少IO的負荷，最直接的方法就是把fsync關閉，但是這樣就會在掉電的情況下，可能會丟失部分數 據。

PostgreSQL配置最佳化