postgresql autovacuum 之 不看不知道

懂得postgresql 数据库原理的都知道 vacuum 的重要性，而相关线程中，autovacuum 是重要的，或者说最重要也是当之无愧。

Autovacuum 作为postgresql 的一个进程一致在工作。

那么这个autovacuum 在除了进行和他名字一样的工作之外，他还负责要收集更多关于dead tuple 和膨胀的信息，要对更新表统计信息的表进行分析，以便优化器能够为SQL语句选择最佳执行计划。

在PG中还有另外一进程 stats collector ，用于跟踪使用情况和活动信息。这些信息会被autovacuum来进行利用。来更好的进行相关的清理工作。

这两个功能在 postgresql.conf 中的开关就是

同时在系统进行vacuum的同时，作为DBA 是希望能进行分析的所以日志中能进行记录那是最好的。所以log_autoovacuum_min_duration 可以记录指定时间超过250毫秒的vacuum 就会被记录。

那在重启后，我们能看到的日志中的记录就是下面这个样子。所以Postgresql在日志方面的记录是很全面的，这相对于某些数据库（SQL SERVER 和 MYSQL）要好的太多了。

所以给POSTGRESQL 日志配一个好的快速的空间在大型频繁的系统是有必要的。

下面接着的问题是，到底什么样的表会被进行autovacuum，或者是备选对象。

实际死的tuple >= autovacuum_vacuum_scale_factor * number of tuples + autovacuum_vacuum_threshold

上面的公式就是表在插入，更新，删除后会被选入到autovacuum中的方法。下面的参数们就是触发分析或进行autovacuum的选择项

例如上面的操作 例如一个表中的行数是1000行（1000*0.2）+50 = 250行，当这个表现在的死行超过250行，就要触发vacuum了。所以调整这个参数是很重要的，另外如果有大表的情况下，你会发现你清理dead T 的速度是越来越慢，那就的对有些大表进行自定义。

怎么定义？首先先确认经常有处理不完的dead tuple 是那些表，

SELECT n_tup_ins as "inserts",n_tup_upd as "updates",n_tup_del as "deletes", n_live_tup as "live_tuples", n_dead_tup as "dead_tuples"

FROM pg_stat_user_tables

通过手动的方式，可以调整这两个参数来达到针对这个表进行更频繁或者更不频繁的vacuum的方法。

在讲完这些后，还有一个问题，就是清理表的vacuum 的线程有没有数量的限制。autovacuum_max_workers 就是控制工作线程的数量的参数，如果你有四个数据库，则另一个数据库就要等待，到下一个周期才能轮上这个数据库的表进行操作。所以可以适当的调整一下这个参数，如果你一个cluster中的数据库比较多的情况下。

说到这里，目前还有一个问题需要考虑，就是业务繁忙时期到底要不要进行AUTOVACUUM, 是不是会在业务繁忙的时候，雪上加霜，对于性能更不利。

自动真空从磁盘读取一个表的8KB(默认的block_size)页，并修改/写入包含死元组的页。这涉及读和写IO。因此，这可能是一个IO密集型操作，在事务高峰期间，在一个包含许多死元组的巨大表上运行一个自动真空，是否是一件好事，所以为了避免这样的情况，可以在参数中进行配置。

vacuum_cost_page_hit 是你在缓冲中能读取页面的cost

vacuum_cost_page_miss 是不在缓冲中读取的页面的cost

vacuum_cost_page_dirty 是在页面中发现dead T 后进行重写的成本

假如 vacuum_cost_delay = 20 的情况下，一秒是1000毫秒，则会发生50次的vacuum，在默认的情况下 根据时间的定义

下面有一个公式

200 * 50 * 8 = 在buffer 中可以处理的dead T数据量

200/10*50 *8= 在磁盘中可以处理的dead T 的数据量

200/20*50*8 = 在一个周期可以处理的dead T 并改写的数量

这三个数量是成递减的分布的，所以调高autovacuum_max_workers，成本被平均分配给实例中运行的所有autovacuum进程的autovacuum数量。因此，增加autovacuum um_max_workers可能会延迟当前运行的autovacuum workers的自动真空执行。增加autovacuum um_vacuum um_cost_limit可能会导致IO瓶颈。

当然这些参数也是可以针对表进行特殊设置的。所以到此autovacuum 的调整绝对属于一个高智商的数学和判断题。