这篇文章主要讲解了“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]:5120pg12@testdb=#\dpg_stat_user_tablesView"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|timestampwithtimezone|||last_autovacuum|timestampwithtimezone|||last_analyze|timestampwithtimezone|||last_autoanalyze|timestampwithtimezone|||vacuum_count|bigint|||autovacuum_count|bigint|||analyze_count|bigint|||autoanalyze_count|bigint|||[local:/data/run/pg12]:5120pg12@testdb=#updatet1setid=2;UPDATE20000[local:/data/run/pg12]:5120pg12@testdb=#SELECTschemaname,relname,n_dead_tupFROMpg_stat_user_tables;schemaname|relname|n_dead_tup------------+--------------------+------------public|tbl|0public|t2|0public|b|0public|a|0public|rel|0public|t_count|0public|t_big_autovacuum_1|0public|t_autovacuum_1|0public|t1|20000(9rows)

监控每张表的dead rows，特别是监控频繁更新的表上，这样有助于DBA确定VACUUM进程是否已有效的周期性的清除这些dead rows。

Table disk usage
在出现dead rows时，磁盘空间会逐步增大，vacuuming执行后可标记dead rows为空闲空间，通过监控空间的变化

[local:/data/run/pg12]:5120pg12@testdb=#SELECTrelnameAS"table_name",pg_size_pretty(pg_table_size(C.oid))AS"table_size"FROMpg_classCLEFTJOINpg_namespaceNON(N.oid=C.relnamespace)WHEREnspnameNOTIN('pg_catalog','information_schema')ANDnspname!~'^pg_toast'ANDrelkindIN('r')ORDERBYpg_table_size(C.oid)DESC;table_name|table_size--------------------+------------rel|845MBt_big_autovacuum_1|498MBtbl|100MBa|65MBb|65MBt1|1456kBt_autovacuum_1|504kBt2|360kBt_count|64kB(9rows)[local:/data/run/pg12]:5120pg12@testdb=#updatet1setid=4;UPDATE20000[local:/data/run/pg12]:5120pg12@testdb=#updatet1setid=5;UPDATE20000[local:/data/run/pg12]:5120pg12@testdb=#SELECTrelnameAS"table_name",pg_size_pretty(pg_table_size(C.oid))AS"table_size"FROMpg_classCLEFTJOINpg_namespaceNON(N.oid=C.relnamespace)WHEREnspnameNOTIN('pg_catalog','information_schema')ANDnspname!~'^pg_toast'ANDrelkindIN('r')ORDERBYpg_table_size(C.oid)DESC;table_name|table_size--------------------+------------rel|845MBt_big_autovacuum_1|498MBtbl|100MBa|65MBb|65MBt1|2864kBt_autovacuum_1|504kBt2|360kBt_count|64kB(9rows)

对t1执行全量更新，然后执行vacuum t1后再次插入等量的数据

[local:/data/run/pg12]:5120pg12@testdb=#vacuumt1;VACUUM[local:/data/run/pg12]:5120pg12@testdb=#selectcount(*)fromt1;count-------20000(1row)[local:/data/run/pg12]:5120pg12@testdb=#\dt1Table"public.t1"Column|Type|Collation|Nullable|Default--------+---------+-----------+----------+---------id|integer|||[local:/data/run/pg12]:5120pg12@testdb=#insertintot1selectgenerate_series(1,20000);INSERT020000[local:/data/run/pg12]:5120pg12@testdb=#SELECTrelnameAS"table_name",pg_size_pretty(pg_table_size(C.oid))AS"table_size"FROMpg_classCLEFTJOINpg_namespaceNON(N.oid=C.relnamespace)WHEREnspnameNOTIN('pg_catalog','information_schema')ANDnspname!~'^pg_toast'ANDrelkindIN('r')ORDERBYpg_table_size(C.oid)DESC;table_name|table_size--------------------+------------rel|845MBt_big_autovacuum_1|498MBtbl|100MBa|65MBb|65MBt1|2864kBt_autovacuum_1|504kBt2|360kBt_count|64kB(9rows)

