PostgreSQL 的内置 autovacuum（管家）正在不断改进，一个又一个版本。它在减少开销和解决边缘情况的同时变得更有能力。我认为没有任何 PostgreSQL 版本没有任何 autovacuum 改进，毫无疑问它对于大多数用例来说已经足够好了。
但是，这远非完美适合任何特定环境。在与许多客户环境合作时，我们不断看到内置逻辑不足的情况。当我继续为许多 Percona 客户进行修复时，我想为每个人记下重要的点。
常见问题/限制
- 表格在高峰时段成为自动清理的候选者。
autovacuum 设置基于比例因子/阈值。当桌子上有大量交易时，桌子越过这些限制的可能性很高——这是高峰时间。实际上，它在非常错误的时间被踢了。 - 挨饿的桌子。
很常见的情况是，有几张桌子过于频繁地成为真空吸尘器的候选者，并反复占用所有工作人员。虽然候选名单中的其他表格长期处于空缺状态。当前的 autovacuum 智能不足以了解谁更饿并给予更好的优先级 - 无法动态控制 autovacuum worker 的节流。
这可能是最糟糕的。即使有一个知情的 DBA，他想autovacuum_vacuum_cost_limit根据需要或时间窗口调整并通知 PostgreSQL。
例如： 这对当前运行的 autovacuum worker没有影响。只有下一个开始工作的工作人员才会考虑这个设置。所以这不能用于解决问题。
ALTER SYSTEM set autovacuum_vacuum_cost_limit = 2000;
select pg_reload_conf(); - DBA 调整参数的尝试往往适得其反。
在看到陈旧的表和饥饿的表之后，绝望的 DBA 保持激进的设置和更多的工作人员。很多时候，这会使系统超出其限制，因为当系统已经有大量活动会话时，一切都在错误的时间以高攻击性出现。乘以maintenance_work_mem工人的分配。系统性能受到很大影响。我见过的最糟糕的情况是 autovacuum worker 占用了高达 50% 的服务器资源。 - 活动时间窗口期间的 Autovacuum 违背了它自己的目的。
如果在高活动窗口期间需要时间来完成，autovacuum worker 将引用旧的 xid/快照。如此有效，它不会清理在同一持续时间内生成的死元组，这与 autovacuum 的目的背道而驰 - 饥饿的表会触发回绕预防 autovacuum。
很常见的情况是，在较长时间的 autovacuum 到达autovacuum_freeze_max_age和环绕预防激进真空中被饥饿的表被触发。
由于这种效率低下，我们不断看到 DBA 倾向于完全禁用自动清理并引发更多问题甚至中断。至少，我对 PostgreSQL 新手的要求是，请永远不要尝试关闭 autovacuum。这不是解决 autovacuum 相关问题的方法。
调整 Autovacuum
调整 autovacuum 显然是第一道防线。
全局级别设置
参数autovacuum_vacuum_cost_limit和autovacuum_vacuum_cost_delay是控制 autovacuum worker 节流的主要两个参数。autovacuum_max_workers控制一次在不同桌子上工作的工人数量。默认情况下，autovacuum_vacuum_cost_limit将被禁用 (-1)，这意味着其他参数的值vacuum_cost_limit将生效。因此，最重要的建议是设置一个值，autovacuum_vacuum_cost_limit以帮助我们单独控制 autovacuum 工作人员。
我在许多安装中看到的一个常见错误是将autovacuum_max_workers其设置为非常高的值，例如 15!。假设这会使 autovacuum 运行得更快。请记住，这autovacuum_vacuum_cost_limit是在所有工人之间分配的。所以工人的数量越高，每个工人的运行速度就越慢。如上所述，较慢的工作人员意味着无效的清理工作。而且，它们中的每一个都可以占用最多maintenance_work_mem。一般来说，默认值autovacuum_max_workers3 就足够了。请仅在绝对必要时考虑增加它。
表级分析设置
实例级别的一揽子调整设置可能对至少一些表不起作用。这些异常值需要特殊处理，并且在表级别调整设置可能变得不可避免。我将从那些过于频繁地成为 autovacuum 候选者的表开始。
带有该设置的 PostgreSQL 日志提供log_autovacuum_min_duration了那些经常成为候选表的详细信息，以及那些花费大量时间和精力的 autovacuum 运行。就个人而言，我更喜欢以此为起点。也可以通过比较两个不同时间戳中的autovacuum_countof来获得 autovacuum 运行的摘要。pg_stat_all_tables我们需要考虑的是 HOT（仅堆元组）更新和fillfactor. n_tup_hot_upd可以使用同一视图 (pg_stat_all_tables)分析热更新信息，对此进行调整可以大大降低真空要求。
配备所有这些信息分析，可以调整特定的表级别设置。例如：
Shell
1
alter table t1 set (autovacuum_vacuum_scale_factor=0.0, autovacuum_vacuum_threshold=130000, autovacuum_analyze_scale_factor=0.0, autovacuum_analyze_threshold=630000, autovacuum_enabled=true, fillfactor=82);
补充计划的真空作业
我们的目标不是禁用 autovacuum，而是用我们对系统的了解来补充 autovacuum。它根本不需要复杂。我们可以拥有的最简单的方法是在其自身或其 TOAST 具有最大年龄的表上运行“真空冻结”。
例如，我们可以有vaccumjob.sql以下内容的文件
Shell
1
2
3
4
5
6
7
8
9
10
WITH cur_vaccs AS (SELECT split_part(split_part(substring(query from '.*\..*'),'.',2),' ',1) as tab FROM pg_stat_activity WHERE query like 'autovacuum%')
select 'VACUUM FREEZE "'|| n.nspname ||'"."'|| c.relname ||'";'
from pg_class c
inner join pg_namespace n on c.relnamespace = n.oid
left join pg_class t on c.reltoastrelid = t.oid and t.relkind = 't'
where c.relkind in ('r','m')
AND NOT EXISTS (SELECT * FROM cur_vaccs WHERE tab = c.relname)
order by GREATEST(age(c.relfrozenxid),age(t.relfrozenxid)) DESC
limit 100;
\gexec
该查询获取 100 个当前未进行自动清理的老化表，并对它们运行“VACUUM FREEZE”。（末尾的 \gexec 执行查询输出）
这可以使用corn来安排低活动窗口，例如：
Shell
1
20 11 * * * /full/path/to/psql -X -f /path/to/vacuumjob.sql > /tmp/vacuumjob.out 2>&1
如果有多个低影响窗口，则可以使用多个时间表来利用所有这些窗口。
实际上，我们已经看到基于表年龄方法的补充计划的真空作业具有以下积极效果
- 这些表格在高峰时段再次成为候选表格的机会大大降低。
- 能够在真空和冻结操作的非高峰时间实现非常有效的服务器资源利用。
- 由于候选人的选择基于与默认标准（比例因子和阈值）完全不同的标准（餐桌年龄），因此消除了一些餐桌永远挨饿的机会。此外，这消除了同一张表一次又一次地成为真空候选者的可能性。
- 在客户/用户环境中，几乎不会再报告环绕式预防 autovacuum。
概括
在实例级别直到表级别，autovacuum 仍未调整或使用不良设置的系统并不罕见。只想总结一下：
- 默认设置在大多数系统中可能效果不佳。重复 autovacuum 在几个表上运行，而其他表饿死 autovacuum 是很常见的。
- 糟糕的设置可能会导致 autovacuum 工作人员占用相当一部分服务器资源而收益甚微。
- 当系统经历大量事务时，Autovacuum 具有在错误时间启动的自然趋势。
- 实际上，对于那些经历繁重事务和具有大量事务表并且预计会出现峰值和负载高峰时间段的系统，计划的清理作业变得必要。
清晰的分析和调整很重要。并且始终强烈建议您进行自定义真空作业，以占用您对系统和影响最小的时间窗口的了解。
原文标题：Importance of PostgreSQL Vacuum Tuning and Custom Scheduled Vacuum Job
原文作者：By Jobin Augustine
原文地址：https://www.percona.com/blog/importance-of-postgresql-vacuum-tuning-and-custom-scheduled-vacuum-job/
