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

    各位关注CP公众号的小伙伴们,公众号文章已经73天没有更新内容了,给各位看官报个歉先,最近CP工作日夜颠倒、身体不适,一直在调整状态，所以停更了一段时间,公众号从2021年5月份开始更新,得到了很多小伙伴们的支持和关注,在这里CP谢个先。有大家的陪伴和鼓励CP才能走到今天。一年的时间里,更新了 1.PG的后端特性。2.PG时序。3.PG学习笔记。这三部分都会继续更新。

    最近有好多小伙伴问CP一些PG相关的运维问题处理的内,有时候太忙,也没有及时给大家回复,报个歉先。出于这些问题,CP准备持续更新"CP的PG运维手记系列"文章,本套文章的出发点,只是解决运维过程中遇到各种问题如何快速解决,而不去长篇大论的讨论原理,简单直接的与大家分享如何解决问题。后面大家对原理感兴趣CP也会更新大家感兴趣的原理或者源码内容分享。

   江湖最高礼仪"抱拳了铁子"

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

案例背景

    A君提工单反馈,业务系统部分业务变慢,经过排查发现是SQL语句运行变慢。DBA介入分析.....此处过程省略1万字。

分析过程

   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。