监控采集pg的autovacuum情况
原创
生产上，谁也不希望见到这种情况：
业务正处于高峰期，postgresql 反应很慢，监控显示磁盘IO很高，结果登陆机器发现居然是有大量的vaccum在吃磁盘IO。
对于这种情况，有必要提前监控预防。
思路
1 元数据采样SQL （注意，这个查询需要连接到PG主库执行，需要先切换到每一个需要采集的库里）
\c test
SELECT
current_database() as dbname,
pg_stat_all_tables.schemaname,
pg_stat_all_tables.relname,
-- pg_stat_all_tables.last_autovacuum,
TO_CHAR(pg_stat_all_tables.last_autovacuum, 'YYYY-MM-DD HH24:MI:SS') as last_autovacuum,
pg_total_relation_size(pg_class.oid) AS table_size
FROM
pg_stat_all_tables
JOIN pg_class ON pg_stat_all_tables.relid = pg_class.oid
WHERE 1=1
AND pg_stat_all_tables.last_autovacuum is not null
AND NOT pg_class.relname LIKE 'pg\_%'
AND NOT pg_class.relname LIKE'sql\_%'
AND DATE(pg_stat_all_tables.last_autovacuum) >= DATE(NOW() - INTERVAL '3 day') -- 只关注最近3天发生过autovacuum的表
;
结果类似
dbname | schemaname | relname | last_autovacuum | table_size
--------+------------+------------------+---------------------+------------
test | public | pgbench_accounts | 2025-05-09 14:00:06 | 138862592
2 创建MySQL表,用于存储上述巡检数据
drop table db_pg_autovacuum_monitoring;
CREATE TABLE db_pg_autovacuum_monitoring (
id int unsigned not null auto_increment PRIMARY KEY,
instance_cluster varchar(64) NOT NULL,
dbname varchar(64) NOT NULL,
schemaname varchar(64) NOT NULL,
relname varchar(64) NOT NULL,
last_autovacuum datetime,
table_size BIGINT,
capture_time datetime not null DEFAULT CURRENT_TIMESTAMP
) comment 'pg autovacuum 监控';
-- 加个唯一索引和时间列索引
alter table db_pg_autovacuum_monitoring add unique key uniq_union(instance_cluster ,dbname,schemaname,relname,last_autovacuum );
alter table db_pg_autovacuum_monitoring add index idx_capture_time(capture_time);
3 插入测试数据
insert into db_pg_autovacuum_monitoring (instance_cluster,dbname,schemaname,relname,last_autovacuum,table_size)
values('test-pg','test','public','pgbench_accounts','2025-05-09 14:00:06',138862592);
insert into db_pg_autovacuum_monitoring (instance_cluster,dbname,schemaname,relname,last_autovacuum,table_size)
values('test-pg','test','public','pgbench_accounts','2025-05-09 14:10:06',138862592);
insert into db_pg_autovacuum_monitoring (instance_cluster,dbname,schemaname,relname,last_autovacuum,table_size)
values('test-pg','test','public','pgbench_accounts','2025-05-09 14:10:06',138862592);
insert into db_pg_autovacuum_monitoring (instance_cluster,dbname,schemaname,relname,last_autovacuum,table_size)
values('test-pg','test','public','sbtest111','2025-05-09 14:10:06',138862592);
insert into db_pg_autovacuum_monitoring (instance_cluster,dbname,schemaname,relname,last_autovacuum,table_size)
values('test-pg','test','public','sbtest111','2025-05-09 14:20:06',138862592);4 分组统计，找出前一天全天周期内vacuum最频繁的几个表
-- 把capture_time调到一天前，便于下面的sql查询的演示
update db_pg_autovacuum_monitoring set capture_time = '2025-05-08 14:18:15';
-- 分组统计，找出前一天全天周期内vacuum最频繁的几个表
SELECT
instance_cluster,
dbname,
schemaname,
relname,
count(*)
FROM
db_pg_autovacuum_monitoring
WHERE
capture_time BETWEEN DATE_SUB( CURRENT_DATE, INTERVAL 1 DAY )
AND DATE_SUB( CURRENT_DATE, INTERVAL 1 DAY ) + INTERVAL 1 DAY - INTERVAL 1 SECOND
GROUP BY
instance_cluster,
dbname,
schemaname,
relname
ORDER BY
count(*) DESC
LIMIT 10;
效果类似：
+------------------+--------+------------+------------------+----------+
| instance_cluster | dbname | schemaname | relname | count(*) |
+------------------+--------+------------+------------------+----------+
| test-pg | test | public | pgbench_accounts | 5 |
| test-pg | test | public | sbtest111 | 2 |
+------------------+--------+------------+------------------+----------+
2 rows in set (0.00 sec)也就是说 pgbench_accounts 这个表在昨天做了最多次数的autovacuum。
大致流程就这些了，可以将上面逻辑用python封装下，和数据库平台结合，定时调度并计算和告警。
一些优化手段
1 优化业务操作
减少不必要的更新和删除
合并小事务
2 调整 autovacuum 配置参数
增大 autovacuum_naptime：
autovacuum_naptime 决定了 autovacuum 守护进程检查是否需要对表进行清理的时间间隔（单位为秒）。增大这个值会使 autovacuum 检查频率降低，从而减少其启动频率。在 postgresql.conf 文件中修改该参数：
autovacuum_naptime = 600 # 例如从默认的10秒改为600秒 修改后需要重启 PostgreSQL 使配置生效。但要注意，设置过大可能导致数据库中死元组长时间积累，影响性能。
降低 autovacuum_max_workers：
autovacuum_max_workers 控制同时运行的 autovacuum 工作进程的最大数量。减少这个值意味着同一时间执行 autovacuum 操作的进程减少，从而降低对系统资源的占用，同时也会在一定程度上降低 autovacuum 的执行频率。在 postgresql.conf 文件中修改：
autovacuum_max_workers = 2 # 例如从默认的3个改为2个同样，修改后需重启服务。但设置过小可能导致 autovacuum 处理效率低下，无法及时清理死元组。
3. 定期手动执行 VACUUM
定期执行 VACUUM ANALYZE：
在业务低峰期定期手动执行 VACUUM ANALYZE 操作。VACUUM 用于清理死元组并回收空间，ANALYZE 用于更新统计信息。手动执行这些操作可以在系统负载较低时完成清理工作，避免 autovacuum 在业务高峰期自动触发。例如，可以通过操作系统的 cron 任务在每天凌晨 2 点执行：
0 2 * * * psql -U your_username -d your_database -c "VACUUM ANALYZE;"使用 VACUUM FULL（高危操作，你知道自己在做什么吗！！）：
VACUUM FULL 比普通的 VACUUM 更激进，它会重写整个表，将表中的数据重新整理并压缩，能更有效地清理空间和减少碎片化。但它会对表加排他锁，期间表不可读写。可以在维护窗口定期执行，例如：
VACUUM FULL your_table;由于其对业务的影响较大，所以只适用于对可用性要求不高的表，并且要在业务低谷期执行。
4. 调整事务隔离级别
选择合适的事务隔离级别：
在开始事务时指定隔离级别：
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 业务操作
COMMIT;参考文章： DBA 失职导致 PostgreSQL 日志疯涨 （公众号 Austindatabases ）
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
