关于postgresql:优化PostgreSQL-Autovacuum

作者：Laurenz Albe 是 CYBERTEC 的高级顾问和反对工程师。自 2006 年以来，他始终在 PostgreSQL 上工作并为 PostgreSQL 做奉献。

译者：类延良，任职于瀚高根底软件股份有限公司，PostgreSQL 数据库技术爱好者，10g &11g OCM，OGG 认证专家。

在许多 PostgreSQL 数据库中，您无需思考或放心调整 autovacuum。它会在后盾主动运行，并在不障碍您的状况下进行清理。

然而有时默认配置还不够好，您必须调整 autovacuum 以使其失常工作。本文介绍了一些典型的问题计划，并介绍了在这些状况下的解决办法。

autovacuum 的工作

有许多 autovacuum 的配置参数，这会使调整变得复杂。次要起因是 autovacuum 具备许多不同的工作。从某种意义上说，autovacuum 必须解决由 PostgreSQL 的多版本并发管制（MVCC）实现引起的所有问题：

• 清理 UPDATE 或 DELETE 操作后留下的“死元组”
• 更新可用空间映射(free space map)，以跟踪表块中的可用空间
• 更新仅索引扫描所需的可见性图(visibility map)
• “解冻”(freeze)表行，以便事务 ID 计数器能够平安地盘绕

依据这些性能中的哪个会导致问题，您须要不同的办法来调整 autovacuum。

调整 autovacuum 以革除死元组

最有名的 autovacuum 工作是清理 UPDATE 或 DELETE 操作中的死元组。如果 autovacuum 不能跟上清理死元组的速度，则应遵循以下三个调整步骤：

确保没有任何货色能够阻止 autovacuum 回收死元组
查看避免 vacuum 革除死元组的已知起因 https://www.cybertec-postgresql.com/en/reasons-why-vacuum-wont-remove-dead-rows/ 罪魁祸首通常是长期事务。除非您能够打消这些阻碍，否则调整 autovacuum 将无用。

如果您不能从根本上解决问题，则能够应用配置参数
idle_in_transaction_session_timeout 使 PostgreSQL 终止会话，这些会话在事务中处于“idle in transaction”的工夫过长。这会在客户端导致谬误，然而如果您没有其余办法能够放弃数据库失常运行，这可能是有情理的。同样，要解决长期运行的 query，能够应用 statement_timeout 配置参数。

调整 autovacuum 以使其运行更快
如果 autovacuum 无奈跟上清理死元组的速度，那么解决方案就是使其工作更快。这看起来仿佛很显著，然而许多人陷入了使 autovacuum 更早开始或更频繁运行将解决问题的陷阱。

VACUUM 是一项消耗资源的操作，因而默认状况下，autovacuum 操作的速度成心升高。目标是使其在后盾运行而不障碍失常的数据库操作。然而，如果您的工作负载创立了很多死元组，那么您将不得不使其更具侵略性：

• 从 autovacuum_vacuum_cost_limit 默认值 200 开始减少（这是一种柔和的办法）
• 从 autovacuum_vacuum_cost_delay 从默认值 2 开始缩小（在旧版本：20）毫秒（这是无效的办法）

设置 autovacuum_vacuum_cost_delay 为零将使 autovacuum 与手动 VACUUM 速度一样快，即尽可能快。

因为并非所有表都以雷同的速度增长死元组，因而通常最好不要更改中的全局设置 postgresql.conf，而要独自更改忙碌表的设置：
ALTER TABLE busy_table SET (autovacuum_vacuum_cost_delay = 1);

对表进行分区还能够帮忙更快地实现工作。请参阅本文 partition 局部理解更多信息。

更改工作负载，以便生成更少的死元组
如果没有其余成果，则必须看到生成的死元组更少。兴许将几个 UPDATE 合并为一行 UPDATE 通常，您能够应用“HOT 更新”来显着缩小死元组的数量：

• 将表的 fillfactor 参数设置为小于 100 的值，以使 INSERTs 在每个块中保留一些可用空间
• 确保你在 update 语句中批改的列不是索引列

而后，任何 SELECT 或 DML 语句都能够革除死元组，而对 VACUUM 的需要则更少。

调整仅索引扫描的 autovacuum
索引扫描的低廉局部是查找理论的表行。如果您想要的所有列都在索引中，则齐全不须要拜访该表。然而在 PostgreSQL 中，您还必须查看一个元组是否可见，并且该信息仅存储在表中。

为了解决这个问题，PostgreSQL 对每个表都有一个“可见性图”(visibility map)。如果一个表块在可见性图 (visibility map) 中被标记为“所有可见”，则不用拜访该表以获取可见性信息。

因而，要取得真正的仅索引扫描，autovacuum 必须处理表并常常更新可见性图(visibility map)。为此，如何配置 autovacuum 取决于查问收到的数据批改类型：

