本节介绍了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。 **参考资料** [Monitoring PostgreSQL VACUUM processes](https://www.datadoghq.com/blog/postgresql-vacuum-monitoring/)