监控采集pg的autovacuum情况

原创

保持热爱奔赴山海

发布于 2025-05-09 15:04:17

4060

文章被收录于专栏：数据库相关数据库相关

生产上，谁也不希望见到这种情况：

业务正处于高峰期，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 删除。

postgresql

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

postgresql

登录后参与评论

0 条评论

热度