本节介绍了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来收集最近频繁更新的数据表统计信息。 ### autovacuum日志信息 这一小节介绍了配置参数中与vacuum相关的参数，包括log_autovacuum_min_duration、autovacuum_naptime等。 **log_autovacuum_min_duration** 如autovacuum的执行时间超过了该参数配置的时间（ms为单位）则在日志中记录。 设置为较低的阈值：10ms ``` [pg12@localhost pg121db]$ grep 'log_autovacuum' postgresql.conf log_autovacuum_min_duration = 10 # -1 disables, 0 logs all actions and ``` 执行SQL ``` [local:/data/run/pg12]:5120 pg12@testdb=# [local:/data/run/pg12]:5120 pg12@testdb=# drop table t_autovacuum_1; DROP TABLE [local:/data/run/pg12]:5120 pg12@testdb=# create table t_autovacuum_1(id int,c1 varchar(20),c2 varchar(20)); CREATE TABLE [local:/data/run/pg12]:5120 pg12@testdb=# [local:/data/run/pg12]:5120 pg12@testdb=# insert into t_autovacuum_1 select x,'c1'||x,'c2'||x from generate_series(1,10000) as x; INSERT 0 10000 [local:/data/run/pg12]:5120 pg12@testdb=# [local:/data/run/pg12]:5120 pg12@testdb=# [local:/data/run/pg12]:5120 pg12@testdb=# drop table t_big_autovacuum_1; DROP TABLE [local:/data/run/pg12]:5120 pg12@testdb=# create table t_big_autovacuum_1(id int,c1 varchar(20),c2 varchar(20)); CREATE TABLE [local:/data/run/pg12]:5120 pg12@testdb=# [local:/data/run/pg12]:5120 pg12@testdb=# insert into t_big_autovacuum_1 select x,'c1'||x,'c2'||x from generate_series(1,1000000) as x; INSERT 0 1000000 [local:/data/run/pg12]:5120 pg12@testdb=# [local:/data/run/pg12]:5120 pg12@testdb=# explain (analyze,verbose) select * from t_autovacuum_1 a join t_big_autovacuum_1 b on a.id = b.id; QUERY PLAN ------------------------------------------------------------------------------------------------------------------ --------------------------------------- Merge Join (cost=63985.35..155911.33 rows=6068861 width=240) (actual time=480.678..492.453 rows=10000 loops=1) Output: a.id, a.c1, a.c2, b.id, b.c1, b.c2 Merge Cond: (a.id = b.id) -> Sort (cost=301.37..310.03 rows=3465 width=120) (actual time=3.061..3.835 rows=10000 loops=1) Output: a.id, a.c1, a.c2 Sort Key: a.id Sort Method: quicksort Memory: 1213kB -> Seq Scan on public.t_autovacuum_1 a (cost=0.00..97.65 rows=3465 width=120) (actual time=0.020..1.796 rows=10000 loops=1) Output: a.id, a.c1, a.c2 -> Materialize (cost=63683.99..65435.46 rows=350295 width=120) (actual time=477.603..481.446 rows=10001 loops =1) Output: b.id, b.c1, b.c2 -> Sort (cost=63683.99..64559.72 rows=350295 width=120) (actual time=477.598..479.173 rows=10001 loops= 1) Output: b.id, b.c1, b.c2 Sort Key: b.id Sort Method: external merge Disk: 31144kB -> Seq Scan on public.t_big_autovacuum_1 b (cost=0.00..9871.95 rows=350295 width=120) (actual tim e=0.019..176.859 rows=1000000 loops=1) Output: b.id, b.c1, b.c2 Planning Time: 0.315 ms Execution Time: 498.336 ms (19 rows) [local:/data/run/pg12]:5120 pg12@testdb=# ``` 日志显示autovacuum的analyze操作 ``` 2019-12-11 12:31:48.655 CST,,,57525,,5df07134.e0b5,1,,2019-12-11 12:31:48 CST,4/3,3091,LOG,00000,"automatic analyze of table ""testdb.public.t_autovacuum_1"" system usage: CPU: user: 0.09 s, system: 0.00 s, elapsed: 0.10 s",,,,,,,,,"" 2019-12-11 12:31:49.073 CST,,,57525,,5df07134.e0b5,2,,2019-12-11 12:31:48 CST,4/5,3092,LOG,00000,"automatic analyze of table ""testdb.public.t_big_autovacuum_1"" system usage: CPU: user: 0.32 s, system: 0.00 s, elapsed: 0.40 s",,,,,,,,,"" ``` **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的行为。 ``` -- session 1 \c db1 drop table t_autovacuum_db1; create table t_autovacuum_db1(id int,c1 varchar(20),c2 varchar(20)); insert into t_autovacuum_db1 select x,'c1'||x,'c2'||x from generate_series(1,500000) as x; do $$ declare begin for i in 1..20 loop update t_autovacuum_db1 set id = i; commit; perform pg_sleep(10); end loop; end $$; -- session 2 \c db2 drop table t_autovacuum_db2; create table t_autovacuum_db2(id int,c1 varchar(20),c2 varchar(20)); insert into t_autovacuum_db2 select x,'c1'||x,'c2'||x from generate_series(1,500000) as x; do $$ declare begin for i in 1..20 loop update t_autovacuum_db2 set id = i; commit; perform pg_sleep(10); end loop; end $$; -- session 3 \c db3 drop table t_autovacuum_db3; create table t_autovacuum_db3(id int,c1 varchar(20),c2 varchar(20)); insert into t_autovacuum_db3 select x,'c1'||x,'c2'||x from generate_series(1,500000) as x; do $$ declare begin for i in 1..20 loop update t_autovacuum_db3 set id = i; commit; perform pg_sleep(10); end loop; end $$; ``` 对应的日志输出 ``` 2019-12-11 15:34:05.298 CST,,,1870,,5df09bec.74e,1,,2019-12-11 15:34:04 CST,6/37,3406,LOG,00000,"automatic analyze of table ""db2.public.t_autovacuum_db2"" system usage: CPU: user: 0.38 s, system: 0.01 s, elapsed: 0.52 s",,,,,,,,,"" 2019-12-11 15:34:20.528 CST,,,1884,,5df09bfb.75c,1,,2019-12-11 15:34:19 CST,6/41,3412,LOG,00000,"automatic analyze of table ""db1.public.t_autovacuum_db1"" system usage: CPU: user: 0.51 s, system: 0.01 s, elapsed: 0.71 s",,,,,,,,,"" 2019-12-11 15:34:35.482 CST,,,1897,,5df09c0a.769,1,,2019-12-11 15:34:34 CST,6/45,3416,LOG,00000,"automatic analyze of table ""db3.public.t_autovacuum_db3"" system usage: CPU: user: 0.40 s, system: 0.01 s, elapsed: 0.61 s",,,,,,,,,"" 2019-12-11 15:35:05.904 CST,,,1924,,5df09c28.784,1,,2019-12-11 15:35:04 CST,6/51,0,LOG,00000,"automatic vacuum of table ""db2.public.t_autovacuum_db2"": index scans: 0 pages: 0 removed, 25467 remain, 0 skipped due to pins, 0 skipped frozen tuples: 500149 removed, 500000 remain, 0 are dead but not yet removable, oldest xmin: 3422 buffer usage: 50964 hits, 2 misses, 6 dirtied avg read rate: 0.015 MB/s, avg write rate: 0.044 MB/s system usage: CPU: user: 0.43 s, system: 0.00 s, elapsed: 1.07 s",,,,,,,,,"" 2019-12-11 15:35:21.297 CST,,,1937,,5df09c37.791,1,,2019-12-11 15:35:19 CST,6/55,0,LOG,00000,"automatic vacuum of table ""db1.public.t_autovacuum_db1"": index scans: 0 pages: 0 removed, 31832 remain, 0 skipped due to pins, 0 skipped frozen tuples: 500270 removed, 500000 remain, 0 are dead but not yet removable, oldest xmin: 3427 buffer usage: 63695 hits, 2 misses, 6 dirtied avg read rate: 0.010 MB/s, avg write rate: 0.031 MB/s system usage: CPU: user: 0.51 s, system: 0.04 s, elapsed: 1.52 s",,,,,,,,,"" 2019-12-11 15:35:36.250 CST,,,1950,,5df09c46.79e,1,,2019-12-11 15:35:34 CST,6/59,0,LOG,00000,"automatic vacuum of table ""db3.public.t_autovacuum_db3"": index scans: 0 pages: 0 removed, 29875 remain, 1 skipped due to pins, 0 skipped frozen tuples: 270 removed, 499874 remain, 0 are dead but not yet removable, oldest xmin: 3430 buffer usage: 59780 hits, 2 misses, 6 dirtied avg read rate: 0.011 MB/s, avg write rate: 0.034 MB/s system usage: CPU: user: 0.43 s, system: 0.03 s, elapsed: 1.38 s",,,,,,,,,"" 2019-12-11 15:38:07.146 CST,,,2081,,5df09cdc.821,1,,2019-12-11 15:38:04 CST,6/81,0,LOG,00000,"automatic vacuum of table ""db2.public.t_autovacuum_db2"": index scans: 0 pages: 0 removed, 44551 remain, 0 skipped due to pins, 0 skipped frozen tuples: 500509 removed, 500000 remain, 0 are dead but not yet removable, oldest xmin: 3463 buffer usage: 89140 hits, 1 misses, 5 dirtied avg read rate: 0.003 MB/s, avg write rate: 0.017 MB/s system usage: CPU: user: 0.58 s, system: 0.11 s, elapsed: 2.28 s",,,,,,,,,"" 2019-12-11 15:38:07.963 CST,,,2081,,5df09cdc.821,2,,2019-12-11 15:38:04 CST,6/82,3463,LOG,00000,"automatic analyze of table ""db2.public.t_autovacuum_db2"" system usage: CPU: user: 0.38 s, system: 0.00 s, elapsed: 0.81 s",,,,,,,,,"" 2019-12-11 15:38:25.836 CST,,,2094,,5df09ceb.82e,1,,2019-12-11 15:38:19 CST,6/86,0,LOG,00000,"automatic vacuum of table ""db1.public.t_autovacuum_db1"": index scans: 0 pages: 0 removed, 38185 remain, 0 skipped due to pins, 0 skipped frozen tuples: 500585 removed, 500000 remain, 0 are dead but not yet removable, oldest xmin: 3464 buffer usage: 76407 hits, 1 misses, 15391 dirtied avg read rate: 0.001 MB/s, avg write rate: 19.978 MB/s system usage: CPU: user: 0.66 s, system: 0.03 s, elapsed: 6.01 s",,,,,,,,,"" 2019-12-11 15:38:26.593 CST,,,2094,,5df09ceb.82e,2,,2019-12-11 15:38:19 CST,6/87,3464,LOG,00000,"automatic analyze of table ""db1.public.t_autovacuum_db1"" system usage: CPU: user: 0.35 s, system: 0.00 s, elapsed: 0.75 s",,,,,,,,,"" 2019-12-11 15:38:36.546 CST,,,2107,,5df09cfa.83b,1,,2019-12-11 15:38:34 CST,6/91,0,LOG,00000,"automatic vacuum of table ""db3.public.t_autovacuum_db3"": index scans: 0 pages: 0 removed, 41365 remain, 0 skipped due to pins, 0 skipped frozen tuples: 500758 removed, 500000 remain, 0 are dead but not yet removable, oldest xmin: 3465 buffer usage: 82768 hits, 1 misses, 2 dirtied avg read rate: 0.005 MB/s, avg write rate: 0.010 MB/s system usage: CPU: user: 0.58 s, system: 0.03 s, elapsed: 1.63 s",,,,,,,,,"" 2019-12-11 15:38:37.276 CST,,,2107,,5df09cfa.83b,2,,2019-12-11 15:38:34 CST,6/92,3465,LOG,00000,"automatic analyze of table ""db3.public.t_autovacuum_db3"" system usage: CPU: user: 0.37 s, system: 0.00 s, elapsed: 0.72 s",,,,,,,,,"" ``` 从日志可见，PG认为目前有4个数据库（60s/4）需要处理，每个15s调度一个数据库。 ### 参考资料 [Monitoring PostgreSQL VACUUM processes](https://www.datadoghq.com/blog/postgresql-vacuum-monitoring/) [Postgres Autovacuum is Not the Enemy](https://www.citusdata.com/blog/2016/11/04/autovacuum-not-the-enemy/) [Tuning Autovacuum in PostgreSQL and Autovacuum Internals](https://www.percona.com/blog/2018/08/10/tuning-autovacuum-in-postgresql-and-autovacuum-internals/)