串連postgres特別消耗cpu資源而引發的PostgreSQL效能最佳化考慮

標籤：記錄   writing   預設   安裝   操作   記憶體   ram   參數配置   建立索引   

由於是開發階段，所以並沒有配置postgres的參數，都是使用安裝時的預設配置，
以前運行也不見得有什麼不正常，可是前幾天我的cpu資源佔用突然升高.
查看進程，發現有一個postgres的進程佔用CPU都是80%以上，而且居高不下；

剛開始以為是配置上需要修改，但事實上，預設配置基本上是很最佳化的，而且是開發階段，資料量也並不大。
後來通過分析，得出結論，解決問題應該從以下幾個方面來逐一考慮：

1，SQL查詢方面
檢查資料檢索的索引是否建立，凡是需要尋找的欄位盡量建立索引，甚至是聯合索引；
建立索引，包括運算式和部分索引；
使用COPY語句代替多個Insert語句；
將多個SQL語句組成一個事務以減少提交事務的開銷；
從一個索引中提取多條記錄時使用CLUSTER；
從一個查詢結果中取出部分記錄時使用LIMIT；
使用先行編譯式查詢（Prepared Query)；
使用ANALYZE以保持精確的最佳化統計；
定期使用 VACUUM 或 pg_autovacuum
進行大量資料更改時先刪除索引（然後重建索引）
2，程式經驗方面
檢查程式，是否使用了串連池，如果沒有使用，儘快使用吧；
繼續檢查程式，串連使用後，是否交還給了串連池；
3，伺服器參數配置
設定檔postgres.conf中的很多設定都會影響效能，
shared_buffers：這是最重要的參數，postgresql通過shared_buffers和核心/磁碟打交道。
因此應該盡量大，讓更多的資料緩衝在shared_buffers中，通常設定為實際RAM的10％是合理的，比如50000(400M)
work_mem：在pgsql 8.0之前叫做sort_mem。postgresql在執行排序操作時，
會根據work_mem的大小決定是否將一個大的結果集拆分為幾個小的和work_mem查不多大小的臨時檔案。
顯然拆分的結果是降低了排序的速度。因此增加work_mem有助於提高排序的速度。通常設定為實際RAM的2%-4%，根據需要排序結果集的大小而定，比如81920(80M)
effective_cache_size：是postgresql能夠使用的最大緩衝，
這個數字對於獨立的pgsql伺服器而言應該足夠大，比如4G的記憶體，可以設定為3.5G(437500)
maintence_work_mem：這裡定義的記憶體只是在CREATE INDEX, VACUUM等時用到，因此用到的頻率不高，但是往往這些指令消耗比較多的資源，
因此應該儘快讓這些指令快速執行完畢：給maintence_work_mem大的記憶體，比如512M(524288)
max_connections：通常，max_connections的目的是防止max_connections * work_mem超出了實際記憶體大小。
比如，如果將work_mem設定為實際記憶體的2%大小，則在極端情況下，如果有50個查詢都有排序要求，而且都使用2％的記憶體，則會導致swap的產生，系統效能就會大大降低。
當然，如果有4G的記憶體，同時出現50個如此大的查詢的幾率應該是很小的。不過，要清楚max_connections和work_mem的關係。
有關參數的解釋可見： http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html 和 http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html。
4，硬體的選擇
由於電腦硬體大多數是相容的，人們總是傾向於相信所有電腦硬體品質也是相同的。
事實上不是， ECC RAM（帶同位的記憶體），SCSI （硬碟）和優質的主板比一些便宜貨要更加可靠且具有更好的效能。
PostgreSQL幾乎可以運行在任何硬體上，但如果可靠性和效能對你的系統很重要，你就需要全面的研究一下你的硬體設定了。
電腦硬體對效能的影響可瀏覽 http://candle.pha.pa.us/main/writings/pgsql/hw_performance/index.html 和 http://www.powerpostgresql.com/PerfList/。
5，為什麼在試圖串連時收到“Sorry, too many clients”訊息？
這表示你已達到預設100個並發後台進程數的限制，
你需要通過修改postgresql.conf檔案中的max_connections值來 增加postmaster的後台並發處理數，修改後需重新啟動postmaster。

串連postgres特別消耗cpu資源而引發的PostgreSQL效能最佳化考慮