PostgreSQL 參數優化

前不久，一個朋友所在的公司，業務人員整天都喊慢。

朋友是搞開發的，不是很懂 DB，他說他們應用的其實沒什麼問題，但是就是每天一到高峯期就辦理特別的慢啊，各種堵塞，一堆請求無法完成。他們沒有專門的 DBA，想找我幫忙看看。

我下班後打開他們的數據庫看了幾眼，讓我大跌眼睛，數據庫全部都是默認配置的參數。然後就給他們隨便修改了一通，告訴他們重啓了一下數據庫。到了第二天他反映，大哥，你也太神了，昨天做了什麼操作，我們的數據庫就突然就變快了。

我說：啥也沒做啊，就是根據經驗值把默認參數調了一下，草率的很！你們參數都是開箱即用的默認值。當做生產使用肯定不行。

PostgreSQL 應該調整的參數

max_connections

允許的最大客戶端連接數。這個參數設置大小和 work_mem 有一些關係。配置的越高，可能會佔用系統更多的內存。通常可以設置數百個連接，如果要使用上千個連接，建議配置連接池來減少開銷。

shared_buffers

PostgreSQL 使用自己的緩衝區，也使用 Linux 操作系統內核緩衝 OS Cache。這就說明數據兩次存儲在內存中，首先是 PostgreSQL 緩衝區，然後是操作系統內核緩衝區。與其他數據庫不同，PostgreSQL 不提供直接 IO，所以這又被稱爲雙緩衝。PostgreSQL 緩衝區稱爲shared_buffer，建議設置爲物理內存的 1/4。而實際配置取決於硬件配置和工作負載，如果你的內存很大，而你又想多緩衝一些數據到內存中，可以繼續調大shared_buffer。

Effective_cache_size

這個參數主要用於 Postgre 查詢優化器。是單個查詢可用的磁盤高速緩存的有效大小的一個假設，是一個估算值，它並不佔據系統內存。由於優化器需要進行估算成本，較高的值更有可能使用索引掃描，較低的值則有可能使用順序掃描。一般這個值設置爲內存的 1/2 是正常保守的設置，設置爲內存的 3/4 是比較推薦的值。通過 free 命令查看操作系統的統計信息，您可能會更好的估算該值。

[pg@e22 ~]$ free -g
              total        used        free      shared  buff/cache   available
Mem:             62           2           5          16          55          40
Swap:             7           0           7

work_mem

這個參數主要用於寫入臨時文件之前內部排序操作和散列表使用的內存量，增加work_mem參數將使 PostgreSQL 可以進行更大的內存排序。這個參數和max_connections有一些關係，假設你設置爲 30MB，則 40 個用戶同時執行查詢排序，很快就會使用 1.2GB 的實際內存。同時對於複雜查詢，可能會運行多個排序和散列操作，例如涉及到 8 張表進行合併排序，此時就需要 8 倍的work_mem。

如下面案例所示，該環境使用 4MB 的work_mem，在執行排序操作的時候，使用的Sort Method是external merge Disk。

kms=> explain (analyze,buffers) select * from KMS_BUSINESS_HALL_TOTAL  order by buss_query_info;
                                                                       QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather Merge  (cost=262167.99..567195.15 rows=2614336 width=52) (actual time=2782.203..5184.442 rows=3137204 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=68 read=25939, temp read=28863 written=28947
   ->  Sort  (cost=261167.97..264435.89 rows=1307168 width=52) (actual time=2760.566..3453.783 rows=1045735 loops=3)
         Sort Key: buss_query_info
         Sort Method: external merge  Disk: 50568kB
         Worker 0:  Sort Method: external merge  Disk: 50840kB
         Worker 1:  Sort Method: external merge  Disk: 49944kB
         Buffers: shared hit=68 read=25939, temp read=28863 written=28947
         ->  Parallel Seq Scan on kms_business_hall_total  (cost=0.00..39010.68 rows=1307168 width=52) (actual time=0.547..259.524 rows=1045735 loops=3)
               Buffers: shared read=25939
 Planning Time: 0.540 ms
 Execution Time: 5461.516 ms
(14 rows)

當我們把參數修改成 512MB 的時候，可以看到Sort Method變成了quicksort Memory，變成了內存排序。

kms=> set work_mem to "512MB";
SET
kms=> explain (analyze,buffers) select * from KMS_BUSINESS_HALL_TOTAL  order by buss_query_info;
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=395831.79..403674.80 rows=3137204 width=52) (actual time=7870.826..8204.794 rows=3137204 loops=1)
   Sort Key: buss_query_info
   Sort Method: quicksort  Memory: 359833kB
   Buffers: shared hit=25939
   ->  Seq Scan on kms_business_hall_total  (cost=0.00..57311.04 rows=3137204 width=52) (actual time=0.019..373.067 rows=3137204 loops=1)
         Buffers: shared hit=25939
 Planning Time: 0.081 ms
 Execution Time: 8419.994 ms