可以看到table占用的空间并没有出现变化，原因是新的rows使用了dead rows的空间。
如不执行vacuum直接插入，则明显可以看到table size的变化。

[local:/data/run/pg12]:5120pg12@testdb=#updatet1setid=10;UPDATE60000[local:/data/run/pg12]:5120pg12@testdb=#SELECTrelnameAS"table_name",pg_size_pretty(pg_table_size(C.oid))AS"table_size"FROMpg_classCLEFTJOINpg_namespaceNON(N.oid=C.relnamespace)WHEREnspnameNOTIN('pg_catalog','information_schema')ANDnspname!~'^pg_toast'ANDrelkindIN('r')ORDERBYpg_table_size(C.oid)DESC;table_name|table_size--------------------+------------rel|845MBt_big_autovacuum_1|498MBtbl|100MBa|65MBb|65MBt1|4288kB-->这是原占用空间t_autovacuum_1|504kBt2|360kBt_count|64kB(9rows)[local:/data/run/pg12]:5120pg12@testdb=#insertintot1selectgenerate_series(1,20000);INSERT020000[local:/data/run/pg12]:5120pg12@testdb=#SELECTrelnameAS"table_name",pg_size_pretty(pg_table_size(C.oid))AS"table_size"FROMpg_classCLEFTJOINpg_namespaceNON(N.oid=C.relnamespace)WHEREnspnameNOTIN('pg_catalog','information_schema')ANDnspname!~'^pg_toast'ANDrelkindIN('r')ORDERBYpg_table_size(C.oid)DESC;table_name|table_size--------------------+------------rel|845MBt_big_autovacuum_1|498MBtbl|100MBa|65MBb|65MBt1|4992kB-->新增占用空间t_autovacuum_1|504kBt2|360kBt_count|64kB(9rows)

Last time (auto)vacuum ran
PG提供了pg_stat_user_tables 视图用于监控最近一次vacuum运行的时间。

[local:/data/run/pg12]:5120pg12@testdb=#\dpg_stat_user_tablesView"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|timestampwithtimezone|||last_autovacuum|timestampwithtimezone|||last_analyze|timestampwithtimezone|||last_autoanalyze|timestampwithtimezone|||vacuum_count|bigint|||autovacuum_count|bigint|||analyze_count|bigint|||autoanalyze_count|bigint|||[local:/data/run/pg12]:5120pg12@testdb=#SELECTschemaname,relname,last_vacuum,last_autovacuumFROMpg_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-1014:29:31.420908+08|2019-12-1014:32:02.639873+08(9rows)

可以看到最近一次的vacumm是14:29:31，而最近一次的autovacuum是14:32:02，autovacuum默认60s执行一次，其他没有变化的表PG不会执行autovacuum。

[local:/data/run/pg12]:5120pg12@testdb=#selectname,settingfrompg_settingswherenamelike'%autovacuum%';name|setting-------------------------------------+-----------autovacuum|onautovacuum_analyze_scale_factor|0.1autovacuum_analyze_threshold|50autovacuum_freeze_max_age|200000000autovacuum_max_workers|3autovacuum_multixact_freeze_max_age|400000000autovacuum_naptime|60-->60sautovacuum_vacuum_cost_delay|2autovacuum_vacuum_cost_limit|-1autovacuum_vacuum_scale_factor|0.2autovacuum_vacuum_threshold|50autovacuum_work_mem|-1log_autovacuum_min_duration|-1(13rows)

执行update操作，60s后再次查询，发现last_autovacuum已更新。

[local:/data/run/pg12]:5120pg12@testdb=#updatet1setid=1;UPDATE80000[local:/data/run/pg12]:5120pg12@testdb=#SELECTschemaname,relname,last_vacuum,last_autovacuumFROMpg_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-1014:29:31.420908+08|2019-12-1014:38:02.771566+08(9rows)

监控vacuum full
通过视图pg_stat_progress_vacuum可监控vacuum full的进度

[local:/data/run/pg12]:5120pg12@testdb=#\dpg_stat_progress_vacuumView"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]:5120pg12@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|grepautovacuum55958?Ss0:00postgres:autovacuumlauncher56057pts/4S+0:00grep--color=autoautovacuum[root@localhost~]#

