PostgreSQL 性能調優

批量導入性能優化

關閉自動提交

在批量插入數據時，如果每條數據都被自動提交，當中途出現系統故障時，不僅不能保障本次批量插入的數據一致性，而且由于有多次提交操作的發生，整個插入效率也會受到很大的打擊。解決方法是，關閉系統的自動提交(SET AUTOCOMMIT = OFF)，并且在插入開始之前，顯式的執行 BEGIN TRANSACTION 命令，在全部插入操作完成之后再執行 COMMIT 命令提交所有的插入操作。

使用 COPY 命令

使用 COPY 在一條命令里裝載所有記錄，而不是一系列的 INSERT 命令。COPY 命令是為裝載數量巨大的數據行優化過的，它不像 INSERT 命令那樣靈活，但是在裝載大量數據時，系統開銷也要少很多。注：因為 COPY 是單條命令，因此在填充表的時候就沒有必要關閉自動提交了。

刪除索引

如果你正在裝載一個新創建的表，最快的方法是創建表，用 COPY 批量裝載，然后創建表需要的任何索引。因為在已存在數據的表上創建索引比維護逐行增加要快。當然在缺少索引期間，其它有關該表的查詢操作的性能將會受到一定的影響，唯一性約束也有可能遭到破壞。

刪除外鍵約束

和索引一樣，"批量地"檢查外鍵約束比一行行檢查更加高效。因此，我們可以先刪除外鍵約束，裝載數據，然后再重建約束。

臨時增大 maintenance_work_mem

在裝載大量數據時，臨時增大 maintenance_work_mem 系統變量的值可以改進性能。這個系統參數可以提高CREATE INDEX 命令和 ALTER TABLE ADD FOREIGN KEY 命令的執行效率，但是它不會對 COPY 操作本身產生多大的影響。

臨時增大 checkpoint_segments

臨時增大 checkpoint_segments 系統變量的值也可以提高大量數據裝載的效率。這是因為在向 PostgreSQL 裝載大量數據時，將會導致檢查點操作(由系統變量 checkpoint_timeout 聲明)比平時更加頻繁的發生。在每次檢查點發生時，所有的臟數據都必須 flush 到磁盤上。通過提高 checkpoint_segments 變量的值，可以有效的減少檢查點的數目。

設置為 UNLOGGED 表

PostgreSQL 的 unlogged table 是不記錄 xlog 的，因此批量導入的時候 unlogged 表速度比 logged 表性能要快上一倍。但是， unlogged 表不是異常安全的：數據庫崩潰或者異常關閉后， unlogged 表中的數據會被自動刪減；另外， unlogged 表中的數據也不會備份到從服務器。任何 unlogged 表上創建的索引也是 unlogged 的。對于批量數據導入，我們可以臨時設置表為 unlogged(ALTER TABLE tb SET UNLOGGED)，之后再將表重新設置為 logged(ALTER TABLE tb SET LOGGED)。

事后運行 VACUUM ANALYZE

在增加或者更新了大量數據之后，應該立即運行 VACUUM ANALYZE 命令，這樣可以保證規劃器得到基于該表的最新數據統計。換句話說，如果沒有統計數據或者統計數據太過陳舊，那么規劃器很可能會選擇一個較差的查詢規劃，從而導致查詢效率過于低下。

查詢性能優化

關于 PostgreSQL 的性能參數，主要以下5個起重要作用:

1. shared_buffers

   這是最重要的參數， PostgreSQL 通過 shared_buffers 和內核和磁盤打交道，因此應該盡量大，讓更多的數據緩存在 shared_buffers 中。通常設置為實際 RAM 的 10% 是合理的，比如50000(400M),也有說法是 25% – 40%.

   要在系統中設置 kernel.shamax 的值，該值決定了進程可調用最大共享內存數量。

   簡單的計算方法是： kernel.shmmax = postgres shared_buffers + 32MB