(8 rows)

maintenance_work_mem

指定維護操作使用的最大內存量，例如（Vacuum、Create Index 和 Alter Table Add Foreign Key），默認值是 64MB。由於通常正常運行的數據庫中不會有大量併發的此類操作，可以設置的較大一些，提高清理和創建索引外鍵的速度。

postgres=# set maintenance_work_mem to "64MB";
SET
Time: 1.971 ms
postgres=# create index idx1_test on test(id);
CREATE INDEX
Time: 7483.621 ms (00:07.484)
postgres=# set maintenance_work_mem to "2GB";
SET
Time: 0.543 ms
postgres=# drop index idx1_test;
DROP INDEX
Time: 133.984 ms
postgres=# create index idx1_test on test(id);
CREATE INDEX
Time: 5661.018 ms (00:05.661)

可以看到在使用默認的 64MB 創建索引，速度爲 7.4 秒，而設置爲 2GB 後，創建速度是 5.6 秒

wal_sync_method

每次發生事務後，PostgreSQL 會強制將提交寫到 WAL 日誌的方式。可以使用pg_test_fsync命令在你的操作系統上進行測試，fdatasync是 Linux 上的默認方法。如下所示，我的環境測試下來fdatasync還是速度可以的。不支持的方法像 fsync_writethrough 直接顯示 n/a。

postgres=# show wal_sync_method ;
 wal_sync_method 
-----------------
 fdatasync
(1 row)

[pg@e22 ~]$ pg_test_fsync -s 3
3 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.

Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync is Linux's default)
        open_datasync                      4782.871 ops/sec     209 usecs/op
        fdatasync                          4935.556 ops/sec     203 usecs/op
        fsync                              3781.254 ops/sec     264 usecs/op
        fsync_writethrough                              n/a
        open_sync                          3850.219 ops/sec     260 usecs/op

Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync is Linux's default)
        open_datasync                      2469.646 ops/sec     405 usecs/op
        fdatasync                          4412.266 ops/sec     227 usecs/op
        fsync                              3432.794 ops/sec     291 usecs/op
        fsync_writethrough                              n/a
        open_sync                          1929.221 ops/sec     518 usecs/op

Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB in different write
open_sync sizes.)
         1 * 16kB open_sync write          3159.780 ops/sec     316 usecs/op
         2 *  8kB open_sync writes         1944.723 ops/sec     514 usecs/op
         4 *  4kB open_sync writes          993.173 ops/sec    1007 usecs/op
         8 *  2kB open_sync writes          493.396 ops/sec    2027 usecs/op
        16 *  1kB open_sync writes          249.762 ops/sec    4004 usecs/op

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written on a different
descriptor.)
        write, fsync, close                3719.973 ops/sec     269 usecs/op
        write, close, fsync                3651.820 ops/sec     274 usecs/op

Non-sync'ed 8kB writes:
        write                            400577.329 ops/sec       2 usecs/op

wal_buffers

事務日誌緩衝區的大小，PostgreSQL 將 WAL 記錄寫入緩衝區，然後再將緩衝區刷新到磁盤。在 PostgreSQL 12 版中，默認值爲 - 1，也就是選擇等於 shared_buffers 的 1/32 。如果自動的選擇太大或太小可以手工設置該值。一般考慮設置爲 16MB。

synchronous_commit

客戶端執行提交，並且等待 WAL 寫入磁盤之後，然後再將成功狀態返回給客戶端。可以設置爲 on，remote_apply，remote_write，local，off 等值。默認設置爲 on。如果設置爲 off，會關閉 sync_commit，客戶端提交之後就立馬返回，不用等記錄刷新到磁盤。此時如果 PostgreSQL 實例崩潰，則最後幾個異步提交將會丟失。

default_statistics_target