同时亦可通过查询pg_settings获得

[local:/data/run/pg12]:5120pg12@testdb=#SELECTname,settingFROMpg_settingsWHEREname='autovacuum';name|setting------------+---------autovacuum|on(1row)

如autovacuum已开启，但结果没有如我们预期，那么问题可能出现在statistics collector上面，autovacuum依赖statistics collector用于确定何时以及间隔多少时间应该运行。通常来说，statistics collector应启用，但如果禁用此项，对autovacuum的正常运行会有较大影响。通过检查track_counts配置项来检查statistics collector是否启用。

[local:/data/run/pg12]:5120pg12@testdb=#SELECTname,settingFROMpg_settingsWHEREname='track_counts';name|setting--------------+---------track_counts|on(1row)[local:/data/run/pg12]:5120pg12@testdb=#

如track_counts为OFF，则statistics collector不会更新dead rows信息，而该项是autovacuum所依赖的信息。

[local:/data/run/pg12]:5120pg12@testdb=#settrack_counts=off;SET[local:/data/run/pg12]:5120pg12@testdb=#updatet1setid=2;UPDATE80000[local:/data/run/pg12]:5120pg12@testdb=#updatet1setid=3;UPDATE80000[local:/data/run/pg12]:5120pg12@testdb=#SELECTschemaname,relname,n_dead_tup,last_vacuum,last_autovacuumFROMpg_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-1014:29:31.420908+08|2019-12-1014:38:02.771566+08(9rows)[local:/data/run/pg12]:5120pg12@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]:5120pg12@testdb=#settrack_counts=on;SET[local:/data/run/pg12]:5120pg12@testdb=#SELECTschemaname,relname,n_dead_tup,last_vacuum,last_autovacuumFROMpg_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-1014:29:31.420908+08|2019-12-1014:38:02.771566+08(9rows)[local:/data/run/pg12]:5120pg12@testdb=#\q[pg12@localhost~]$psqlExpandeddisplayisusedautomatically.psql(12.1)Type"help"forhelp.[local:/data/run/pg12]:5120pg12@testdb=#updatet1setid=100;UPDATE80000[local:/data/run/pg12]:5120pg12@testdb=#SELECTschemaname,relname,n_dead_tup,last_vacuum,last_autovacuumFROMpg_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-1014:29:31.420908+08|2019-12-1014:38:02.771566+08(9rows)

2.The autovacuum process is disabled on one or more tables
PG可在表级别上设置autovacuum是否生效

[local:/data/run/pg12]:5120pg12@testdb=#createtablet2(idint);CREATETABLE[local:/data/run/pg12]:5120pg12@testdb=#altertablet2SET(autovacuum_enabled=false);ALTERTABLE[local:/data/run/pg12]:5120pg12@testdb=#\dt2Table"public.t2"Column|Type|Collation|Nullable|Default--------+---------+-----------+----------+---------id|integer|||[local:/data/run/pg12]:5120pg12@testdb=#SELECTreloptionsFROMpg_classWHERErelname='t2';reloptions----------------------------{autovacuum_enabled=false}(1row)[local:/data/run/pg12]:5120pg12@testdb=#

在t2上插入数据并更新

[local:/data/run/pg12]:5120pg12@testdb=#insertintot2selectgenerate_series(1,100000);INSERT0100000[local:/data/run/pg12]:5120pg12@testdb=#updatet2setid=1;UPDATE100000[local:/data/run/pg12]:5120pg12@testdb=#SELECTschemaname,relname,n_dead_tup,last_vacuum,last_autovacuumFROMpg_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-1014:29:31.420908+08|2019-12-1014:59:57.269249+08(9rows)

t2的dead rows为100000，但60s超时后，autovacuum并没有对该表进行vacuum处理。

[local:/data/run/pg12]:5120pg12@testdb=#\!dateTueDec1015:06:54CST2019[local:/data/run/pg12]:5120pg12@testdb=#\!dateTueDec1015:08:28CST2019[local:/data/run/pg12]:5120pg12@testdb=#SELECTschemaname,relname,n_dead_tup,last_vacuum,last_autovacuumFROMpg_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-1014:29:31.420908+08|2019-12-1014:59:57.269249+08(9rows)[local:/data/run/pg12]:5120pg12@testdb=#