2. work_mem在 PostgreSQL 8.0之前叫做 sort_mem。PostgreSQL 在執行排序操作時，會根據 work_mem 的大小決定是否將一個大的結果集拆分為幾個小的和 work_mem 差不多大小的臨時文件。顯然拆分的結果是降低了排序的速度。因此增加 work_mem 有助于提高排序的速度。通常設置為實際 RAM 的 2% – 4%，根據需要排序結果集的大小而定，比如 81920(80M)

   注意：這是 per connection and per sort 的設定。如果有100個連接，每個連接有2個 sort 運算，那么需要的總內存是： 100 * 2 * work_mem。

   利用 EXPLAIN ANALYZE 可以檢查是否有足夠的 work_mem。

3. effective_cache_size設置稍大，優化器更傾向使用索引掃描而不是順序掃描，建議的設置為可用空閑內存的 25%，這里的可用空閑內存指的是主機物理內存在運行 pg 時的空閑值。

4. maintenance_work_mem這里定義的內存只是在 CREATE INDEX, VACUUM 等時用到，因此用到的頻率不高，但是往往這些指令消耗比較多的資源，因此應該盡快讓這些指令快速執行完畢：給 maintence_work_mem 大的內存，比如 512M(524288)

5. max_connections通常， max_connections 的目的是防止 max_connections * work_mem 超出了實際內存大小。比如，如果將 work_mem 設置為實際內存的 2% 大小，則在極端情況下，如果有50個查詢都有排序要求，而且都使用2% 的內存，則會導致 swap 的產生，系統性能就會大大降低。

6. FSYNC vs ASYNCPostgreSQL 默認做 fsync，也就是說 PostgreSQL 會等待數據被寫入硬盤，才會給 query返回成功的信號。如果設定 sync=no 關閉 fsync 的話， PostgreSQL 不會等待 WAL 寫回硬盤，就直接返回 query 成功。通常這個會帶來 15-25% 的性能提升.但是缺點就是，如果系統崩潰（斷電， PostgreSQL 掛掉）的時候，你將有可能丟失最后那個 transcation. 不過這個并不會造成你系統的數據結構問題。如果說在系統出問題的時候丟失1-2筆數據是可以接受的，那么 25% 的性能提升是很可觀的。

7. WAL設定fsync 可以選擇 on 或者 off

8. wal_buffersWAL 的儲存大小。default 是 64 kb。 實驗證明， 設定這個值在 256 kb 到 1 MB 之間會提升效能。

9. wal_writer_delayWAL 檢查 WAL 數據（回寫）的間隔時間。值是毫秒(milliseconds)

10. Checkpoints

    確保數據回寫硬盤。

    如果 checkpoint 運行頻率高于checkpint_warning 值，dirty data page 會被 flushed 回硬盤。PostgreSQL 會在日志（log）中記錄出來，通過觀察log，可以來決定 checkpoint_segments 的設定。增加

    checkpoint_segments 或者 checkpoint_timeout 可以有一定的效能提升。而唯一的壞處就是如果系統掛了，在重啟的時需要多一點時間來回復（系統啟動恢復期間數據庫是不能用的）鑒于 PostgreSQL 很少掛掉，這個其實可以設定的很長（1天都可以）。

    設定：

    • checkpoint_segments： 最多的wal log數量，到達后會激發 checkpoint，通常設置定為30
    • checkpoint_timeout： 一般設置15-20分鐘，常的可以設定1天也沒關系
    • checkpoint_completion_target： 這個保持不動就好。內建是0.5，意思就是每個 checkpoint 預計在下個 checkpoint 完成前的一半時間內完成。
    • checkpoint_warning： 如果checkpint速度快于這個時間，在log中記錄。內建是30秒

Reference：

PostgreSQL Performance Optimization

PostgreSQL 優化數據的批量插入

來自： http://www.mutouxiaogui.cn/blog/?p=413