PostgreSQL 使用統計信息來生成執行計劃。統計信息可以通過手動 Analyze 命令或者是 autovacuum 進程啓動的自動分析來收集，default_statistics_target參數指定在收集和記錄這些統計信息時的詳細程度。默認值爲 100 對於大多數工作負載是比較合理的，對於非常簡單的查詢，較小的值可能會有用，而對於複雜的查詢（尤其是針對大型表的查詢），較大的值可能會更好。爲了不要一刀切，可以使用 ALTER TABLE .. ALTER COLUMN .. SET STATISTICS 覆蓋特定表列的默認收集統計信息的詳細程度。

checkpoint_timeout、max_wal_size，min_wal_size、checkpoint_completion_target

瞭解這兩個參數以前，首先我們來看一下，觸發檢查點的幾個操作。

• 直接執行 checkpoint 命令

• 執行需要檢查點的命令（例如 pg_start_backup,Create database,pg_ctl stop/start 等等）

• 自上一個檢查點以來，達到了已經配置的時間量（checkpoint_timeout ）

• 自上一個檢查點以來生成的 WAL 數量（max_wal_size）

使用默認值，檢查點將在 checkpoint_timeout=5min。也就是每 5 分鐘觸發一次。而 max_wal_size 設置是自動檢查點之間增長的最大預寫日誌記錄（WAL）量。默認是 1GB，如果超過了 1GB，則會發生檢查點。這是一個軟限制。在一個特殊的情況下，比如系統遭遇到短時間的高負載，日誌產生幾秒種就可以達到 1GB，這個速度已經明顯超過了 checkpoint_timeout ，pg_wal 目錄的大小會急劇增加。此時我們可以從日誌中看到相關類似的警告。

LOG:  checkpoints are occurring too frequently (9 seconds apart)
HINT:  Consider increasing the configuration parameter "max_wal_size".
LOG:  checkpoints are occurring too frequently (2 seconds apart)
HINT:  Consider increasing the configuration parameter "max_wal_size".

所以要合理配置 max_wal_size，以避免頻繁的進行檢查點。一般推薦設置爲 16GB 以上，不過具體設置多大還需要和工作負荷相匹配。

min_wal_size 參數是隻要 WAL 磁盤使用量保持在這個設置之下，在做檢查點時，舊的 WAL 文件總是被回收以便未來使用，而不是直接被刪除。

而檢查點的寫入不是全部立馬完成的，PostgreSQL 會將一次檢查點的所有操作分散到一段時間內。這段時間由參數checkpoint_completion_target控制，它是一個分數，默認爲 0.5。也就是在兩次檢查點之間的 0.5 比例完成寫盤操作。如果設置的很小，則檢查點進程就會更加迅速的寫盤，設置的很大，則就會比較慢。一般推薦設置爲 0.9，讓檢查點的寫入分散一點。但是缺點就是出現故障的時候，影響恢復的時間。

使用 PGTune 工具來配置參數

對於朋友這樣的公司，沒有 DBA 專業人士，我一般會建議他們使用 PGTune 來配置參數，這款工具是在線軟件，鏈接地址。設置很簡單，你只需要知道你的數據庫版本，操作系統類型，內存數量，CPU 數量，磁盤類型，連接數，還有應用的類型。就可以輕輕鬆鬆得到一些建議的參數值。

使用 postgresqltuner 來優化參數

當然我們還可以使用 postgresqltuner 工具來優化參數，作者說受到了 mysqltuner 的啓發，它是 perl 腳本寫的。鏈接地址

這個軟件使用起來也很簡單，直接下載解壓，執行腳本就行了。

postgresqltuner.pl --host=dbhost --database=testdb --user=username --password=qwerty

執行輸出結果如下：

這個軟件要比 PGTune 要專業一些，它還輸出了一些操作系統的配置，同時它根據數據庫當前的負載來判斷內存參數是否合理，類似於 Advisor。

總結

最後來做個 ending 吧，系統默認的配置只適合自己玩玩，並不適合開箱即用。還是需要根據 DBA 的專業經驗來進行相關參數的配置，如果沒有專業的 DBA 童鞋，也可以使用 PGTune 或者是 postgresqltuner 腳本來進行一些優化，做完這些優化，系統性能將會大幅提升。

參考文檔

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Serverhttps://www.percona.com/blog/2018/08/31/tuning-postgresql-database-parameters-to-optimize-performance/

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/aDFlZXr7QvNYfu6IEYqCng