PostgreSQL VACUUM 之深入淺出 (二)
- 2022 年 2 月 25 日
- 筆記
- PostgreSQL
AUTOVACUUM
AUTOVACUUM 簡介
PostgreSQL 提供了 AUTOVACUUM 的機制。
autovacuum 不僅會自動進行 VACUUM，也會自動進行 ANALYZE，以分析統計資訊用於執行計劃。
在 postgresql.conf 中，autovacuum 參數已默認打開。
autovacuum = on
autovacuum 打開後，會有一個 autovacuum launcher 進程
$ ps -ef|grep postgres|grep autovacuum|grep -v grep
postgres 28398 28392 0 Nov13 ? 00:00:19 postgres: autovacuum launcher
在 pg_stat_activity 也可以看到 backend_type 為 autovacuum launcher 的連接:
psql -d alvindb -U postgres
alvindb=# \x
Expanded display is on.
alvindb=# SELECT * FROM pg_stat_activity WHERE backend_type = 'autovacuum launcher';
-[ RECORD 1 ]----+------------------------------
datid |
datname |
pid | 28398
usesysid |
usename |
application_name |
client_addr |
client_hostname |
client_port |
backend_start | 2021-11-13 23:18:00.406618+08
xact_start |
query_start |
state_change |
wait_event_type | Activity
wait_event | AutoVacuumMain
state |
backend_xid |
backend_xmin |
query |
backend_type | autovacuum launcher
那麼 AUTOVACUUM 多久運行一次？
autovacuum launcher 會每隔 autovacuum_naptime ，創建 autovacuum worker，檢查是否需要做 autovacuum。
psql -d alvindb -U postgres
alvindb=# SELECT * FROM pg_stat_activity WHERE backend_type = 'autovacuum worker';
-[ RECORD 1 ]----+------------------------------
datid | 13220
datname | postgres
pid | 32457
usesysid |
usename |
application_name |
client_addr |
client_hostname |
client_port |
backend_start | 2021-11-06 23:32:53.880281+08
xact_start |
query_start |
state_change |
wait_event_type |
wait_event |
state |
backend_xid |
backend_xmin |
query |
backend_type | autovacuum worker
autovacuum_naptime 默認為 1min：
#autovacuum_naptime = 1min # time between autovacuum runs
autovacuum 又是根據什麼標準決定是否進行 VACUUM 和 ANALYZE 呢?
當 autovacuum worker 檢查到，
dead tuples 大於 vacuum threshold 時，會自動進行 VACUUM。
vacuum threshold 公式如下：
vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples
增刪改的行數據大於 analyze threshold 時，會自動進行 ANALYZE。
analyze threshold 公式如下：
analyze threshold = analyze base threshold + analyze scale factor * number of tuples
對應 postgresql.conf 中相關參數如下：
#autovacuum_vacuum_threshold = 50 # min number of row updates before vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
dead tuples 為 pg_stat_user_tables.n_dead_tup(Estimated number of dead rows)
alvindb=> SELECT * FROM pg_stat_user_tables WHERE schemaname = 'alvin' AND relname = 'tb_test_vacuum';
-[ RECORD 1 ]-------+---------------
relid | 37409
schemaname | alvin
relname | tb_test_vacuum
seq_scan | 2
seq_tup_read | 0
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 0
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 0
n_dead_tup | 0
n_mod_since_analyze | 0
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze |
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0
那麼 number of tuples 是哪個列的值？是 pg_stat_user_tables.n_live_tup(Estimate number of live rows)？還是實際的 count 值?
其實是 pg_class.reltuples (Estimate number of live rows in the table used by the planner)。
alvindb=> SELECT u.schemaname,u.relname,c.reltuples,u.n_live_tup,u.n_mod_since_analyze,u.n_dead_tup,u.last_autoanalyze,u.last_autovacuum
FROM
pg_stat_user_tables u, pg_class c, pg_namespace n
WHERE n.oid = c.relnamespace
AND c.relname = u.relname
AND n.nspname = u.schemaname
AND u.schemaname = 'alvin'
AND u.relname = 'tb_test_vacuum'
-[ RECORD 1 ]-------+---------------
schemaname | alvin
relname | tb_test_vacuum
reltuples | 0
n_live_tup | 0
n_mod_since_analyze | 0
n_dead_tup | 0
last_autoanalyze |
last_autovacuum |
所以 AUTO VACUUM 具體公式如下：
pg_stat_user_tables.n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * pg_class.reltuples
同理，AUTO ANALYZE 具體公式如下：
pg_stat_user_tables.n_mod_since_analyze > autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * pg_class.reltuples
精準觸發 AUTOVACUUM
下面實測一下 autovacuum。為了測試方便，autovacuum_naptime 臨時修改為 5s，這樣觸發了臨界條件，只需要等 5s 就能看到效果，而不是等 1min。
修改參數如下:
autovacuum_naptime = 5s
autovacuum_vacuum_threshold = 100 # min number of row updates before vacuum
autovacuum_analyze_threshold = 100 # min number of row updates before analyze
autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
接下來通過一步一步測試，精準觸發 autovacuum。
為了方便測試，通過如下 AUTOVACUUM 計算 SQL 計算需要刪除或修改的數據行數。
alvindb=> WITH v AS (
SELECT * FROM
(SELECT setting AS autovacuum_vacuum_scale_factor FROM pg_settings WHERE name = 'autovacuum_vacuum_scale_factor') vsf,
(SELECT setting AS autovacuum_vacuum_threshold FROM pg_settings WHERE name = 'autovacuum_vacuum_threshold') vth,
(SELECT setting AS autovacuum_analyze_scale_factor FROM pg_settings WHERE name = 'autovacuum_analyze_scale_factor') asf,
(SELECT setting AS autovacuum_analyze_threshold FROM pg_settings WHERE name = 'autovacuum_analyze_threshold') ath
),
t AS (
SELECT
c.reltuples,u.*
FROM
pg_stat_user_tables u, pg_class c, pg_namespace n
WHERE n.oid = c.relnamespace
AND c.relname = u.relname
AND n.nspname = u.schemaname
AND u.schemaname = 'alvin'
AND u.relname = 'tb_test_vacuum'
)
SELECT
schemaname,
relname,
autovacuum_vacuum_scale_factor,
autovacuum_vacuum_threshold,
autovacuum_analyze_scale_factor,
autovacuum_analyze_threshold,
n_live_tup,
reltuples,
autovacuum_analyze_trigger,
n_mod_since_analyze,
autovacuum_analyze_trigger - n_mod_since_analyze AS rows_to_mod_before_auto_analyze,
last_autoanalyze,
autovacuum_vacuum_trigger,
n_dead_tup,
autovacuum_vacuum_trigger - n_dead_tup AS rows_to_delete_before_auto_vacuum,
last_autovacuum
FROM (
SELECT
schemaname,
relname,
autovacuum_vacuum_scale_factor,
autovacuum_vacuum_threshold,
autovacuum_analyze_scale_factor,
autovacuum_analyze_threshold,
floor(autovacuum_analyze_scale_factor::numeric * reltuples) + 1 + autovacuum_analyze_threshold::int AS autovacuum_analyze_trigger,
floor(autovacuum_vacuum_scale_factor::numeric * reltuples) + 1 + autovacuum_vacuum_threshold::int AS autovacuum_vacuum_trigger,
reltuples,
n_live_tup,
n_dead_tup,
n_mod_since_analyze,
last_autoanalyze,
last_autovacuum
FROM
v,
t) a;
-[ RECORD 1 ]---------------------+---------------
schemaname | alvin
relname | tb_test_vacuum
autovacuum_vacuum_scale_factor | 0.2
autovacuum_vacuum_threshold | 100
autovacuum_analyze_scale_factor | 0.1
autovacuum_analyze_threshold | 100
n_live_tup | 0
reltuples | 0
autovacuum_analyze_trigger | 101
n_mod_since_analyze | 0
rows_to_mod_before_auto_analyze | 101
last_autoanalyze |
autovacuum_vacuum_trigger | 101
n_dead_tup | 0
rows_to_delete_before_auto_vacuum | 101
last_autovacuum |
根據計算公式，
pg_stat_user_tables.n_mod_since_analyze > 100 + 0.1 * 0
即當修改的行數大於 100，即為 101 時，將觸發 AUTO ANALYZE。
先插入 100 行數據，
alvindb=> SELECT clock_timestamp();
clock_timestamp
-------------------------------
2021-11-06 20:45:57.669183+08
(1 row)
alvindb=> INSERT INTO tb_test_vacuum(test_num) SELECT gid FROM generate_series(1,100,1) gid;
INSERT 0 100
此時，通過如下計算可以看到，再更新 1 行，將觸發 AUTO ANALYZE。
schemaname | alvin
relname | tb_test_vacuum
autovacuum_vacuum_scale_factor | 0.2
autovacuum_vacuum_threshold | 100
autovacuum_analyze_scale_factor | 0.1
autovacuum_analyze_threshold | 100
n_live_tup | 100
reltuples | 0
autovacuum_analyze_trigger | 101
n_mod_since_analyze | 100
rows_to_mod_before_auto_analyze | 1
last_autoanalyze |
autovacuum_vacuum_trigger | 101
n_dead_tup | 0
rows_to_delete_before_auto_vacuum | 101
last_autovacuum |
此時，統計資訊為空:
alvindb=> SELECT * FROM pg_stats WHERE schemaname = 'alvin' AND tablename = 'tb_test_vacuum';
(0 rows)
現在插入最後一條數據，
alvindb=> SELECT clock_timestamp();
clock_timestamp
-------------------------------
2021-11-06 20:46:31.034422+08
(1 row)
alvindb=> INSERT INTO tb_test_vacuum(test_num) SELECT gid FROM generate_series(101,101,1) gid;
INSERT 0 1
執行 AUTOVACUUM 計算 SQL， 可以看到，已觸發 AUTO ANALYZE:
schemaname | alvin
relname | tb_test_vacuum
autovacuum_vacuum_scale_factor | 0.2
autovacuum_vacuum_threshold | 100
autovacuum_analyze_scale_factor | 0.1
autovacuum_analyze_threshold | 100
n_live_tup | 101
reltuples | 101
autovacuum_analyze_trigger | 111
n_mod_since_analyze | 0
rows_to_mod_before_auto_analyze | 111
last_autoanalyze | 2021-11-06 20:46:39.88796+08
autovacuum_vacuum_trigger | 121
n_dead_tup | 0
rows_to_delete_before_auto_vacuum | 121
last_autovacuum |
可以看到表 tb_test_vacuum 統計資訊已更新：
alvindb=> SELECT * FROM pg_stats WHERE schemaname = 'alvin' AND tablename = 'tb_test_vacuum';
查看 PostgreSQL 日誌，可以看到
[ 2021-11-06 20:46:39.887 CST 6816 6186792f.1aa0 1 3/173948 13179359]LOG: automatic analyze of table "alvindb.alvin.tb_test_vacuum" system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
PostgreSQL 日誌中是否記錄 AUTOVACUUM 由參數 log_autovacuum_min_duration 控制，默認關閉。
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
可將該參數改為 0，即記錄所有的 AUTOVACUUM 操作。
log_autovacuum_min_duration = 0
從 AUTOVACUUM 計算 SQL 的執行結果得知，再修改 111 行將觸發 AUTO ANALYZE。
rows_to_mod_before_auto_analyze | 111
rows_to_delete_before_auto_vacuum | 121
先修改 110 行，並 sleep 6s。
alvindb=> SELECT clock_timestamp();
clock_timestamp
------------------------------
2021-11-06 20:47:30.75553+08
(1 row)
alvindb=> INSERT INTO tb_test_vacuum(test_num) SELECT gid FROM generate_series(102,111,1) gid;
INSERT 0 10
alvindb=> UPDATE tb_test_vacuum SET test_num = test_num WHERE test_num <= 100;
UPDATE 100
alvindb=> SELECT pg_sleep(6);
pg_sleep
----------
(1 row)
alvindb=> SELECT clock_timestamp();
clock_timestamp
-------------------------------
2021-11-06 20:47:43.465651+08
(1 row)
從 AUTOVACUUM 計算 SQL 的執行結果得知，修改後 110 行並 sleep 6s (前面已將 autovacuum_naptime 設置成了 5s)後，AUTO ANALYZE 並未觸發。
schemaname | alvin
relname | tb_test_vacuum
autovacuum_vacuum_scale_factor | 0.2
autovacuum_vacuum_threshold | 100
autovacuum_analyze_scale_factor | 0.1
autovacuum_analyze_threshold | 100
n_live_tup | 111
reltuples | 101
autovacuum_analyze_trigger | 111
n_mod_since_analyze | 110
rows_to_mod_before_auto_analyze | 1
last_autoanalyze | 2021-11-06 20:46:39.88796+08
autovacuum_vacuum_trigger | 121
n_dead_tup | 100
rows_to_delete_before_auto_vacuum | 21
last_autovacuum |
再修改 1 行預計將觸發 AUTO ANALYZE。此時刪除一行：
alvindb=> SELECT clock_timestamp();
clock_timestamp
-------------------------------
2021-11-06 20:47:55.746411+08
(1 row)
alvindb=> DELETE FROM tb_test_vacuum WHERE test_id = 111;
DELETE 1
alvindb=> SELECT pg_sleep(6);
pg_sleep
----------
(1 row)
alvindb=> SELECT clock_timestamp();
clock_timestamp
-------------------------------
2021-11-06 20:48:01.796389+08
(1 row)
從 AUTOVACUUM 計算 SQL 的查詢結果中的 last_autoanalyze 得知，已精準觸發 AUTO ANALYZE。
並且從 rows_to_delete_before_auto_vacuum 得知，預計刪除 22 行後，將觸發 AUTO VACUUM。
schemaname | alvin
relname | tb_test_vacuum
autovacuum_vacuum_scale_factor | 0.2
autovacuum_vacuum_threshold | 100
autovacuum_analyze_scale_factor | 0.1
autovacuum_analyze_threshold | 100
n_live_tup | 110
reltuples | 110
autovacuum_analyze_trigger | 112
n_mod_since_analyze | 0
rows_to_mod_before_auto_analyze | 112
last_autoanalyze | 2021-11-06 20:48:04.928899+08
autovacuum_vacuum_trigger | 123
n_dead_tup | 101
rows_to_delete_before_auto_vacuum | 22
last_autovacuum |
先刪除 (UPDATE = DELETE + INSERT) 21 行：
alvindb=> SELECT clock_timestamp();
clock_timestamp
-------------------------------
2021-11-06 20:48:32.313706+08
(1 row)
alvindb=> UPDATE tb_test_vacuum SET test_num = test_num WHERE test_num <= 21;
UPDATE 21
alvindb=> SELECT pg_sleep(6);
pg_sleep
----------
(1 row)
alvindb=> SELECT clock_timestamp();
clock_timestamp
-------------------------------
2021-11-06 20:48:38.454997+08
(1 row)
從 AUTOVACUUM 計算 SQL 的查詢結果中的 last_autovacuum 得知，還未觸發 AUTO VACUUM。
並且從 rows_to_delete_before_auto_vacuum 得知，預計刪除 1 行後，將觸發 AUTO VACUUM。
schemaname | alvin
relname | tb_test_vacuum
autovacuum_vacuum_scale_factor | 0.2
autovacuum_vacuum_threshold | 100
autovacuum_analyze_scale_factor | 0.1
autovacuum_analyze_threshold | 100
n_live_tup | 110
reltuples | 110
autovacuum_analyze_trigger | 112
n_mod_since_analyze | 21
rows_to_mod_before_auto_analyze | 91
last_autoanalyze | 2021-11-06 20:48:04.928899+08
autovacuum_vacuum_trigger | 123
n_dead_tup | 122
rows_to_delete_before_auto_vacuum | 1
last_autovacuum |
此時刪除一行
alvindb=> SELECT clock_timestamp();
clock_timestamp
-------------------------------
2021-11-06 20:48:39.174009+08
(1 row)
alvindb=> DELETE FROM tb_test_vacuum WHERE test_id = 110;
DELETE 1
alvindb=> SELECT pg_sleep(6);
pg_sleep
----------
(1 row)
alvindb=> SELECT clock_timestamp();
clock_timestamp
-------------------------------
2021-11-06 20:48:45.213537+08
(1 row)
從 AUTOVACUUM 計算 SQL 的查詢結果中的 last_autovacuum 得知，已精準觸發 AUTO VACUUM！
schemaname | alvin
relname | tb_test_vacuum
autovacuum_vacuum_scale_factor | 0.2
autovacuum_vacuum_threshold | 100
autovacuum_analyze_scale_factor | 0.1
autovacuum_analyze_threshold | 100
n_live_tup | 109
reltuples | 109
autovacuum_analyze_trigger | 111
n_mod_since_analyze | 22
rows_to_mod_before_auto_analyze | 89
last_autoanalyze | 2021-11-06 20:48:04.928899+08
autovacuum_vacuum_trigger | 122
n_dead_tup | 0
rows_to_delete_before_auto_vacuum | 122
last_autovacuum | 2021-11-06 20:48:49.914345+08
查看 PostgreSQL 日誌，可以看到
[ 2021-11-06 20:48:49.914 CST 7207 618679b1.1c27 1 3/174162 0]LOG: automatic vacuum of table "alvindb.alvin.tb_test_vacuum": index scans: 1
pages: 0 removed, 1 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 123 removed, 109 remain, 0 are dead but not yet removable, oldest xmin: 13179371
buffer usage: 59 hits, 4 misses, 4 dirtied
avg read rate: 121.832 MB/s, avg write rate: 121.832 MB/s
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
buffer usage: 59 hits, 4 misses, 4 dirtied
avg read rate: 121.832 MB/s, avg write rate: 121.832 MB/s
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
那麼問題來了，autovacuum_vacuum_scale_factor 為 0.2 對於所有的表都合適嗎？1 億數據量的表有 2000 萬 dead tuples 以上才會觸發 AUTO VACUUM，這意味著表越大越不容易觸發 AUTO VACUUM。怎麼可以解決這個問題呢？
精準觸發表級 AUTOVACUUM
可以根據需要，在表上設置合理的 autovacuum_vacuum_scale_factor。對於大表，可以設置小點的 autovacuum_vacuum_scale_factor，如 0.1。
下面帶你一步一步設置並精確觸發表級的 AUTO ANALYZE 和 AUTO VACUUM。
這次將採用大一點的數據量進行測試。考慮到手動創建表，插入數據等比較麻煩，接下來測試利用 PostgreSQL 自帶的工具 pgbench。
使用 pgbench 創建 10 萬行數據的測試表：
$ pgbench -i alvindb
dropping old tables...
creating tables...
generating data...
100000 of 100000 tuples (100%) done (elapsed 0.38 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.
修改表級參數：
alvindb=> ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_scale_factor = 0.1, autovacuum_vacuum_threshold = 2000);
ALTER TABLE
alvindb=> ALTER TABLE pgbench_accounts SET (autovacuum_analyze_scale_factor = 0.05, autovacuum_analyze_threshold = 2000);
ALTER TABLE
按照之前 AUTOVACUUM 計算 SQL ，可知要修改 11001 行才會觸發 AUTO ANALYZE， 要有約 21001 個 dead tuples 才會觸發 AUTO VACUUM。
schemaname | public
relname | pgbench_accounts
autovacuum_vacuum_scale_factor | 0.2
autovacuum_vacuum_threshold | 1000
autovacuum_analyze_scale_factor | 0.1
autovacuum_analyze_threshold | 1000
n_live_tup | 100000
reltuples | 100000
autovacuum_analyze_trigger | 11001
n_mod_since_analyze | 0
rows_to_mod_before_auto_analyze | 11001
last_autoanalyze |
autovacuum_vacuum_trigger | 21001
n_dead_tup | 0
rows_to_delete_before_auto_vacuum | 21001
last_autovacuum |
現在設置了表級的參數以後，從如下 表級 AUTOVACUUM 計算 SQL ，可知修改 7001 行就可以觸發 AUTO ANALYZE， 有約 12001 個 dead tuples 就可以觸發 AUTO VACUUM。更重要的是，表級的 AUTOVACUUM 參數不會對其他表產生影響，只對已設置的表有效，也可以對不同大小的表設置不同的參數，還可以隨時調整！
表級 AUTOVACUUM 計算 SQL
alvindb=> WITH v AS (
SELECT (SELECT split_part(x, '=', 2) FROM unnest(c.reloptions) q (x) WHERE x ~ '^autovacuum_vacuum_scale_factor=' ) as autovacuum_vacuum_
scale_factor,
(SELECT split_part(x, '=', 2) FROM unnest(c.reloptions) q (x) WHERE x ~ '^autovacuum_vacuum_threshold=' ) as autovacuum_vacuum_thresh
old,
(SELECT split_part(x, '=', 2) FROM unnest(c.reloptions) q (x) WHERE x ~ '^autovacuum_analyze_scale_factor=' ) as autovacuum_analyze_s
cale_factor,
(SELECT split_part(x, '=', 2) FROM unnest(c.reloptions) q (x) WHERE x ~ '^autovacuum_analyze_threshold=' ) as autovacuum_analyze_thre
shold
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname IN ('public')
AND c.relname = 'pgbench_accounts'
),
t AS (
SELECT
c.reltuples,u.*
FROM
pg_stat_user_tables u, pg_class c, pg_namespace n
WHERE n.oid = c.relnamespace
AND c.relname = u.relname
AND n.nspname = u.schemaname
AND u.schemaname = 'public'
AND u.relname = 'pgbench_accounts'
)
SELECT
schemaname,
relname,
autovacuum_vacuum_scale_factor,
autovacuum_vacuum_threshold,
autovacuum_analyze_scale_factor,
autovacuum_analyze_threshold,
n_live_tup,
reltuples,
autovacuum_analyze_trigger,
n_mod_since_analyze,
autovacuum_analyze_trigger - n_mod_since_analyze AS rows_to_mod_before_analyze,
last_autoanalyze,
autovacuum_vacuum_trigger,
n_dead_tup,
autovacuum_vacuum_trigger - n_dead_tup AS rows_to_delete_before_vacuum,
last_autovacuum
FROM (
SELECT
schemaname,
relname,
autovacuum_vacuum_scale_factor,
autovacuum_vacuum_threshold,
autovacuum_analyze_scale_factor,
autovacuum_analyze_threshold,
floor(autovacuum_analyze_scale_factor::numeric * reltuples) + 1 + autovacuum_analyze_threshold::int AS autovacuum_analyze_trigger,
floor(autovacuum_vacuum_scale_factor::numeric * reltuples) + 1 + autovacuum_vacuum_threshold::int AS autovacuum_vacuum_trigger,
reltuples,
n_live_tup,
n_dead_tup,
n_mod_since_analyze,
last_autoanalyze,
last_autovacuum
FROM
v,
t) a;
-[ RECORD 1 ]-------------------+-----------------
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 | 100000
reltuples | 100000
autovacuum_analyze_trigger | 7001
n_mod_since_analyze | 0
rows_to_mod_before_analyze | 7001
last_autoanalyze |
autovacuum_vacuum_trigger | 12001
n_dead_tup | 0
rows_to_delete_before_vacuum | 12001
last_autovacuum |
現在已預測到要修改的行數，接下來一步一步來觸發一下表級的 AUTO ANALYZE 和 AUTO VACUUM。
先刪除 7000 行數據：
alvindb=> SELECT clock_timestamp();
clock_timestamp
-------------------------------
2021-11-06 23:33:03.252622+08
(1 row)
alvindb=> DELETE FROM pgbench_accounts WHERE aid<=7000;
DELETE 7000
alvindb=> SELECT pg_sleep(6);
pg_sleep
----------
(1 row)
alvindb=> SELECT clock_timestamp();
clock_timestamp
-------------------------------
2021-11-06 23:33:09.363536+08
(1 row)
根據表級 AUTOVACUUM 計算 SQL 執行結果的 rows_to_mod_before_analyze 得知，再修改 1 行將觸發 AUTO 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 | 93000
reltuples | 100000
autovacuum_analyze_trigger | 7001
n_mod_since_analyze | 7000
rows_to_mod_before_analyze | 1
last_autoanalyze |
autovacuum_vacuum_trigger | 12001
n_dead_tup | 7000
rows_to_delete_before_vacuum | 5001
last_autovacuum |
再修改 1 行：
alvindb=> SELECT clock_timestamp();
clock_timestamp
-------------------------------
2021-11-06 23:33:30.649717+08
(1 row)
alvindb=> UPDATE pgbench_accounts SET bid = bid WHERE aid=7001;
UPDATE 1
alvindb=> SELECT pg_sleep(6);
pg_sleep
----------
(1 row)
alvindb=> SELECT clock_timestamp();
clock_timestamp
-------------------------------
2021-11-06 23:33:36.705928+08
(1 row)
根據表級 AUTOVACUUM 計算 SQL 執行結果的 last_autoanalyze 得知，已精準觸發 AUTO 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 | 93000
reltuples | 93000
autovacuum_analyze_trigger | 6651
n_mod_since_analyze | 0
rows_to_mod_before_analyze | 6651
last_autoanalyze | 2021-11-06 23:33:40.87317+08
autovacuum_vacuum_trigger | 11301
n_dead_tup | 7001
rows_to_delete_before_vacuum | 4300
last_autovacuum |
從 PostgreSQL 日誌中也可以看到 AUTO ANALYZE 被觸發了：
[ 2021-11-06 23:33:40.873 CST 32646 6186a054.7f86 1 6/1393 13179750]LOG: automatic analyze of table "alvindb.public.pgbench_accounts" syst
em usage: CPU: user: 0.04 s, system: 0.03 s, elapsed: 0.11 s
並且，根據 rows_to_delete_before_vacuum 得知，再刪除 4300 行就可以觸發 AUTO VACUUM。
接下來先刪除 4299 行，以測試臨界值：
alvindb=> SELECT clock_timestamp();
clock_timestamp
-------------------------------
2021-11-06 23:33:43.867176+08
(1 row)
alvindb=> UPDATE pgbench_accounts SET bid = bid WHERE aid>=95702;
UPDATE 4299
alvindb=> SELECT pg_sleep(6);
pg_sleep
----------
(1 row)
alvindb=> SELECT clock_timestamp();
clock_timestamp
-------------------------------
2021-11-06 23:33:50.016447+08
(1 row)
autovacuum_naptime 為 5s，此時並未觸發 AUTO VACUUM。
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 | 93000
reltuples | 93000
autovacuum_analyze_trigger | 6651
n_mod_since_analyze | 4299
rows_to_mod_before_analyze | 2352
last_autoanalyze | 2021-11-06 23:33:40.87317+08
autovacuum_vacuum_trigger | 11301
n_dead_tup | 11300
rows_to_delete_before_vacuum | 1
last_autovacuum |
再刪除 (UPDATE = DELETE + INSERT) 1 行 :
alvindb=> SELECT clock_timestamp();
clock_timestamp
-------------------------------
2021-11-06 23:33:53.326483+08
(1 row)
alvindb=> UPDATE pgbench_accounts SET bid = bid WHERE aid=7002;
UPDATE 1
alvindb=> SELECT pg_sleep(6);
pg_sleep
----------
(1 row)
alvindb=> SELECT clock_timestamp();
clock_timestamp
-------------------------------
2021-11-06 23:33:59.439375+08
(1 row)
從如下結果中的 last_autovacuum 得知，此時已精確觸發 AUTO VACUUM！
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 | 93000
reltuples | 93000
autovacuum_analyze_trigger | 6651
n_mod_since_analyze | 4300
rows_to_mod_before_analyze | 2351
last_autoanalyze | 2021-11-06 23:33:40.87317+08
autovacuum_vacuum_trigger | 11301
n_dead_tup | 0
rows_to_delete_before_vacuum | 11301
last_autovacuum | 2021-11-06 23:34:00.956936+08
從 PostgreSQL 日誌中也可以看到 AUTO VACUUM 被觸發了：
[ 2021-11-06 23:34:00.956 CST 32710 6186a068.7fc6 1 6/1455 0]LOG: automatic vacuum of table "alvindb.public.pgbench_accounts": index scans
: 1
pages: 0 removed, 421 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 2 removed, 93000 remain, 0 are dead but not yet removable, oldest xmin: 13179755
buffer usage: 967 hits, 60 misses, 7 dirtied
avg read rate: 10.067 MB/s, avg write rate: 1.174 MB/s
system usage: CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.18 s
公眾號
關注 DBA Daily 公眾號，第一時間收到文章的更新。
通過一線 DBA 的日常工作，學習實用資料庫技術乾貨！
公眾號優質文章推薦
[PG Upgrade Series] Extract Epoch Trap
[PG Upgrade Series] Toast Dump Error
GitLab supports only PostgreSQL now
