这篇文章主要讲解了“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来收集最近频繁更新的数据表统计信息。

这一小节介绍了配置参数中与vacuum相关的参数，包括log_autovacuum_min_duration、autovacuum_naptime等。
log_autovacuum_min_duration
如autovacuum的执行时间超过了该参数配置的时间（ms为单位）则在日志中记录。
设置为较低的阈值：10ms

[pg12@localhostpg121db]$grep'log_autovacuum'postgresql.conflog_autovacuum_min_duration=10#-1disables,0logsallactionsand

执行SQL

[local:/data/run/pg12]:5120pg12@testdb=#[local:/data/run/pg12]:5120pg12@testdb=#droptablet_autovacuum_1;DROPTABLE[local:/data/run/pg12]:5120pg12@testdb=#createtablet_autovacuum_1(idint,c1varchar(20),c2varchar(20));CREATETABLE[local:/data/run/pg12]:5120pg12@testdb=#[local:/data/run/pg12]:5120pg12@testdb=#insertintot_autovacuum_1selectx,'c1'||x,'c2'||xfromgenerate_series(1,10000)asx;INSERT010000[local:/data/run/pg12]:5120pg12@testdb=#[local:/data/run/pg12]:5120pg12@testdb=#[local:/data/run/pg12]:5120pg12@testdb=#droptablet_big_autovacuum_1;DROPTABLE[local:/data/run/pg12]:5120pg12@testdb=#createtablet_big_autovacuum_1(idint,c1varchar(20),c2varchar(20));CREATETABLE[local:/data/run/pg12]:5120pg12@testdb=#[local:/data/run/pg12]:5120pg12@testdb=#insertintot_big_autovacuum_1selectx,'c1'||x,'c2'||xfromgenerate_series(1,1000000)asx;INSERT01000000[local:/data/run/pg12]:5120pg12@testdb=#[local:/data/run/pg12]:5120pg12@testdb=#explain(analyze,verbose)select*fromt_autovacuum_1ajoint_big_autovacuum_1bona.id=b.id;QUERYPLAN---------------------------------------------------------------------------------------------------------------------------------------------------------MergeJoin(cost=63985.35..155911.33rows=6068861width=240)(actualtime=480.678..492.453rows=10000loops=1)Output:a.id,a.c1,a.c2,b.id,b.c1,b.c2MergeCond:(a.id=b.id)->Sort(cost=301.37..310.03rows=3465width=120)(actualtime=3.061..3.835rows=10000loops=1)Output:a.id,a.c1,a.c2SortKey:a.idSortMethod:quicksortMemory:1213kB->SeqScanonpublic.t_autovacuum_1a(cost=0.00..97.65rows=3465width=120)(actualtime=0.020..1.796rows=10000loops=1)Output:a.id,a.c1,a.c2->Materialize(cost=63683.99..65435.46rows=350295width=120)(actualtime=477.603..481.446rows=10001loops=1)Output:b.id,b.c1,b.c2->Sort(cost=63683.99..64559.72rows=350295width=120)(actualtime=477.598..479.173rows=10001loops=1)Output:b.id,b.c1,b.c2SortKey:b.idSortMethod:externalmergeDisk:31144kB->SeqScanonpublic.t_big_autovacuum_1b(cost=0.00..9871.95rows=350295width=120)(actualtime=0.019..176.859rows=1000000loops=1)Output:b.id,b.c1,b.c2PlanningTime:0.315msExecutionTime:498.336ms(19rows)[local:/data/run/pg12]:5120pg12@testdb=#

日志显示autovacuum的analyze操作

2019-12-1112:31:48.655CST,,,57525,,5df07134.e0b5,1,,2019-12-1112:31:48CST,4/3,3091,LOG,00000,"automaticanalyzeoftable""testdb.public.t_autovacuum_1""systemusage:CPU:user:0.09s,system:0.00s,elapsed:0.10s",,,,,,,,,""2019-12-1112:31:49.073CST,,,57525,,5df07134.e0b5,2,,2019-12-1112:31:48CST,4/5,3092,LOG,00000,"automaticanalyzeoftable""testdb.public.t_big_autovacuum_1""systemusage:CPU:user:0.32s,system:0.00s,elapsed:0.40s",,,,,,,,,""

