写点什么

优化 PostgreSQL Autovacuum

发布于: 2020 年 12 月 21 日

作者: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_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


发布于: 2020 年 12 月 21 日阅读数: 21
用户头像

开源是一种商业模式适合于中国 2020.10.31 加入

官方公众号:开源软件联盟PostgreSQL分会 官方网站:postgresqlchina.com 官方交流社区:pgfans.cn 官方资源社区:postgreshub.cn

评论

发布
暂无评论
优化PostgreSQL Autovacuum