设置数据表autovacuum_enabled为true，等待60s，这时候发现t2已被vacuum

[local:/data/run/pg12]:5120pg12@testdb=#altertablet2SET(autovacuum_enabled=true);ALTERTABLE[local:/data/run/pg12]:5120pg12@testdb=#SELECTschemaname,relname,n_dead_tup,last_vacuum,last_autovacuumFROMpg_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-1014:29:31.420908+08|2019-12-1014:59:57.269249+08(9rows)[local:/data/run/pg12]:5120pg12@testdb=#\!dateTueDec1015:09:05CST2019[local:/data/run/pg12]:5120pg12@testdb=#[local:/data/run/pg12]:5120pg12@testdb=#\!dateTueDec1015:10:26CST2019[local:/data/run/pg12]:5120pg12@testdb=#SELECTschemaname,relname,n_dead_tup,last_vacuum,last_autovacuumFROMpg_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-1015:09:57.621123+08public|t_count|0||public|t_big_autovacuum_1|0||public|t_autovacuum_1|0||public|t1|0|2019-12-1014:29:31.420908+08|2019-12-1014:59:57.269249+08(9rows)[local:/data/run/pg12]:5120pg12@testdb=#

3.Autovacuuming settings aren’t keeping pace with updates
如果autovacuum已启用，但没有我们想象中那么频繁的执行，这时候需要调整默认的配置选项。

[local:/data/run/pg12]:5120pg12@testdb=#SELECTname,setting,boot_val,pending_restartfrompg_settingswherecategorylike'Autovacuum';name|setting|boot_val|pending_restart-------------------------------------+-----------+-----------+-----------------autovacuum|on|on|fautovacuum_analyze_scale_factor|0.1|0.1|fautovacuum_analyze_threshold|50|50|fautovacuum_freeze_max_age|200000000|200000000|fautovacuum_max_workers|3|3|fautovacuum_multixact_freeze_max_age|400000000|400000000|fautovacuum_naptime|60|60|fautovacuum_vacuum_cost_delay|2|2|fautovacuum_vacuum_cost_limit|-1|-1|fautovacuum_vacuum_scale_factor|0.2|0.2|fautovacuum_vacuum_threshold|50|50|f(11rows)

查询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是否需要执行，计算公式如下：

autovacuumingthreshold=autovacuum_vacuum_threshold+(autovacuum_vacuum_scale_factor*estimatednumberofrowsinthetable)

通过调整参数，如减少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]:5120pg12@testdb=#begin;BEGIN[local:/data/run/pg12]:5120pg12@testdb=#*updatet2setid=10;UPDATE100000[local:/data/run/pg12]:5120pg12@testdb=#*commit;COMMIT[local:/data/run/pg12]:5120pg12@testdb=#[local:/data/run/pg12]:5120pg12@testdb=#SELECTschemaname,relname,n_dead_tup,last_vacuum,last_autovacuumFROMpg_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-1015:09:57.621123+08public|t_count|0||public|t_big_autovacuum_1|0||public|t_autovacuum_1|0||public|t1|0|2019-12-1014:29:31.420908+08|2019-12-1014:59:57.269249+08(9rows)

开另外一个窗口，lock表

[local:/data/run/pg12]:5120pg12@testdb=#begin;BEGIN[local:/data/run/pg12]:5120pg12@testdb=#*lockt2inSHAREUPDATEEXCLUSIVEmode;LOCKTABLE[local:/data/run/pg12]:5120pg12@testdb=#*

autovacuum由于无法获取锁，因此无法对表进行vacuum