autovacuum_naptime
该参数控制autovacuum的调度时间，如有多个数据库，则每个数据库每隔一定的时间（autovacuum_naptime/数据库个数）启动autovacuum来进行处理。
从PG的源码可见，通过函数rebuild_database_list来构建出现变化后的DatabaseList，链表中的数据库应出现在pgstats中，在autovacuum_naptime所设定的时间间隔范围内均匀分布。
比如autovacuum_naptime = 60s，有4个数据库db1->db4，那么每隔60s/4就会有启动一个autovacuum worker对相应的DB进行处理。
可能的一个处理时间序列是：db1->XX（时）:XX（分）:18（秒）,db4->XX:XX:33,db4->XX:XX:48,db4->XX:XX:03
后续如需要对db1->db4进行vacuum，那么db1->db4会在下一个18秒、33秒、48秒和03秒触发autovacuum。

测试场景：创建3个数据库，db1 -> db3，每个数据库每隔10s执行全表更新，通过日志观察autovacuum的行为。

--session1\cdb1droptablet_autovacuum_db1;createtablet_autovacuum_db1(idint,c1varchar(20),c2varchar(20));insertintot_autovacuum_db1selectx,'c1'||x,'c2'||xfromgenerate_series(1,500000)asx;do$$declarebeginforiin1..20loopupdatet_autovacuum_db1setid=i;commit;performpg_sleep(10);endloop;end$$;--session2\cdb2droptablet_autovacuum_db2;createtablet_autovacuum_db2(idint,c1varchar(20),c2varchar(20));insertintot_autovacuum_db2selectx,'c1'||x,'c2'||xfromgenerate_series(1,500000)asx;do$$declarebeginforiin1..20loopupdatet_autovacuum_db2setid=i;commit;performpg_sleep(10);endloop;end$$;--session3\cdb3droptablet_autovacuum_db3;createtablet_autovacuum_db3(idint,c1varchar(20),c2varchar(20));insertintot_autovacuum_db3selectx,'c1'||x,'c2'||xfromgenerate_series(1,500000)asx;do$$declarebeginforiin1..20loopupdatet_autovacuum_db3setid=i;commit;performpg_sleep(10);endloop;end$$;

对应的日志输出

