PG数据库AUTOVACUUM的优化小技巧

讨论了AUTOVACUUM的必要性，对于大型PostgreSQL数据库来说，AUTOVACUUM时必须的，而且优化好AUTOVACUUM对于大型PostgreSQL数据库来说至关重要。那么该如何来优化AUTOAVCUUM呢？今天我们就讨论一下优化AUTOVACUUM的一些常用的技巧。

首先我们要考虑的是三个基本的参数autovacuum_max_workers、maintenance_work_mem、autovacuum_freeze_max_age，这三个参数设置的合理，大部分AUTOVACUUM的问题都得到了解决。

autovacuum_max_workers指定了autovacuum使用的工作进程的数量，默认为3，对于绝大多数大型PostgreSQL数据库来说，这个值太小了。增加 autovacuum worker的数量意味着更多的进程可用于清理数据，这是面对数量非常多的表时需要考虑的问题。如果仅仅从autovacuum去考虑，理想情况下为每个 CPU 创建一个worker肯定会有最好的效率。因此worker的设置不应超过 CPU 的数量，但过多的worker可能会因 CPU 使用率的激增而对业务产生影响。通常应该将worker的数量设置为3-cpu_count之间。具体如何设置，需要在最大化autovacuum效率与整体系统性能之间进行平衡。如果你不知道该如何设置，那么最先设置为CPU_COUNT的1/2吧，如果在系统高峰期，CPU资源出现了瓶颈，那么适当缩小该值，如果CPU资源还很充足，但是autovacuum的性能不足，那么就增加这个值。

maintenance_work_mem，这个参数的值越大，vacuum的效率越高。不过要注意的是，当超出了每个设定后，增加该参数的效率会出现递减。太大的值会浪费内存资源，如果设置的太大耗尽了内存，那么就会产生十分严重的性能问题。对于较大的Post过热SQL数据库实例，如果物理内存充足，建议maintenance_work_mem至少设置为 1 GB，设置较大的参数将显着提高清理具有大量死元组的表的性能。 1 GB的维护工作内存足以一次处理大约 1.79 亿个死元组，如果你一次性要处理更多的死元组，那么还需要继续加大这个参数。如果这个参数设置不足以一次性清理如此多的死元组，那么会采用多次扫描的方式来清理，这样就会降低清理的性能。

autovacuum_freeze_max_age，合理设置此参数可以减少 TXID 回绕的机会。Max age越大，autovacuum运行的频率就越低，这样就可以减少整个系统的负载，降低业务高峰期的性能风险。但是和上面提及的所有 autovacuum 参数一样，参数的设置都具有两面性。将该值设置的太大，导致autovacuum延迟太久，那么可能会在autovacuum完成之前耗尽 txid 编号，从而导致服务器强制关闭以保护数据完整性。设置合理的参数取决于对你的系统的死元组年龄的持续观察。

可能对于很大一部分PostgreSQL数据库来说，合理设置了这三个参数就基本上可以高枕无忧了。不过我们往往会面临更为复杂的情况。对于电信、互联网、银行等的超大型的数据库来说，这三个参数还不足以解决autovacuum的问题。要彻底解决问题，那么就需要对这些系统级参数无法解决问题的表设置个性化的autovacuum参数。这些参数属于表的storage参数。通过ALTER TABLE .. SET STORAGE_PARAMETER可以进行个性化的设置。

Storage参数中，和Vacuum有关的参数很多。在数据库运行过程中，随着数据局的变化，死元组的数量也在增加。autovacuum 启动器进程持续监视数据库中的表，并在某张表中的死元组数量达到 autovacuum 阈值后为vacuum 作业选择正确的表。该阈值的计算是基于一些参数的设置的，这个设置可以是全局性的，也可以是针对某张表的（比如上面的storage参数），如autovacuum_vacuum_threshold，autovacuum_analyze_threshold，autovacuum_vacuum_scale_factor，和autovacuum_analyze_scale_factor。

autovacuum_vacuum_threshold 和 autovacuum_analyze_threshold这两个参数分别确定要为 autovacuum 和 autoanalyzer 表中从上一次vacuum后更新或删除的记录数的最小数量。两者的默认值都是 50。

autovacuum_vacuum_scale_factor 和 autovacuum_analyze_scale_factor参数分别确定要为 autovacuum 和 autoanalyzer 安排的表需要更改的表的百分比。默认autovacuum_vacuum_scale_factor值为 0.2 (20%) 和autovacuum_analyze_scale_factor 0.1 (10%)。

对于上述四个参数，如果表中的记录数不是很大，那么就可以使用全局的设置，默认的参数一般都没有太大问题。但是对于有大量行的超级大表，这些参数的默认值就完全不够用了。Vacuum threshold的计算公式如下:

vacuum threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * n_live_tup

其中n_live_tup是活动元组的数量，这个值可以从pg_stat_all_tables视图获得。一旦死元组的数量达到了这个限额，那么就可以启动vacuum作业。如果 postmaster 启动工作进程失败，它会在共享内存中设置一个标志并向启动进程发送一个信号。读取 postmaster 的信号，启动器再次尝试通过向 postmaster 发送信号来启动工作进程（postmaster 未能启动工作进程可能是因为高负载和内存压力或已经运行的进程太多）。

基于上面的原理，为某些大表设置合理的阈值，从而确保autovacuum在一种对业务系统影响尽可能小的平衡状态下运行，就可以确保业务系统长期稳定的运行。从另外一个角度来说，对于超大型的PostgreSQL数据库来说，加强对pg_stat_all_tables中相关的统计数据的监控，也可以为我们动态的优化autovacuum提供最为准确的依据。