POSTGRESQL 提高POSTGRESQL性能的一些习惯 (3)

这个系列写到第三期了，实际上POSTGRESQL 的优化和一个核心之一，这就是VACUUM，一个弄不清vacuum，autovacuum的PG 管理员一定是不大合格的PG DBA。

这里不能从原理开始，这篇文字中会提及PG 13 中关于autovacuum的一些技巧，后期会写一些关于AUTOVACUUM的脚本。

第一个问题，autovacuum能满足日常的表的vacuum 操作需求吗？

这个问题其实是不大好回答的，如果你的数据库只是小批量的应用，大概率都是满足需求的，但是如果是大型的数据库应用，回答一定是，NO ，实际上我们的数据库大多就不符合安装postgresql 或者云上的POSTGRESQL 的默认设定。主要的问题在以下的部分

1 autovacuum 工作的线程数不足

这个问题一般存在于表多，并且大表多的数据库，以及逻辑库较多的数据库中，其实一句话可以解释，就是表太多，几个周期中，你的表并未能被轮询到，并且被autovacuum进行处理，导致你的表一直长期得不到 autovacuum的“雨露均沾”。

解决这个问题的核心在于你是否有足够的autovacuum的线程，来在较少的周期内轮询到大部分表，而不会因为线程少而导致根本轮不上的情况。

当然还有一些极端的情况，我们也是遇到过的就是一个大表在运行autovacuum 时很长时间根本运行不完，有的运行了2个小时，还在一个表上 autovacuum，这也是导致 autovacuum的线程不够用的问题，和表轮询不到autovacuum的问题产生的一个原因。

解决方案：对于大型的POSTGRESQL 数据库，一定要有足够的CPU 资源，和更大的autovacuum , 这里就需要调整你的 autovacuum_max_workers 的参数了，默认在PG13 是 3 个，这在大型的数据库中，不够用是很正常的，我们调整成 6 这个参数。CPU 均在 8C -16C 。

另外一定不要贪，将autovacuum_max_workers 调整的太大，会导致另一个问题，系统在触发autovacuum 可能过于集中，导致磁盘的压力上升，产生性能问题，所以这不是一个 左 右的问题，而是一个 适中的问题。

（需要写脚本，去找出是否存在表长期轮休不到autovacuum的问题的分析脚本）。

2 另一个autovacuum的核心问题就是，参数设计不合理的问题。

对于小表来说autovacuum的参数设计是可以的，基本上不会出现问题，但是对于大表来说，统一个AUTOVACUUM 的参数就是一个灾难，并且随着数据量的加大，越来越难。

autovacuum_analyze_scale_factor 0.1 sighup

autovacuum_analyze_threshold 50 sighup

假设你有一张5千万的表，如果想触发一次autovacuum的成本在

50000000 * 0.1 + 50 = 5000000 + 50 = 5000050 ，那么每次要等操作的死行在 500万以上，才能触发一次autovacuum ,这一定不是什么好事。

所以动态调整autovacuum针对每个表一定是一个必选项，尤其针对大表和频繁进行更改的表update ，太多的表。

所以我们需要一个能动态调整表的以上两个参数的程序。避免大表太长时间达不到触发条件。

3 autovacuum cost 太低导致autovacuum 速度太慢

autovacuum的工作速度是很有可能被限制的，除了表的索引太多，表太大，会导致autovacuum一个表的时间很长，2个小时还无法结束，而另一个问题就是autovacuum 的cost 的问题，这里vacuum会被一下的一些值所限制

vacuum_cost_page_hit ,这个位置是与 vacuum 工作的buffer有关，如果你的 vacuum的buffer 不足，则命中需要进行 vaccum的页面的成本就高了。

vaccum_cost_page_miss 这个位置还是与内存有关，如果页面需要读入到缓存中，但缓存没有空间了，就必须清理缓冲，清理缓冲也是需要有成本的，所以缓存的大小还是很重要的。

vaccum_cost_page_dirty 操作页面时，需要对页面进行清理的工作的成本这里涉及的是IO的成本。

如果这些成本累加，达到了 vaccum_cost_limit 的默认成本的值的情况下，则vacuum 就停止了，并且停止vacuum_cost_delay的时间为默认2毫秒，当设置为0 时则自动停止延迟的设置。所以您可以禁用成本限制(通过将autovacuum_vacuum_cost_delay设置为0)，或者通过减少autovacuum_vacuum_cost_delay或将autovacuum_vacuum_cost_limit设置为较高的值(如10000)来减少其影响。

这个位置也可以写一些程序，立即停止一些 autovacuum的工作。

当然除此之外，长期运行的事务，未使用的逻辑复制槽，不进行 commit 的事务，全部是autovacuum 看上去不工作的可能原因之一。

最后所有的vacuum的工作不能指望 autovaccum 全部完成，在夜间定期对的表进行vacuum 工作，也是一种避免工作期间出现autovacuum 影响工作的的一种好的工作方式。