PostgreSQL配置優(yōu)化
作者：佚名
在fsync打開的情況下，優(yōu)化后性能能夠提升30%左右。因為有部分優(yōu)化選項在默認(rèn)的SQL測試語句中沒有體現(xiàn)出它的優(yōu)勢，如果到實際測試中，提升應(yīng)該不止30%。測試的過程中，主要的瓶頸就在系統(tǒng)的IO，如果需要減少IO的負(fù)荷，最直接的方法就是把fsync關(guān)閉，但是這樣就會在掉電的情況下，可能會丟失部分?jǐn)?shù)據(jù)。
硬件和系統(tǒng)配置
| 操作系統(tǒng) | Ubuntu13.04 |
| 系統(tǒng)位數(shù) | 64 |
| CPU | Intel(R) Core(TM)2 Duo CPU |
| 內(nèi)存 | 4G |
| 硬盤 | Seagate ST2000DM001-1CH164 |
| 測試工具 | PostgreSQL-9.1.11 |
測試工具
| 工具名稱 | pgbench |
| 數(shù)據(jù)量 | 200W（整個數(shù)據(jù)庫大小約為300M） |
| 模擬客戶端數(shù) | 4 |
| 線程數(shù) | 4 |
| 測試時間 | 60秒 |
- 準(zhǔn)備命令：pgbench -i -s 20 pgbenchdb
- 測試命令：pgbench -r -j4 -c4 -T60 testdb
配置文件
默認(rèn)的配置配置文件是保存在/etc/postgresql/VERSION/main目錄下的postgresql.conf文件
- 如果想查看參數(shù)修改是否生效，可以用psql連接到數(shù)據(jù)庫后，用<show 選項名> 來查看。
- 如果要修改shared_buffers， 在ubuntu下可能需要執(zhí)行命令<sysctl -w>Managing Kernel Resources
主要選項
| 選項 | 默認(rèn)值 | 說明 | 是否優(yōu)化 | 原因 |
| max_connections | 100 | 允許客戶端連接的最大數(shù)目 | 否 | 因為在測試的過程中，100個連接已經(jīng)足夠 |
| fsync | on | 強(qiáng)制把數(shù)據(jù)同步更新到磁盤 | 是 | 因為系統(tǒng)的IO壓力很大，為了更好的測試其他配置的影響，把改參數(shù)改為off |
| shared_buffers | 24MB | 決定有多少內(nèi)存可以被PostgreSQL用于緩存數(shù)據(jù)（推薦內(nèi)存的1/4) | 是 | 在IO壓力很大的情況下，提高該值可以減少IO |
| work_mem | 1MB | 使內(nèi)部排序和一些復(fù)雜的查詢都在這個buffer中完成 | 是 | 有助提高排序等操作的速度，并且減低IO |
| effective_cache_size | 128MB | 優(yōu)化器假設(shè)一個查詢可以用的最大內(nèi)存，和shared_buffers無關(guān)（推薦內(nèi)存的1/2) | 是 | 設(shè)置稍大，優(yōu)化器更傾向使用索引掃描而不是順序掃描 |
| maintenance_work_mem | 16MB | 這里定義的內(nèi)存只是被VACUUM等耗費(fèi)資源較多的命令調(diào)用時使用 | 是 | 把該值調(diào)大，能加快命令的執(zhí)行 |
| wal_buffer | 768kB | 日志緩存區(qū)的大小 | 是 | 可以降低IO，如果遇上比較多的并發(fā)短事務(wù)，應(yīng)該和commit_delay一起用 |
| checkpoint_segments | 3 | 設(shè)置wal log的最大數(shù)量數(shù)（一個log的大小為16M） | 是 | 默認(rèn)的48M的緩存是一個嚴(yán)重的瓶頸，基本上都要設(shè)置為10以上 |
| checkpoint_completion_target | 0.5 | 表示checkpoint的完成時間要在兩個checkpoint間隔時間的N%內(nèi)完成 | 是 | 能降低平均寫入的開銷 |
| commit_delay | 0 | 事務(wù)提交后，日志寫到wal log上到wal_buffer寫入到磁盤的時間間隔。需要配合commit_sibling | 是 | 能夠一次寫入多個事務(wù)，減少IO，提高性能 |
| commit_siblings | 5 | 設(shè)置觸發(fā)commit_delay的并發(fā)事務(wù)數(shù)，根據(jù)并發(fā)事務(wù)多少來配置 | 是 | 減少IO，提高性能 |
測試數(shù)據(jù)
- 測試的數(shù)據(jù)是運(yùn)行3次，取平均值。
- 關(guān)閉fsync是為了更好的體現(xiàn)出其他參數(shù)對PostgreSQL的影響。
| 參數(shù) | 修改值 | 事務(wù)總數(shù) | tps(包括建立連接) | tps(不包括建立連接) |
| 默認(rèn)設(shè)置 | 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 |
| 恢復(fù)fsync | off | 11157 | 185.883542 | 185.909849 |
| commit_delay && commit_siblings | 10 && 4 | 11229 | 187.103538 | 187.131747 |
總結(jié)
| 事務(wù)總數(shù) | tps(包括建立連接) | tps(不包括建立連接) | |
| 優(yōu)化前 | 8464 | 140.999792 | 141.016182 |
| 優(yōu)化后(fsync=on) | 11229 | 187.103538 | 187.131747 |
| 優(yōu)化后(fsync=off) | 198639 | 3310.241458 | 3310.724067 |
在fsync打開的情況下，優(yōu)化后性能能夠提升30%左右。因為有部分優(yōu)化選項在默認(rèn)的SQL測試語句中沒有體現(xiàn)出它的優(yōu)勢，如果到實際測試中，提升應(yīng)該不止30%。
測試的過程中，主要的瓶頸就在系統(tǒng)的IO，如果需要減少IO的負(fù)荷，最直接的方法就是把fsync關(guān)閉，但是這樣就會在掉電的情況下，可能會丟失部分?jǐn)?shù)據(jù)。
原文鏈接：http://blog.csdn.net/kyle__shaw/article/details/17576259
責(zé)任編輯：陳四芳
來源：
路人丙的奮斗史
