VACUUM 引數優化

上面已經介紹過了以下設定表級 AUTOVACUUM 相關引數和 autovacuum_max_workers：

ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_scale_factor = 0.1, autovacuum_vacuum_threshold = 2000);
ALTER TABLE pgbench_accounts SET (autovacuum_analyze_scale_factor = 0.05, autovacuum_analyze_threshold = 2000);

下面就以下常用 VACUUM 引數詳細介紹如何進行調優，

maintenance_work_mem 引數

#maintenance_work_mem = 64MB            # min 1MB
#autovacuum_work_mem = -1               # min 1MB, or -1 to use maintenance_work_mem

vacuum_cost_delay 引數

#vacuum_cost_delay = 0
#autovacuum_vacuum_cost_delay = 20ms

vacuum_cost_limit 引數

#vacuum_cost_limit = 200
#autovacuum_vacuum_cost_limit = -1

引數優化測驗用例

使用 pgbench 生成 5000 萬測驗資料，

pgbench -i -s 500 alvindb

設定表級 AUTOVACUUM 相關引數：

ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_scale_factor = 0.1, autovacuum_vacuum_threshold = 2000);
ALTER TABLE pgbench_accounts SET (autovacuum_analyze_scale_factor = 0.05, autovacuum_analyze_threshold = 2000);

洗掉 2000 萬資料：

DELETE FROM pgbench_accounts WHERE aid>=1500001 AND aid <=3500000;
DELETE FROM pgbench_accounts WHERE aid>=15000001 AND aid <=17000000;
DELETE FROM pgbench_accounts WHERE aid>=25000001 AND aid <=28000000;
DELETE FROM pgbench_accounts WHERE aid>=35000001 AND aid <=38000000;
DELETE FROM pgbench_accounts WHERE aid>=40000001; 

另外，將 autovacuum_naptime 設定為 3min 以給洗掉資料留夠時間，

alvindb=> SHOW autovacuum_naptime;
 autovacuum_naptime 
--------------------
 3min
(1 row)

AUTOVACUUM 測驗

首先采用默認引數：

SHOW maintenance_work_mem;
 maintenance_work_mem 
----------------------
 64MB
(1 row)
SHOW autovacuum_work_mem;
 autovacuum_work_mem 
---------------------
 -1
(1 row)
SHOW autovacuum_vacuum_cost_delay;
 autovacuum_vacuum_cost_delay 
------------------------------
 20ms
(1 row)
SHOW autovacuum_vacuum_cost_limit;
 autovacuum_vacuum_cost_limit 
------------------------------
 -1
(1 row)
SHOW vacuum_cost_limit;
 vacuum_cost_limit 
-------------------
 200
(1 row)

執行測驗用例的同時，通過如下 SQL 查詢

SELECT * FROM pg_stat_activity WHERE backend_type ~ 'autovacuum worker' AND pid <> pg_backend_pid();\watch 1

可以看到， AUTOVACUUM 已觸發，并且 autovacuum worker 已啟動，先是 query 為空，而后 query 為 VACUUM ANALYZE public.pgbench_accounts，

2021年11月07日 星期日 23時17分09秒 (every 1s)

-[ RECORD 1 ]----+------------------------------
datid            | 37509
datname          | alvindb
pid              | 16660
usesysid         | 
usename          | 
application_name | 
client_addr      | 
client_hostname  | 
client_port      | 
backend_start    | 2021-11-07 23:17:09.427627+08
xact_start       | 2021-11-07 23:17:09.426378+08
query_start      | 
state_change     | 
wait_event_type  | 
wait_event       | 
state            | 
backend_xid      | 
backend_xmin     | 13180269
query            | 
backend_type     | autovacuum worker

2021年11月07日 星期日 23時17分10秒 (every 1s)

-[ RECORD 1 ]----+---------------------------------------------------
datid            | 37509
datname          | alvindb
pid              | 16660
usesysid         | 
usename          | 
application_name | 
client_addr      | 
client_hostname  | 
client_port      | 
backend_start    | 2021-11-07 23:17:09.427627+08
xact_start       | 2021-11-07 23:17:09.459083+08
query_start      | 2021-11-07 23:17:09.459083+08
state_change     | 2021-11-07 23:17:09.459084+08
wait_event_type  | 
wait_event       | 
state            | active
backend_xid      | 
backend_xmin     | 13180269
query            | autovacuum: VACUUM ANALYZE public.pgbench_accounts
backend_type     | autovacuum worker

