CP的PG运维手记系列1之autovacuum不工作问题解析

CP的PG运维手记系列1之autovacuum不工作问题解析

--------------------正文开始-----------------------

案例背景

分析过程

1.查看OS主机整体资源使用情况。

2.看具体SQL执行计划(explain)

3.查看表数据量以及表大小

4.查看pg_stat_all_tables视图的n_dead_tup、last_autovacuum列

5.查看运行pg_log下的运行日志

产生原因

本次问题产生原因是物理复制槽无效后未被删除导致。

可能产生该问题得到原因如下：

1.无效的复制槽未被删除。

2.长事物。

3.本地回环网络接口不可访问。autovacuum launcher process和stats collector process进程需要通过本地回环网络接口收集统计信息。

4.大表导致autovacuum过慢，autovacuum包含vacuum操作和analyze操作对于大表两个操作都会慢。

解决方案

1.无效的复制槽未被删除select * from pg_replication_slots;#查询复制槽状态select pg_drop_replication_slot('复制槽名');#删除无效复制槽

2.长事物设置参数idle_in_transaction_session_timeout (integer)终止任何已经闲置超过这个参数所指定的时间（以毫秒计）的打开事务的会话。这使得该会话所持有的任何锁被释放，并且其所持有的连接槽可以被重用， 它也允许只对这个事务可见的元组被清理。通过命令杀掉,占用长事物会话select * from pg_stat_activity; #查询会话状态pg_terminate_backend() #终止会话

3.本地回环网络接口不可访问。autovacuum launcher process和 stats collector process 进程需要通过本地回环网络接口收集统计信息。  pg_isready -h localhost -p 5432 #检查localhost连通性 ifconfig检查本地回环网口是否正常

4.大表导致autovacuum过慢，autovacuum包含vacuum操作 和analyze操作对于大表两个操作都会慢。  a.使用表分区,降低autovacuum操作的数据集大小。 #降低大表的比例因子大小 b.ALTER TABLE 表名 SET (autovacuum_analyze_scale_factor = 值); c.业务非繁忙期,定时执行vacuum。