为接管 UPDATEs 或 DELETEs 的表的仅索引扫描调整 autovacuum
为此，您能够缩小表的存储参数 autovacuum_vacuum_scale_factor，例如
ALTER TABLE mytable SET (autovacuum_vacuum_scale_factor = 0.01);

依照如上所述放慢 autovacuum 是一个好主见。

调整仅接管 INSERTs 的表的仅索引扫描的 autovacuum
从 v13 开始，这很简略：对配置参数 autovacuum_vacuum_insert_scale_factor 进行调整，调整办法是如上所示对 autovacuum_vacuum_scale_factor 的调整。

对于较旧的 PostgreSQL 版本，您能够做的最好办法就是升高 autovacuum_freeze_max_age，最佳值取决于您应用事务 id 的速率，如果您每天耗费 100000 个事务 id，并且心愿每天主动清理 table，则能够进行如下设置：

ALTER TABLE insert_only SET (autovacuum_freeze_max_age = 100000);

要测量事务 ID 耗费的速率，请在一个长的工夫距离内应用两次 txid_current()函数 (或者从 V13 开始的 pg_current_xact_id() 函数)，而后取其差值。

调整 autovacuum 以防止事务回绕问题
失常时，autovacuum 关怀并启动一个非凡的“anti-warparound”autovacuum worker，不管一个表中最老的 transaction id 比 autovacuum_freeze_max_age 参数值更老或者一个表中的最老的 multiact 比 autovacuum_multixact_freeze_max_age 参数值更老

确保 anti-wraparound vacuum 能够 freeze 掉所有表中的行
再次强调，你不得不确保：没有阻塞 autovacuum 进行 freeze 老元组和改良 pg_database.datfrozenxid 以及 pg_database.datminmxid。这些阻塞者包含：

• 运行工夫很长的 session，这些 session 放弃一个事务处于 open 状态或者有长期表（autovacuum 不能解决长期表）
• 数据损坏，这会导致所有的 autovacuum worker 运行失败。

为了避免数据损坏，请应用更好的硬件，并总是运行最新的 PostgrSQL 的主要版本。

为接管 updates 或者 Deletes 的 tables 调优 anti-wraparound vacuum
在接管 updates 或者 deletes 的 table 上，你不得不做的所有是看 autovacuum 正在运行，并且足够快以便及时实现（参见上文）

为接管 inserts 的 tables 调优 anti-wraparound vacuum
从 PostgreSQL v13 开始，在这种状况下没有非凡思考，因为您也能够在此类表上定期运行 autovacuum。
在此之前，仅插入表是有问题的：因为没有死元组，因而永远不会触发失常的 autovacuum 运行。

而后，一旦 autovacuum_freeze_max_age 或 autovacuum_multixact_freeze_max_age 超过该值，您可能会忽然取得大量的 autovacuum 运行，从而解冻整个大表，破费很长时间并导致大量的 I / O。

为防止这种状况，请缩小 autovacuum_freeze_max_age 该表：
ALTER TABLE insert_only SET (autovacuum_freeze_max_age = 10000000);

分区
对于很大的表，倡议应用分区。这样做的益处是您能够让多个 autovacuum workers 并行处理多个分区，因而整个分区表的实现速度比单个 autovacuum worker 快。

如果您有多个分区，则应减少 autovacuum_max_workers，该参数是指 autovacuum workers 的最大数量。

只有更新影响所有分区，分区还能够帮忙清理接管大量更新的表。

调优 autoanalyze
更新表统计信息是主动清理的“辅助工作”。

您晓得，如果您的查问打算在手工对表执行 ANALYZE 后变得更好，那么主动统计信息收集将不会常常产生。

在这种状况下，您能够升高 autovacuum_analyze_scale_factor 以使 autoanalyze 更频繁地处理表：
ALTER TABLE mytable SET (autovacuum_analyze_scale_factor = 0.02);

另一种抉择是不应用 scale factor，而是应用 set autovacuum_analyze_threshold，以便每当固定数量的行产生更改时，就计算表统计信息。

例如，要配置每当超过一百万行更改时要剖析的表：

ALTER TABLE mytable SET (
autovacuum_analyze_scale_factor = 0,
autovacuum_analyze_threshold = 1000000
);

论断
依据您的特定问题和 PostgreSQL 版本，有不同的调整开关能够使 autovacuum 正确执行其工作。autovacuum 的许多工作和许多配置参数并没有使它变得更容易。

如果本文中的提醒还不够，请思考寻求业余征询
(https://www.cybertec-postgresql.com/en/services/postgresql-consulting/)

原文链接：
https://www.cybertec-postgresql.com/en/tuning-autovacuum-postgresql/

更多精彩内容，请关注以下平台、网站：

中国 Postgre SQL 分会官网公众号（技术文章、技术流动）：
开源软件联盟 PostgreSQL 分会

中国 Postgre SQL 分会技术问答社区：
www.pgfans.cn

中国 Postgre SQL 分会官方网站：
www.postgresqlchina.com