[local:/data/run/pg12]:5120pg12@testdb=#selectpid,locktype,relation::regclass,mode,grantedfrompg_lockswherepid<>pg_backend_pid();pid|locktype|relation|mode|granted-------+------------+----------+--------------------------+---------58050|virtualxid||ExclusiveLock|t58050|relation|t2|ShareUpdateExclusiveLock|t(2rows)[local:/data/run/pg12]:5120pg12@testdb=#SELECTschemaname,relname,n_dead_tup,last_vacuum,last_autovacuumFROMpg_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-1015:09:57.621123+08public|t_count|0||public|t_big_autovacuum_1|0||public|t_autovacuum_1|0||public|t1|0|2019-12-1014:29:31.420908+08|2019-12-1014:59:57.269249+08(9rows)

释放锁

[local:/data/run/pg12]:5120pg12@testdb=#*commit;COMMIT[local:/data/run/pg12]:5120pg12@testdb=#

autovacuum可正常执行，last_autovacuum已更新

[local:/data/run/pg12]:5120pg12@testdb=#\!dateTueDec1015:33:01CST2019[local:/data/run/pg12]:5120pg12@testdb=#\!dateTueDec1015:33:40CST2019[local:/data/run/pg12]:5120pg12@testdb=#SELECTschemaname,relname,n_dead_tup,last_vacuum,last_autovacuumFROMpg_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-1015:32:58.743764+08public|t_count|0||public|t_big_autovacuum_1|0||public|t_autovacuum_1|0||public|t1|0|2019-12-1014:29:31.420908+08|2019-12-1014:59:57.269249+08(9rows)[local:/data/run/pg12]:5120pg12@testdb=#

通过进程状态亦可诊断

--session1[local:/data/run/pg12]:5120pg12@testdb=#begin;BEGIN[local:/data/run/pg12]:5120pg12@testdb=#*lockt2inSHAREUPDATEEXCLUSIVEmode;LOCKTABLE[local:/data/run/pg12]:5120pg12@testdb=#*--session2[local:/data/run/pg12]:5120pg12@testdb=#vacuumt2;--console[pg12@localhost~]$ps-ef|grep'waiting'pg125654055944014:59?00:00:01postgres:pg12testdb[local]VACUUMwaitingpg125850253760015:36pts/200:00:00grep--color=autowaiting[pg12@localhost~]$

进程显示为VACUUM waiting

5.Long-running open transactions
MVCC的一个副作用是vacuum不能清理那些其他事务还需要访问的过期dead rows。因此，如无必要确保事务正常完结。
通过视图pg_stat_activity可监控事务的状态

[local:/data/run/pg12]:5120pg12@testdb=#SELECTpid,xact_start,state,usenameFROMpg_stat_activity;pid|xact_start|state|usename-------+-------------------------------+--------+---------55958|||55960|||pg1256540|2019-12-1015:42:47.210597+08|active|pg1258050||idle|pg1255956|||55955|||55957|||(7rows)

如state列显示为disabled，则需检查系统参数track_activities

[local:/data/run/pg12]:5120pg12@testdb=#showtrack_activities;track_activities------------------on(1row)[local:/data/run/pg12]:5120pg12@testdb=#settrack_activities=off;SET[local:/data/run/pg12]:5120pg12@testdb=#SELECTpid,xact_start,state,usenameFROMpg_stat_activity;pid|xact_start|state|usename-------+------------+----------+---------55958|||55960|||pg1256540||disabled|pg1258050||idle|pg1255956|||55955|||55957|||(7rows)[local:/data/run/pg12]:5120pg12@testdb=#[local:/data/run/pg12]:5120pg12@testdb=#settrack_activities=on;SET[local:/data/run/pg12]:5120pg12@testdb=#SELECTpid,xact_start,state,usenameFROMpg_stat_activity;pid|xact_start|state|usename-------+-------------------------------+--------+---------55958|||55960|||pg1256540|2019-12-1015:52:19.500017+08|active|pg1258050||idle|pg1255956|||55955|||55957|||(7rows)

对于长时间闲置的session，PG提供了参数idle_in_transaction_session_timeout 用于控制这些session，超过该参数配置的时间（以ms为单位），PG会自动终止这些session。

感谢各位的阅读，以上就是“PostgreSQL中怎么监控VACUUM的处理过程”的内容了，经过本文的学习后，相信大家对PostgreSQL中怎么监控VACUUM的处理过程这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！