PostgreSQL的AutoVacuum原理及autovacuum不工作问题解析
1、AutoVacuum概述
PostgreSQL数据库是有数据清理的，有人工执行清理，也有自动清理，但是这2种的清理方式对性能是有不同的影响，特别是OLTP环境中，每次不管是人工清理还是自动清理dead tuple，都会对数据库的IO有明显的影响，基于PostgreSQL 的原理每个表中的行会存在多个版本的数据，为了完成数据库的MVCC 多版本控制，以及数据的UNDO 的功能在这些过期版本的行被弃用后，会产生众多的死行dead tuple.在这样的情况下，如果不及时的将这些dead tuple 进行清理，轻则影响磁盘空间的在利用，重则影响数据库的性能，在测试中发现，在每次进行百万级别的dead tuple清理，数据库会有7%-10%的性能下降，多次往复亦是如此，同时会导致本来一张表3G的表就能存储，但最终由于dead tuple可能达到80G甚至更大的空间。
2、AutoVacuum参数说明
autovacuum
- 默认：autovacuum = on (布尔型)
- 表示是否开起autovacuum。当需要冻结xid（事务ID）时，尽管此值为off，PG也会进行vacuum。
log_autovacuum_min_duration
- 默认：log_autovacuum_min_duration = -1(数字型) 单位ms
- 在规定时长内未完成的vacuum予以记录日志，-1表示禁用，0表示所有的，大于0仅记录超过时间的。
autovacuum_max_workers
- 默认：autovacuum_max_workers = 3(数字型)
- autovacuum最大工作清理进程数,CPU核多IO好的情况下，可以增加，但是注意内存消耗，有多少个worker就会有多少倍的autovacuum_work_mem内存使用，会消耗较多内存，重启数据库生效。
autovacuum_naptime
- 默认：autovacuum_naptime = 1min(分钟)
- 两次vacuum启动的时间间隔。
autovacuum_analyze_threshold
- 默认：autovacuum_analyze_threshold = 50(数字型)
- 自动analyze操作的最小行数，有利于对SQL语句进行更精准匹配到最好的执行计划。
autovacuum_vacuum_scale_factor
- 默认：autovacuum_vacuum_scale_factor = 0.2(数字型)
- autovacuum的vacuum操作所需的变更量阈值,这个表的update/delete的tuple总数大于(pg_class.reltuples*autovacuum_vacuum_scale_factor+autovacuum_vacuum_threshold)时, 触发vacuum操作,建议不要太高频率，否则会因为vacuum产生较多的XLOG。
autovacuum_analyze_scale_factor
- 默认：autovacuum_analyze_scale_factor = 0.1(数字型)
- 表示autovacuum的analyze操作所需的变更量阈值,当这个表的INSERT/update/delete的tuple总数大于(pg_class.reltuples*autovacuum_analyze_scale_factor+autovacuum_analyze_threshold)时, 触发analyze操作。
autovacuum_freeze_max_age
- 默认：autovacuum_freeze_max_age = 200000000(数字型)
- 某表的pg_class.relfrozenxid的最大值，如果超出此值则重置xid,冻结表的记录。如果启用了autovacuum_freeze_max_age参数，还会防止事务ID过多导致的老化问题
autovacuum_vacuum_cost_delay
- 默认：autovacuum_vacuum_cost_delay = 20ms (数字型)
- 当autovacuum进程执行时，对vacuum执行cost进行评估，如果超过autovacuum_vacuum_cost_limit的值时，则延迟这么长的时间。
autovacuum_vacuum_cost_limit
- 默认：autovacuum_vacuum_cost_limit = -1 (数字型)
-
autovacuum进程的评估阀值，-1表示使用vacuum_cost_limit值，如果在执行 autovacuum进程期间评估的cost超过autovacuum_vacuum_cost_limit,则autovacuum进程则会休眠。
3、autovacuum不工作问题解析
（1）分析过程
1.查看OS主机整体资源使用情况。
2.看具体SQL执行计划(explain)
3.查看表数据量以及表大小
4.查看pg_stat_all_tables视图的n_dead_tup、last_autovacuum列
5.查看运行pg_log下的运行日志
（2)产生原因
本次问题产生原因是物理复制槽无效后未被删除导致。
可能产生该问题得到原因如下：
1.无效的复制槽未被删除。
2.长事物。
3.本地回环网络接口不可访问。autovacuum launcher process和stats collector process进程需要通过本地回环网络接口收集统计信息。
4.大表导致autovacuum过慢，autovacuum包含vacuum操作和analyze操作对于大表两个操作都会慢。
(3)解决方案
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。
4、autovacuum后空间回收原理
vacuum只清理死元组，不会回收空间，只有手动执行vacuum full时，才会收回空闲空间给操作系统。
如：表中有三条数据，大小有400M，出现严重表膨胀，执行autovacuum后，清理了死元组，但是400M的空间被回收，这是因为，死元组存储在这三条数据后，执行autovacuum会自动收回三条数据后面的空间，表大小是1M，如果死元组在这三条数据中间，执行autovacuum不会收回存储空间，这张表大小还是400M。