完成后，根據 last_autoanalyze 和 last_autovacuum 得知是先做了 VACUUM，然后做 ANALYZE:

schemaname                      | public
relname                         | pgbench_accounts
autovacuum_vacuum_scale_factor  | 0.1
autovacuum_vacuum_threshold     | 2000
autovacuum_analyze_scale_factor | 0.05
autovacuum_analyze_threshold    | 2000
n_live_tup                      | 30000000
reltuples                       | 30000000
autovacuum_analyze_trigger      | 1502001
n_mod_since_analyze             | 0
rows_to_mod_before_analyze      | 1502001
last_autoanalyze                | 2021-11-07 23:22:41.640812+08
autovacuum_vacuum_trigger       | 3002001
n_dead_tup                      | 142848
rows_to_delete_before_vacuum    | 2859153
last_autovacuum                 | 2021-11-07 23:22:14.06792+08

查看 PostgreSQL 日志，得知 VACUUM 用時 304.60 s，ANALYZE 用時 27.56 s，

[    2021-11-07 23:22:14.067 CST 16660 6187edf5.4114 1 3/238595 13180270]LOG:  automatic vacuum of table "alvindb.public.pgbench_accounts": in
dex scans: 2
        pages: 35699 removed, 165914 remain, 576 skipped due to pins, 0 skipped frozen
        tuples: 6190880 removed, 30142834 remain, 0 are dead but not yet removable, oldest xmin: 13180269
        buffer usage: 419596 hits, 40795 misses, 100492 dirtied
        avg read rate: 4.185 MB/s, avg write rate: 10.310 MB/s
        system usage: CPU: user: 14.02 s, system: 1.41 s, elapsed: 304.60 s

[    2021-11-07 23:22:41.640 CST 16660 6187edf5.4114 2 3/238596 13180271]LOG:  automatic analyze of table "alvindb.public.pgbench_accounts" system usage: CPU: user: 3.25 s, system: 7.58 s, elapsed: 27.56 s

通過調整引數，逐步并反復測驗，結果如下：

根據如上測驗，可以驗證，適當增大 autovacuum_work_mem 和 autovacuum_vacuum_cost_limit、減少 autovacuum_vacuum_cost_delay 可提高 AUTOVACUUM 性能，

手動 VACUUM ANALYZE 測驗

下面測驗手動 VACUUM ANALYZE，測驗方法基本與 AUTOVACUUM 一樣，

這里主要簡單測驗下 maintenance_work_mem，其他不再重復測驗，

關閉 autovacuum 并將 vacuum_cost_delay 設定為 0，并手動執行如下 SQL:

VACUUM ANALYZE pgbench_accounts;

用時統計如下：

根據如上測驗，可以驗證，適當增大 maintenance_work_mem 和 vacuum_cost_limit、減少 vacuum_cost_delay 可提高 AUTOVACUUM 性能，

與 AUTOVACUUM 不同的是，手動 VACUUM 可以通過如下方式設定引數，這樣，在實際作業中，就可以靈活調整引數而不需要改組態檔了，

SET vacuum_cost_delay = 10;
VACUUM ANALYZE pgbench_accounts;

公眾號

關注 DBA Daily 公眾號，第一時間收到文章的更新，
通過一線 DBA 的日常作業，學習實用資料庫技術干貨！

公眾號優質文章推薦

PostgreSQL VACUUM 之深入淺出

華山論劍之 PostgreSQL sequence

[PG Upgrade Series] Extract Epoch Trap

[PG Upgrade Series] Toast Dump Error

GitLab supports only PostgreSQL now

MySQL or PostgreSQL?

PostgreSQL hstore Insight

ReIndex 失敗原因調查

PG 資料匯入 Hive 亂碼問題調查

PostGIS 擴展創建失敗原因調查

標籤：其他

上一篇：洗掉字典中具有相同值的所有鍵

下一篇：oracle洗掉超過N天資料腳本