溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要講解了“PostgreSQL中怎么監控VACUUM的處理過程”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“PostgreSQL中怎么監控VACUUM的處理過程”吧！
概覽
PG的MVCC要求“過期”的數據不能馬上被物理清除，而是標記為dead rows，這些dead rows后續會通過vacuuming過程清理。
vacuuming通過以下方式讓數據庫保持健康：
1.標記dead rows可用于存儲新數據，這樣可以避免不必要的磁盤浪費以及可以跳過dead rows以提升順序掃描的性能；
2.更新vm（用于跟蹤過期或已廢棄的數據，反應在pages上）。這可以提升index-only scans的性能；
3.避免出現事務ID回卷失敗。
PG提供了autovacuum機制，通過周期性的運行ANALYZE來收集最近頻繁更新的數據表統計信息。
監控指標
為了讓VACUUMs平滑運行，應該監控以下幾個指標：
1.dead rows
2.table disk usage
3.VACUUM/AUTOVACUUM最近執行的時間
4.監控vacuum full
dead rows
PG提供了pg_stat_user_tables視圖用于監控dead rows
[local:/data/run/pg12]:5120 pg12@testdb=# \d pg_stat_user_tables View "pg_catalog.pg_stat_user_tables" Column | Type | Collation | Nullable | Default ---------------------+--------------------------+-----------+----------+--------- relid | oid | | | schemaname | name | | | relname | name | | | seq_scan | bigint | | | seq_tup_read | bigint | | | idx_scan | bigint | | | idx_tup_fetch | bigint | | | n_tup_ins | bigint | | | n_tup_upd | bigint | | | n_tup_del | bigint | | | n_tup_hot_upd | bigint | | | n_live_tup | bigint | | | n_dead_tup | bigint | | | n_mod_since_analyze | bigint | | | last_vacuum | timestamp with time zone | | | last_autovacuum | timestamp with time zone | | | last_analyze | timestamp with time zone | | | last_autoanalyze | timestamp with time zone | | | vacuum_count | bigint | | | autovacuum_count | bigint | | | analyze_count | bigint | | | autoanalyze_count | bigint | | | [local:/data/run/pg12]:5120 pg12@testdb=# update t1 set id = 2; UPDATE 20000 [local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname,relname, n_dead_tup FROM pg_stat_user_tables; schemaname | relname | n_dead_tup ------------+--------------------+------------ public | tbl | 0 public | t2 | 0 public | b | 0 public | a | 0 public | rel | 0 public | t_count | 0 public | t_big_autovacuum_1 | 0 public | t_autovacuum_1 | 0 public | t1 | 20000 (9 rows)
監控每張表的dead rows，特別是監控頻繁更新的表上，這樣有助于DBA確定VACUUM進程是否已有效的周期性的清除這些dead rows。
Table disk usage
在出現dead rows時，磁盤空間會逐步增大，vacuuming執行后可標記dead rows為空閑空間，通過監控空間的變化
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT
relname AS "table_name",
pg_size_pretty(pg_table_size(C.oid)) AS "table_size"
FROM
pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND nspname !~ '^pg_toast' AND relkind IN ('r')
ORDER BY pg_table_size(C.oid)
DESC;
table_name | table_size
--------------------+------------
rel | 845 MB
t_big_autovacuum_1 | 498 MB
tbl | 100 MB
a | 65 MB
b | 65 MB
t1 | 1456 kB
t_autovacuum_1 | 504 kB
t2 | 360 kB
t_count | 64 kB
(9 rows)
[local:/data/run/pg12]:5120 pg12@testdb=# update t1 set id = 4;
UPDATE 20000
[local:/data/run/pg12]:5120 pg12@testdb=# update t1 set id = 5;
UPDATE 20000
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT
relname AS "table_name",
pg_size_pretty(pg_table_size(C.oid)) AS "table_size"
FROM
pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND nspname !~ '^pg_toast' AND relkind IN ('r')
ORDER BY pg_table_size(C.oid)
DESC;
table_name | table_size
--------------------+------------
rel | 845 MB
t_big_autovacuum_1 | 498 MB
tbl | 100 MB
a | 65 MB
b | 65 MB
t1 | 2864 kB
t_autovacuum_1 | 504 kB
t2 | 360 kB
t_count | 64 kB
(9 rows)對t1執行全量更新，然后執行vacuum t1后再次插入等量的數據
[local:/data/run/pg12]:5120 pg12@testdb=# vacuum t1;
VACUUM
[local:/data/run/pg12]:5120 pg12@testdb=# select count(*) from t1;
count
-------
20000
(1 row)
[local:/data/run/pg12]:5120 pg12@testdb=# \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
[local:/data/run/pg12]:5120 pg12@testdb=# insert into t1 select generate_series(1,20000);
INSERT 0 20000
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT
relname AS "table_name",
pg_size_pretty(pg_table_size(C.oid)) AS "table_size"
FROM
pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND nspname !~ '^pg_toast' AND relkind IN ('r')
ORDER BY pg_table_size(C.oid)
DESC;
table_name | table_size
--------------------+------------
rel | 845 MB
t_big_autovacuum_1 | 498 MB
tbl | 100 MB
a | 65 MB
b | 65 MB
t1 | 2864 kB
t_autovacuum_1 | 504 kB
t2 | 360 kB
t_count | 64 kB
(9 rows)可以看到table占用的空間并沒有出現變化，原因是新的rows使用了dead rows的空間。
如不執行vacuum直接插入，則明顯可以看到table size的變化。
[local:/data/run/pg12]:5120 pg12@testdb=# update t1 set id = 10;
UPDATE 60000
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT
relname AS "table_name",
pg_size_pretty(pg_table_size(C.oid)) AS "table_size"
FROM
pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND nspname !~ '^pg_toast' AND relkind IN ('r')
ORDER BY pg_table_size(C.oid)
DESC;
table_name | table_size
--------------------+------------
rel | 845 MB
t_big_autovacuum_1 | 498 MB
tbl | 100 MB
a | 65 MB
b | 65 MB
t1 | 4288 kB -->這是原占用空間
t_autovacuum_1 | 504 kB
t2 | 360 kB
t_count | 64 kB
(9 rows)
[local:/data/run/pg12]:5120 pg12@testdb=# insert into t1 select generate_series(1,20000);
INSERT 0 20000
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT
relname AS "table_name",
pg_size_pretty(pg_table_size(C.oid)) AS "table_size"
FROM
pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND nspname !~ '^pg_toast' AND relkind IN ('r')
ORDER BY pg_table_size(C.oid)
DESC;
table_name | table_size
--------------------+------------
rel | 845 MB
t_big_autovacuum_1 | 498 MB
tbl | 100 MB
a | 65 MB
b | 65 MB
t1 | 4992 kB --> 新增占用空間
t_autovacuum_1 | 504 kB
t2 | 360 kB
t_count | 64 kB
(9 rows)Last time (auto)vacuum ran
PG提供了pg_stat_user_tables 視圖用于監控最近一次vacuum運行的時間。
[local:/data/run/pg12]:5120 pg12@testdb=# \d pg_stat_user_tables View "pg_catalog.pg_stat_user_tables" Column | Type | Collation | Nullable | Default ---------------------+--------------------------+-----------+----------+--------- relid | oid | | | schemaname | name | | | relname | name | | | seq_scan | bigint | | | seq_tup_read | bigint | | | idx_scan | bigint | | | idx_tup_fetch | bigint | | | n_tup_ins | bigint | | | n_tup_upd | bigint | | | n_tup_del | bigint | | | n_tup_hot_upd | bigint | | | n_live_tup | bigint | | | n_dead_tup | bigint | | | n_mod_since_analyze | bigint | | | last_vacuum | timestamp with time zone | | | last_autovacuum | timestamp with time zone | | | last_analyze | timestamp with time zone | | | last_autoanalyze | timestamp with time zone | | | vacuum_count | bigint | | | autovacuum_count | bigint | | | analyze_count | bigint | | | autoanalyze_count | bigint | | | [local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables; schemaname | relname | last_vacuum | last_autovacuum ------------+--------------------+-------------------------------+------------------------------- public | tbl | | public | t2 | | public | b | | public | a | | public | rel | | public | t_count | | public | t_big_autovacuum_1 | | public | t_autovacuum_1 | | public | t1 | 2019-12-10 14:29:31.420908+08 | 2019-12-10 14:32:02.639873+08 (9 rows)
可以看到最近一次的vacumm是14:29:31，而最近一次的autovacuum是14:32:02，autovacuum默認60s執行一次，其他沒有變化的表PG不會執行autovacuum。
[local:/data/run/pg12]:5120 pg12@testdb=# select name,setting from pg_settings where name like '%autovacuum%'; name | setting -------------------------------------+----------- autovacuum | on autovacuum_analyze_scale_factor | 0.1 autovacuum_analyze_threshold | 50 autovacuum_freeze_max_age | 200000000 autovacuum_max_workers | 3 autovacuum_multixact_freeze_max_age | 400000000 autovacuum_naptime | 60 --> 60s autovacuum_vacuum_cost_delay | 2 autovacuum_vacuum_cost_limit | -1 autovacuum_vacuum_scale_factor | 0.2 autovacuum_vacuum_threshold | 50 autovacuum_work_mem | -1 log_autovacuum_min_duration | -1 (13 rows)
執行update操作，60s后再次查詢，發現last_autovacuum已更新。
[local:/data/run/pg12]:5120 pg12@testdb=# update t1 set id = 1; UPDATE 80000 [local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables; schemaname | relname | last_vacuum | last_autovacuum ------------+--------------------+-------------------------------+------------------------------- public | tbl | | public | t2 | | public | b | | public | a | | public | rel | | public | t_count | | public | t_big_autovacuum_1 | | public | t_autovacuum_1 | | public | t1 | 2019-12-10 14:29:31.420908+08 | 2019-12-10 14:38:02.771566+08 (9 rows)
監控vacuum full
通過視圖pg_stat_progress_vacuum可監控vacuum full的進度
[local:/data/run/pg12]:5120 pg12@testdb=# \d pg_stat_progress_vacuum View "pg_catalog.pg_stat_progress_vacuum" Column | Type | Collation | Nullable | Default --------------------+---------+-----------+----------+--------- pid | integer | | | datid | oid | | | datname | name | | | relid | oid | | | phase | text | | | heap_blks_total | bigint | | | heap_blks_scanned | bigint | | | heap_blks_vacuumed | bigint | | | index_vacuum_count | bigint | | | max_dead_tuples | bigint | | | num_dead_tuples | bigint | | | [local:/data/run/pg12]:5120 pg12@testdb=#
VACUUM的相關主題
如果上述指標提示VACUUMs沒有正常執行，可以通過查詢設置可發現問題所在，包括：
1.The autovacuum process is disabled on your database
2.The autovacuum process is disabled on one or more tables
3.Autovacuuming settings aren’t keeping pace with updates
4.Lock conflicts
5.Long-running open transactions
1.The autovacuum process is disabled on your database
通過執行ps -axww | grep autovacuum命令可監控autovacuum是否正在運行
[root@localhost ~]# ps -axww | grep autovacuum 55958 ? Ss 0:00 postgres: autovacuum launcher 56057 pts/4 S+ 0:00 grep --color=auto autovacuum [root@localhost ~]#
同時亦可通過查詢pg_settings獲得
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT name, setting FROM pg_settings WHERE name='autovacuum'; name | setting ------------+--------- autovacuum | on (1 row)
如autovacuum已開啟，但結果沒有如我們預期，那么問題可能出現在statistics collector上面，autovacuum依賴statistics collector用于確定何時以及間隔多少時間應該運行。通常來說，statistics collector應啟用，但如果禁用此項，對autovacuum的正常運行會有較大影響。通過檢查track_counts配置項來檢查statistics collector是否啟用。
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT name, setting FROM pg_settings WHERE name='track_counts'; name | setting --------------+--------- track_counts | on (1 row) [local:/data/run/pg12]:5120 pg12@testdb=#
如track_counts為OFF，則statistics collector不會更新dead rows信息，而該項是autovacuum所依賴的信息。
[local:/data/run/pg12]:5120 pg12@testdb=# set track_counts=off; SET [local:/data/run/pg12]:5120 pg12@testdb=# update t1 set id = 2; UPDATE 80000 [local:/data/run/pg12]:5120 pg12@testdb=# update t1 set id = 3; UPDATE 80000 [local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables; schemaname | relname | n_dead_tup | last_vacuum | last_autovacuum ------------+--------------------+------------+-------------------------------+------------------------------- public | tbl | 0 | | public | t2 | 0 | | public | b | 0 | | public | a | 0 | | public | rel | 0 | | public | t_count | 0 | | public | t_big_autovacuum_1 | 0 | | public | t_autovacuum_1 | 0 | | public | t1 | 0 | 2019-12-10 14:29:31.420908+08 | 2019-12-10 14:38:02.771566+08 (9 rows) [local:/data/run/pg12]:5120 pg12@testdb=#
手工把track_counts設置為off，更新t1，查詢pg_stat_user_tables發現n_dead_tup沒有統計dead rows，導致autovacuum并沒有對t1表進行“vacuum”。
手工設置track_counts為on，但沒有觸發統計信息的更新，退出psql重新登錄，更新數據表后才會出現新的統計信息
[local:/data/run/pg12]:5120 pg12@testdb=# set track_counts=on; SET [local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables; schemaname | relname | n_dead_tup | last_vacuum | last_autovacuum ------------+--------------------+------------+-------------------------------+------------------------------- public | tbl | 0 | | public | t2 | 0 | | public | b | 0 | | public | a | 0 | | public | rel | 0 | | public | t_count | 0 | | public | t_big_autovacuum_1 | 0 | | public | t_autovacuum_1 | 0 | | public | t1 | 0 | 2019-12-10 14:29:31.420908+08 | 2019-12-10 14:38:02.771566+08 (9 rows) [local:/data/run/pg12]:5120 pg12@testdb=# \q [pg12@localhost ~]$ psql Expanded display is used automatically. psql (12.1) Type "help" for help. [local:/data/run/pg12]:5120 pg12@testdb=# update t1 set id = 100; UPDATE 80000 [local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables; schemaname | relname | n_dead_tup | last_vacuum | last_autovacuum ------------+--------------------+------------+-------------------------------+------------------------------- public | tbl | 0 | | public | t2 | 0 | | public | b | 0 | | public | a | 0 | | public | rel | 0 | | public | t_count | 0 | | public | t_big_autovacuum_1 | 0 | | public | t_autovacuum_1 | 0 | | public | t1 | 79868 | 2019-12-10 14:29:31.420908+08 | 2019-12-10 14:38:02.771566+08 (9 rows)
2.The autovacuum process is disabled on one or more tables
PG可在表級別上設置autovacuum是否生效
[local:/data/run/pg12]:5120 pg12@testdb=# create table t2(id int);
CREATE TABLE
[local:/data/run/pg12]:5120 pg12@testdb=# alter table t2 SET (autovacuum_enabled = false);
ALTER TABLE
[local:/data/run/pg12]:5120 pg12@testdb=# \d t2
Table "public.t2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT reloptions FROM pg_class WHERE relname='t2';
reloptions
----------------------------
{autovacuum_enabled=false}
(1 row)
[local:/data/run/pg12]:5120 pg12@testdb=#在t2上插入數據并更新
[local:/data/run/pg12]:5120 pg12@testdb=# insert into t2 select generate_series(1,100000); INSERT 0 100000 [local:/data/run/pg12]:5120 pg12@testdb=# update t2 set id = 1; UPDATE 100000 [local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables; schemaname | relname | n_dead_tup | last_vacuum | last_autovacuum ------------+--------------------+------------+-------------------------------+------------------------------- public | tbl | 0 | | public | b | 0 | | public | a | 0 | | public | rel | 0 | | public | t2 | 100000 | | public | t_count | 0 | | public | t_big_autovacuum_1 | 0 | | public | t_autovacuum_1 | 0 | | public | t1 | 0 | 2019-12-10 14:29:31.420908+08 | 2019-12-10 14:59:57.269249+08 (9 rows)
t2的dead rows為100000，但60s超時后，autovacuum并沒有對該表進行vacuum處理。
[local:/data/run/pg12]:5120 pg12@testdb=# \! date Tue Dec 10 15:06:54 CST 2019 [local:/data/run/pg12]:5120 pg12@testdb=# \! date Tue Dec 10 15:08:28 CST 2019 [local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables; schemaname | relname | n_dead_tup | last_vacuum | last_autovacuum ------------+--------------------+------------+-------------------------------+------------------------------- public | tbl | 0 | | public | b | 0 | | public | a | 0 | | public | rel | 0 | | public | t2 | 100000 | | public | t_count | 0 | | public | t_big_autovacuum_1 | 0 | | public | t_autovacuum_1 | 0 | | public | t1 | 0 | 2019-12-10 14:29:31.420908+08 | 2019-12-10 14:59:57.269249+08 (9 rows) [local:/data/run/pg12]:5120 pg12@testdb=#
設置數據表autovacuum_enabled為true，等待60s，這時候發現t2已被vacuum
[local:/data/run/pg12]:5120 pg12@testdb=# alter table t2 SET (autovacuum_enabled = true); ALTER TABLE [local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables; schemaname | relname | n_dead_tup | last_vacuum | last_autovacuum ------------+--------------------+------------+-------------------------------+------------------------------- public | tbl | 0 | | public | b | 0 | | public | a | 0 | | public | rel | 0 | | public | t2 | 100000 | | public | t_count | 0 | | public | t_big_autovacuum_1 | 0 | | public | t_autovacuum_1 | 0 | | public | t1 | 0 | 2019-12-10 14:29:31.420908+08 | 2019-12-10 14:59:57.269249+08 (9 rows) [local:/data/run/pg12]:5120 pg12@testdb=# \! date Tue Dec 10 15:09:05 CST 2019 [local:/data/run/pg12]:5120 pg12@testdb=# [local:/data/run/pg12]:5120 pg12@testdb=# \! date Tue Dec 10 15:10:26 CST 2019 [local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables; schemaname | relname | n_dead_tup | last_vacuum | last_autovacuum ------------+--------------------+------------+-------------------------------+------------------------------- public | tbl | 0 | | public | b | 0 | | public | a | 0 | | public | rel | 0 | | public | t2 | 0 | | 2019-12-10 15:09:57.621123+08 public | t_count | 0 | | public | t_big_autovacuum_1 | 0 | | public | t_autovacuum_1 | 0 | | public | t1 | 0 | 2019-12-10 14:29:31.420908+08 | 2019-12-10 14:59:57.269249+08 (9 rows) [local:/data/run/pg12]:5120 pg12@testdb=#
3.Autovacuuming settings aren’t keeping pace with updates
如果autovacuum已啟用，但沒有我們想象中那么頻繁的執行，這時候需要調整默認的配置選項。
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT name,setting,boot_val,pending_restart from pg_settings where category like 'Autovacuum'; name | setting | boot_val | pending_restart -------------------------------------+-----------+-----------+----------------- autovacuum | on | on | f autovacuum_analyze_scale_factor | 0.1 | 0.1 | f autovacuum_analyze_threshold | 50 | 50 | f autovacuum_freeze_max_age | 200000000 | 200000000 | f autovacuum_max_workers | 3 | 3 | f autovacuum_multixact_freeze_max_age | 400000000 | 400000000 | f autovacuum_naptime | 60 | 60 | f autovacuum_vacuum_cost_delay | 2 | 2 | f autovacuum_vacuum_cost_limit | -1 | -1 | f autovacuum_vacuum_scale_factor | 0.2 | 0.2 | f autovacuum_vacuum_threshold | 50 | 50 | f (11 rows)
查詢pg_settings，其中setting為當前配置的值，boot_val是默認值，可以看到當前庫的配置與默認值一樣。
確定autovacuum運行頻度的參數有：
1.autovacuum_vacuum_threshold，觸發閾值，默認為50
2.autovacuum_vacuum_scale_factor，觸發dead rows率，默認為0.2，即20%
3.表的估算行數，存儲在pg_class.reltuples中
PG結合上述3個參數來確定autovacuum是否需要執行，計算公式如下：
autovacuuming threshold = autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * estimated number of rows in the table)
通過調整參數，如減少autovacuum_vacuum_scale_factor可觸發VACUUMs運行得更頻繁。
PG還提供了log_autovacuum_min_duration參數來診斷autovacuum的運行間隔時間，如超過該時間設置則會記錄在日志中，這樣有助于診斷autovacuum的設定是否合理。
4.Lock conflicts
vacuum的執行需要持有SHARE UPDATE EXCLUSIVE lock，如有session持有的鎖（SHARE UPDATE EXCLUSIVE，SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE）與其沖突，則無法執行vacuum。
更新t2
[local:/data/run/pg12]:5120 pg12@testdb=# begin; BEGIN [local:/data/run/pg12]:5120 pg12@testdb=#* update t2 set id = 10; UPDATE 100000 [local:/data/run/pg12]:5120 pg12@testdb=#* commit; COMMIT [local:/data/run/pg12]:5120 pg12@testdb=# [local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables; schemaname | relname | n_dead_tup | last_vacuum | last_autovacuum ------------+--------------------+------------+-------------------------------+------------------------------- public | tbl | 0 | | public | b | 0 | | public | a | 0 | | public | rel | 0 | | public | t2 | 100000 | | 2019-12-10 15:09:57.621123+08 public | t_count | 0 | | public | t_big_autovacuum_1 | 0 | | public | t_autovacuum_1 | 0 | | public | t1 | 0 | 2019-12-10 14:29:31.420908+08 | 2019-12-10 14:59:57.269249+08 (9 rows)
開另外一個窗口，lock表
[local:/data/run/pg12]:5120 pg12@testdb=# begin; BEGIN [local:/data/run/pg12]:5120 pg12@testdb=#* lock t2 in SHARE UPDATE EXCLUSIVE mode; LOCK TABLE [local:/data/run/pg12]:5120 pg12@testdb=#*
autovacuum由于無法獲取鎖，因此無法對表進行vacuum
[local:/data/run/pg12]:5120 pg12@testdb=# select pid,locktype,relation::regclass,mode,granted from pg_locks where pid <> pg_backend_pid(); pid | locktype | relation | mode | granted -------+------------+----------+--------------------------+--------- 58050 | virtualxid | | ExclusiveLock | t 58050 | relation | t2 | ShareUpdateExclusiveLock | t (2 rows) [local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables; schemaname | relname | n_dead_tup | last_vacuum | last_autovacuum ------------+--------------------+------------+-------------------------------+------------------------------- public | tbl | 0 | | public | b | 0 | | public | a | 0 | | public | rel | 0 | | public | t2 | 100000 | | 2019-12-10 15:09:57.621123+08 public | t_count | 0 | | public | t_big_autovacuum_1 | 0 | | public | t_autovacuum_1 | 0 | | public | t1 | 0 | 2019-12-10 14:29:31.420908+08 | 2019-12-10 14:59:57.269249+08 (9 rows)
釋放鎖
[local:/data/run/pg12]:5120 pg12@testdb=#* commit; COMMIT [local:/data/run/pg12]:5120 pg12@testdb=#
autovacuum可正常執行，last_autovacuum已更新
[local:/data/run/pg12]:5120 pg12@testdb=# \! date Tue Dec 10 15:33:01 CST 2019 [local:/data/run/pg12]:5120 pg12@testdb=# \! date Tue Dec 10 15:33:40 CST 2019 [local:/data/run/pg12]:5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables; schemaname | relname | n_dead_tup | last_vacuum | last_autovacuum ------------+--------------------+------------+-------------------------------+------------------------------- public | tbl | 0 | | public | b | 0 | | public | a | 0 | | public | rel | 0 | | public | t2 | 0 | | 2019-12-10 15:32:58.743764+08 public | t_count | 0 | | public | t_big_autovacuum_1 | 0 | | public | t_autovacuum_1 | 0 | | public | t1 | 0 | 2019-12-10 14:29:31.420908+08 | 2019-12-10 14:59:57.269249+08 (9 rows) [local:/data/run/pg12]:5120 pg12@testdb=#
通過進程狀態亦可診斷
-- session 1 [local:/data/run/pg12]:5120 pg12@testdb=# begin; BEGIN [local:/data/run/pg12]:5120 pg12@testdb=#* lock t2 in SHARE UPDATE EXCLUSIVE mode; LOCK TABLE [local:/data/run/pg12]:5120 pg12@testdb=#* -- session 2 [local:/data/run/pg12]:5120 pg12@testdb=# vacuum t2; -- console [pg12@localhost ~]$ ps -ef|grep 'waiting' pg12 56540 55944 0 14:59 ? 00:00:01 postgres: pg12 testdb [local] VACUUM waiting pg12 58502 53760 0 15:36 pts/2 00:00:00 grep --color=auto waiting [pg12@localhost ~]$
進程顯示為VACUUM waiting
5.Long-running open transactions
MVCC的一個副作用是vacuum不能清理那些其他事務還需要訪問的過期dead rows。因此，如無必要確保事務正常完結。
通過視圖pg_stat_activity可監控事務的狀態
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT pid,xact_start, state, usename FROM pg_stat_activity; pid | xact_start | state | usename -------+-------------------------------+--------+--------- 55958 | | | 55960 | | | pg12 56540 | 2019-12-10 15:42:47.210597+08 | active | pg12 58050 | | idle | pg12 55956 | | | 55955 | | | 55957 | | | (7 rows)
如state列顯示為disabled，則需檢查系統參數track_activities
[local:/data/run/pg12]:5120 pg12@testdb=# show track_activities; track_activities ------------------ on (1 row) [local:/data/run/pg12]:5120 pg12@testdb=# set track_activities=off; SET [local:/data/run/pg12]:5120 pg12@testdb=# SELECT pid,xact_start, state, usename FROM pg_stat_activity; pid | xact_start | state | usename -------+------------+----------+--------- 55958 | | | 55960 | | | pg12 56540 | | disabled | pg12 58050 | | idle | pg12 55956 | | | 55955 | | | 55957 | | | (7 rows) [local:/data/run/pg12]:5120 pg12@testdb=# [local:/data/run/pg12]:5120 pg12@testdb=# set track_activities=on; SET [local:/data/run/pg12]:5120 pg12@testdb=# SELECT pid,xact_start, state, usename FROM pg_stat_activity; pid | xact_start | state | usename -------+-------------------------------+--------+--------- 55958 | | | 55960 | | | pg12 56540 | 2019-12-10 15:52:19.500017+08 | active | pg12 58050 | | idle | pg12 55956 | | | 55955 | | | 55957 | | | (7 rows)
對于長時間閑置的session，PG提供了參數idle_in_transaction_session_timeout 用于控制這些session，超過該參數配置的時間（以ms為單位），PG會自動終止這些session。
感謝各位的閱讀，以上就是“PostgreSQL中怎么監控VACUUM的處理過程”的內容了，經過本文的學習后，相信大家對PostgreSQL中怎么監控VACUUM的處理過程這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