2019-12-1115:34:05.298CST,,,1870,,5df09bec.74e,1,,2019-12-1115:34:04CST,6/37,3406,LOG,00000,"automaticanalyzeoftable""db2.public.t_autovacuum_db2""systemusage:CPU:user:0.38s,system:0.01s,elapsed:0.52s",,,,,,,,,""2019-12-1115:34:20.528CST,,,1884,,5df09bfb.75c,1,,2019-12-1115:34:19CST,6/41,3412,LOG,00000,"automaticanalyzeoftable""db1.public.t_autovacuum_db1""systemusage:CPU:user:0.51s,system:0.01s,elapsed:0.71s",,,,,,,,,""2019-12-1115:34:35.482CST,,,1897,,5df09c0a.769,1,,2019-12-1115:34:34CST,6/45,3416,LOG,00000,"automaticanalyzeoftable""db3.public.t_autovacuum_db3""systemusage:CPU:user:0.40s,system:0.01s,elapsed:0.61s",,,,,,,,,""2019-12-1115:35:05.904CST,,,1924,,5df09c28.784,1,,2019-12-1115:35:04CST,6/51,0,LOG,00000,"automaticvacuumoftable""db2.public.t_autovacuum_db2"":indexscans:0pages:0removed,25467remain,0skippedduetopins,0skippedfrozentuples:500149removed,500000remain,0aredeadbutnotyetremovable,oldestxmin:3422bufferusage:50964hits,2misses,6dirtiedavgreadrate:0.015MB/s,avgwriterate:0.044MB/ssystemusage:CPU:user:0.43s,system:0.00s,elapsed:1.07s",,,,,,,,,""2019-12-1115:35:21.297CST,,,1937,,5df09c37.791,1,,2019-12-1115:35:19CST,6/55,0,LOG,00000,"automaticvacuumoftable""db1.public.t_autovacuum_db1"":indexscans:0pages:0removed,31832remain,0skippedduetopins,0skippedfrozentuples:500270removed,500000remain,0aredeadbutnotyetremovable,oldestxmin:3427bufferusage:63695hits,2misses,6dirtiedavgreadrate:0.010MB/s,avgwriterate:0.031MB/ssystemusage:CPU:user:0.51s,system:0.04s,elapsed:1.52s",,,,,,,,,""2019-12-1115:35:36.250CST,,,1950,,5df09c46.79e,1,,2019-12-1115:35:34CST,6/59,0,LOG,00000,"automaticvacuumoftable""db3.public.t_autovacuum_db3"":indexscans:0pages:0removed,29875remain,1skippedduetopins,0skippedfrozentuples:270removed,499874remain,0aredeadbutnotyetremovable,oldestxmin:3430bufferusage:59780hits,2misses,6dirtiedavgreadrate:0.011MB/s,avgwriterate:0.034MB/ssystemusage:CPU:user:0.43s,system:0.03s,elapsed:1.38s",,,,,,,,,""2019-12-1115:38:07.146CST,,,2081,,5df09cdc.821,1,,2019-12-1115:38:04CST,6/81,0,LOG,00000,"automaticvacuumoftable""db2.public.t_autovacuum_db2"":indexscans:0pages:0removed,44551remain,0skippedduetopins,0skippedfrozentuples:500509removed,500000remain,0aredeadbutnotyetremovable,oldestxmin:3463bufferusage:89140hits,1misses,5dirtiedavgreadrate:0.003MB/s,avgwriterate:0.017MB/ssystemusage:CPU:user:0.58s,system:0.11s,elapsed:2.28s",,,,,,,,,""2019-12-1115:38:07.963CST,,,2081,,5df09cdc.821,2,,2019-12-1115:38:04CST,6/82,3463,LOG,00000,"automaticanalyzeoftable""db2.public.t_autovacuum_db2""systemusage:CPU:user:0.38s,system:0.00s,elapsed:0.81s",,,,,,,,,""2019-12-1115:38:25.836CST,,,2094,,5df09ceb.82e,1,,2019-12-1115:38:19CST,6/86,0,LOG,00000,"automaticvacuumoftable""db1.public.t_autovacuum_db1"":indexscans:0pages:0removed,38185remain,0skippedduetopins,0skippedfrozentuples:500585removed,500000remain,0aredeadbutnotyetremovable,oldestxmin:3464bufferusage:76407hits,1misses,15391dirtiedavgreadrate:0.001MB/s,avgwriterate:19.978MB/ssystemusage:CPU:user:0.66s,system:0.03s,elapsed:6.01s",,,,,,,,,""2019-12-1115:38:26.593CST,,,2094,,5df09ceb.82e,2,,2019-12-1115:38:19CST,6/87,3464,LOG,00000,"automaticanalyzeoftable""db1.public.t_autovacuum_db1""systemusage:CPU:user:0.35s,system:0.00s,elapsed:0.75s",,,,,,,,,""2019-12-1115:38:36.546CST,,,2107,,5df09cfa.83b,1,,2019-12-1115:38:34CST,6/91,0,LOG,00000,"automaticvacuumoftable""db3.public.t_autovacuum_db3"":indexscans:0pages:0removed,41365remain,0skippedduetopins,0skippedfrozentuples:500758removed,500000remain,0aredeadbutnotyetremovable,oldestxmin:3465bufferusage:82768hits,1misses,2dirtiedavgreadrate:0.005MB/s,avgwriterate:0.010MB/ssystemusage:CPU:user:0.58s,system:0.03s,elapsed:1.63s",,,,,,,,,""2019-12-1115:38:37.276CST,,,2107,,5df09cfa.83b,2,,2019-12-1115:38:34CST,6/92,3465,LOG,00000,"automaticanalyzeoftable""db3.public.t_autovacuum_db3""systemusage:CPU:user:0.37s,system:0.00s,elapsed:0.72s",,,,,,,,,""

从日志可见，PG认为目前有4个数据库（60s/4）需要处理，每个15s调度一个数据库。

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