前言

几周前，介绍了调优检查点的基础知识，在那篇文章中，我还提到autovacuum是第二个常见的性能问题来源(基于我们在邮件列表和客户那里看到的情况)。让我继续写一篇关于如何调整autovacuum的文章，以尽量减少性能问题的风险。在这篇文章中，我将简要地解释为什么我们甚至需要autovacuum(死行，膨胀以及如何autovacuum处理它)，然后转移到这篇博客文章的主要焦点-调优。我将介绍所有相关的配置选项，以及调优它们的一些基本规则。

注:这是在2016年最初写的一篇博文的更新版本，更新后反映了PostgreSQL配置的各种变化。否则，总体调优方法基本保持不变。

什么是死元组（或死行)？

在开始讨论调优autovacuum之前，我们首先需要了解什么是"死行"，以及为什么实际上需要由autovacuum执行清理......

注意:一些资源说"死元组"而不是"死行"------这几乎只是同一事物的不同名称。术语"元组"来自关系代数(关系数据库的基础)，因此更抽象，而"行"的含义通常更接近实现。但这些差异大多被忽略了，这两个术语可以互换使用。在这篇文章中，我将坚持使用"行"。

当您在Postgres中执行DELETE时，行不会立即从数据文件中删除。相反，只有通过在标题中设置xmax字段才能将其标记为已删除。类似于UPDATE，在Postgres中基本上相当于DELETE + INSERT。

这是Postgres中MVCC(多版本并发控制)的基本思想之一，允许不同的进程看到不同的行子集，这取决于它们何时获得快照。例如，在DELETE之前启动的SELECT应该看到旧的行，而在DELETE提交之后启动的SELECT应该看到新版本(至少对于默认的READ COMMITTED隔离级别)。

其他MVCC实现采用不同的方法，但Postgres创建行副本。这种MVCC实现的缺点是，即使在所有可能看到这些版本完成的事务之后，它也会留下已删除的行。

因此，最后有一行被"标记"为已删除，但仍然占用数据文件中的空间。如果您更新了一行100次，那么该行将有101个副本。如果不清理，这些"死行"(实际上对未来的任何事务都不可见)将永远留在数据文件中，从而浪费磁盘空间。对于具有大量delete和/或update操作的表，死行很容易占据绝大部分磁盘空间。当然，这些死行仍然从索引中引用，进一步增加了浪费的磁盘空间量。

这就是我们在PostgreSQL中所说的"膨胀"------表和索引相对于最小所需的空间量来说是膨胀的。当然，如果查询必须处理更多的数据(即使99%的数据被立即作为"不可见的"丢弃)，这也会影响这些查询的性能。

VACUUM and autovacuum

回收浪费空间(被死行占用)的最直接方法是手动运行VACUUM命令。这个维护命令将扫描表并从表和索引中删除死行。它不会将磁盘空间返回给操作系统/文件系统，但它只会为新行提供磁盘空间。

例如，如果您有一个10GB的表，但是其中9GB被死行使用，VACUUM将完成，表仍然是10GB。但是接下来的9GB行不需要扩展表---它将使用数据文件中回收的空间。当然，这个例子有点极端---您不应该允许表中存在9GB的死行。我们很快就会讲到。

注意: VACUUM FULL将回收空间并将其返回给操作系统，但它有许多缺点。它获得表上的独占锁，阻塞所有操作(包括只读查询)，并创建表的新副本，可能会使使用的磁盘空间增加一倍(因此，当磁盘空间已经用完时，这样做特别不切实际)。

手动运行VACUUM的问题是，它只在您决定运行时发生，而不是在需要时发生。如果在所有表上每5分钟运行一次，很可能大多数运行实际上不会清理任何东西。它只会检查表，可能会读取一些数据，但却发现还没有可以清理的数据。所以你只会浪费CPU和I/O资源。或者您可以选择不那么频繁地运行它，比如每天晚上运行一次，表可能很容易积累更多您想要的死行。

换句话说，获得正确的VACUUM调度是非常困难的，特别是对于可能随时间变化的工作负载(由于数据/周期间用户活动的变化，或由于应用程序的变化)。唯一的例外是对批量数据加载有非常明确的时间表的系统(例如，每晚批量加载的分析数据库)。

这导致我们使用autovacuum------一个负责及时触发清理的后台进程。通常情况下，这足以控制浪费空间的数量，但不要太频繁。诀窍在于数据库跟踪一段时间内产生的死行数(每个事务报告更新/删除的行数)，因此当表累积一定数量的死行时，它可以触发清理。这意味着在繁忙时期，清理工作将更加频繁。

autoanalyze

清理死行并不是autovacuum过程的唯一职责。它还负责收集用于查询规划的数据分布统计信息。您可以使用ANALYZE手动收集这些数据，但它会遇到与VACUUM类似的问题------很难经常运行它，但又不会太频繁。解决方案也是类似的---数据库可以监视修改的行数，并在超过某个阈值时自动运行ANALYZE。

注意: 对ANALYZE的负面影响更严重一些，因为虽然VACUUM的成本主要与死行数量成正比(当表中死行很少时相当低，使不必要的清理变得便宜)，但ANALYZE实际上必须在每次执行时重新构建统计信息(包括收集随机样本，这可能需要相当多的I/O)。另一方面，表中的膨胀可能会稍微降低查询速度，因为必须做更多的I/O。但是陈旧的统计数据意味着选择低效的查询计划和消耗大量资源(时间、CPU、I/O)的查询的风险很大。这种差异可能是指数级的。

在这篇博文的其余部分，我将忽略这个autovacuum任务------其配置与清理相当相似，并且遵循大致相同的推理。

监控

在开始调优之前，您需要能够收集相关数据。否则，您如何知道是否需要进行任何调优，或者如何评估更改的影响?换句话说，您需要进行一些基本的监视，定期从数据库中收集重要的指标。

如果你正在使用一些监控插件(你绝对应该这样做，有很多选择，包括免费的和商业的)，那么你很可能已经有了这些数据。但在本文中，我将使用数据库中可用的统计数据来演示调优。

为了进行清理，您至少需要查看这些值:

1. pg_stat_all_tables.N_dead_tup:  每个表(用户表和系统编目)中的死行数
2. (n_dead_tup / n_live_tup) : 每个表中死行/活行的比率
3. (pg_class.Relpages / pg_class.relrows) :  平均每行占用的空间大小

还有一个方便的pgstattuple扩展，允许您对表和索引执行分析，包括计算空闲空间，死行等。

调优目标

在查看实际配置参数之前，让我们简要讨论一下高级调优目标是什么，即在更改参数时我们想要实现什么:

• 清理死行------保持合理的磁盘空间量，不要浪费不合理的磁盘空间量，防止索引膨胀并保持查询速度。

• 尽量减少清理影响------不要太频繁地执行清理，因为这会浪费资源(CPU、I/O和RAM)，并可能严重损害性能。 也就是说，您需要找到适当的平衡------过于频繁地运行清理可能和不够频繁地运行清理一样糟糕。这种权衡很大程度上取决于数据量、您正在处理的工作负载类型(特别是DELETE/UPDATE的次数)。

大多数配置选项都有非常保守的默认值。首先，默认值通常是几年前根据当时可用的资源(CPU、RAM等)选择的。其次，我们希望默认配置可以在任何地方工作，包括像树莓派这样的小型机器或小型VPS服务器。我们不时地调整默认值(稍后我们将在这篇博客文章中看到)，但即使这样，我们也倾向于采取谨慎的小步骤。

对于较小的系统和/或处理以读取为主的工作负载的系统，默认配置参数可以很好地工作，但是对于大型系统，需要进行一些调优。随着数据库大小和/或写入量的增加，问题开始出现。

使用autovacuum，典型的问题是清理不经常发生，然后当它最终发生时，它会严重破坏查询的性能，因为它必须处理大量的垃圾。在这些情况下，你应该遵循这个简单的规则:

***如果受影响很大，说明你做得不够频繁。***

也就是说，您需要调优autovacuum配置，以便更频繁地进行清理，但每次执行时处理的死行数量更少。

现在我们知道我们想要实现什么，让我们看看配置参数...

注意: 人们有时会遵循不同的规则------如果受影响很大，就不要做。-然后禁用autovacuum。请不要这样做，除非你真的(真的)知道你在做什么，并且有一个定期执行的清理脚本(例如从cron)。否则，您将陷入困境，您最终将不得不处理性能严重下降甚至可能中断的问题，而不是性能有所下降。

Thresholds(阈值) 以及 Scale Factors(比例因子)

自然地，你可以调整的第一件事是何时触发清理，这受到两个参数(默认值)的影响:

Autovacuum_vacuum_threshold = 50
Autovacuum_vacuum_scale_factor = 0.2

每当表(可以看到为pg_stat_all_tables.n_dead_tup)的死行数超过threshold + pg_class.relrows * scale_factor时，就会触发清理。

这个公式表明，在清理之前，表的20%可能是死行(50行的阈值是为了防止非常频繁地清理小表，但对于大表，它与比例因子相比微不足道)。

那么，这些默认值有什么问题，尤其是比例因子? 这个值本质上决定了表的多少部分可以被"浪费"，20%对于中小型表来说非常合适------在10GB的表上，这个值允许最多2GB的死行。但是对于一个1TB的表，这意味着我们可以积累多达200GB的死行，然后当清理最终发生时，它将不得不一次做很多工作。

这是一个积累了大量死行，并且必须立即清除所有死行的示例，这将会造成伤害-它将使用大量I/O和CPU，生成WAL等等。这正是我们想要防止的对其他后端的破坏。

根据前面提到的规则，正确的解决方案是更频繁地触发清理。这可以通过显著降低比例因子来实现，也许像这样:

Autovacuum_vacuum_scale_factor = 0.01

这将限制降低到表的1%，即1TB表的~10GB。或者你可以完全放弃比例因子，只依赖于阈值:

Autovacuum_vacuum_scale_factor = 0

Autovacuum_vacuum_threshold = 10000

这将在生成10000行死行后触发清理。

需要考虑的一件事是，仅仅减少比例因子就可以更容易地触发小表上的清理------如果您有一个有1000行的表，比例因子1%意味着更新10行就足以使表符合清理条件。这似乎过于激进了。

最简单的解决方法就是忽略它，把它当作不存在的问题。清理小表的成本可能非常低，而对大表的改进通常非常显著，即使更频繁地处理小表，总体效果仍然是非常积极的。

但如果你想防止这种情况发生，可以稍微提高一下阈值。你可能会得到这样的结果:

Autovacuum_vacuum_scale_factor = 0.01

Autovacuum_vacuum_threshold = 1000

一旦累积了1000 + 1%的死行，就会触发清理表。对于小表，1000将主导决策，对于大表，1%的比例因子将更重要。

还要考虑到postgresql.conf中的参数影响整个集群(所有数据库中的所有表)。如果你只有少数几个大表，你也可以考虑使用ALTER TABLE修改它们的参数:

set autovacuum_vacuum_scale_factor = 0.01

ALTER TABLE large_table set autovacuum_vacuum_threshold = 10000

我强烈建议只修改postgresql.conf参数，只有当修改不够时才诉诸ALTER TABLE。它使清理行为的调试和分析变得更加复杂。如果您最终要这样做，请确保记录每个表的原因。

注意: 这些参数应该多低?为什么不选择0.001，即对于一个1TB的表只选择0.1% (1GB)呢?你可以试试，但我不推荐这么低的值。"浪费"的空间作为新数据的缓冲区，给系统一点空闲。从200GB到10GB节省了很多空间，同时仍然为新行留下了很多空间，而从10GB到1GB的好处要小得多。不值得冒过早触发清理的风险(在实际清理行之前，迫使再次尝试清理)。

Throttling (限流)

autovacuum系统内置的一个重要功能是节流。清理意味着后台维护任务，对用户查询等影响最小。我们当然不希望清理消耗如此多的资源(CPU和磁盘I/O)来影响常规的用户活动(例如，使查询变得更慢)。这就需要限制清理在一段时间内可以利用的资源量。

清理过程相当简单------它从数据文件中读取页面(8kB数据块)，并检查页面是否需要清理。如果没有死行，则不做任何更改就直接丢弃该页。否则，它将被清理(删除死行)，标记为"脏"，并最终写入(缓存并最终写入磁盘)。

让我们将"成本"分配给基本用例，表示所需资源的数量(数值在PG 14中发生了变化，除非另有明确说明，否则我会使用新值):

| 用例 | 选项 | PG 14+ | PG 13 及以前 | |--------------------|------------------------|------------|---------------| | shared buffer中的页 | vacuum_cost_page_hit | 1 | 1 | | 不在shared buffer中的页 | vacuum_cost_page_miss | 2 | 10 | | 需要清理的页 | vacuum_cost_page_dirty | 20 | 20 |

这表示，如果在共享缓冲区中找到一个页面，则花费1个令牌。如果必须从操作系统(可能从磁盘)读取它，则认为它稍微昂贵一些，并且需要2个令牌。最后，如果页面被清理弄脏并且需要写入，则计数为20。这使我们能够计算autovacuum在一段时间内所做的功。

然后通过限制一次可以完成的工作量(默认设置为200)来实现节流，并且每次清理完成这么多工作时，它都会休眠一会儿。睡眠时间原来是20毫秒，但在PG 12中减少到2毫秒。

autovacuum_vacuum_cost_delay = 2ms

autovacuum_vacuum_cost_limit = 200

我们来计算一下它实际能做多少功。延迟在PG 12中发生了变化，这意味着我们有三组释放，具有不同的参数参数值。

对于2ms的延迟，清理可以每秒运行500次，并且每轮200个令牌意味着每秒100000个令牌的预算(在PG 12之前的旧版本中，限制是10000个)。考虑到前面讨论的清理操作的成本，这意味着:

| | PG 14+ | PG 12 / 13 | PG11 | |------------------|----------|----------------|----------| | 从shared buffer里读 | 800 MB/s | 80 MB/s | 80 MB/s | | 从OS或磁盘读 | 400 MB/s | 40 MB/s | 8 MB/s | | 写（脏页) | 40 MB/s | 4 MB/s | 4 MB/s |

考虑到当前硬件的能力(假设本地存储)，PG 14之前版本的默认限制可能太低了。增加成本限制(可能是1000-2000，增加吞吐量5-10倍)或者以类似的方式降低成本延迟是一个好主意。当然，您可以调整其他参数(每页操作成本、睡眠延迟)，但我们不经常这样做------更改成本限制就足够了。

在PG 14中，限制明显更高，使得默认值更合适。

注意: 常规VACUUM具有相同的节流机制，但默认情况下是禁用的(vacuum_cost_delay设置为0)，但是如果您需要运行手动VACUUM，您可以启用节流以限制对数据库其余部分(用户查询等)的影响。

工作进程数量

一个还没有提到的配置选项是autovacuum_max_workers，那么它是关于什么的呢?清理不是由单个autovacuum进程执行的------相反，数据库启动到执行实际清理的autovacuum_max_workers进程(每个worker进程一次处理单个表)。默认配置允许最多3个这样的worker进程。

这绝对是有用的，因为您不希望在清理完一个大表之前停止清理小表(由于节流，这可能需要相当多的时间)。

问题是用户认为增加工作人员的数量也会增加可能发生的清理量。如果你允许6个autovacuum worker而不是默认的3个，它将做两倍的清理工作，对吧?

不幸的是,没有。前几段所述的成本限制是全球性的，由所有运行中的autovacuum worker共同承担。每个工作进程只获得全局限制成本限制的一小部分(大约为1/autovacuum_max_workers)。因此，增加工作线程的数量只会使它们运行得更慢，而不会增加清理吞吐量。

这有点像高速公路------汽车数量增加一倍，但速度减半，每小时到达目的地的人数大致相同。

因此，如果数据库上的清理工作跟不上用户活动，那么增加worker的数量将无济于事。您需要先调整其他参数。

针对单表限流

实际上，当我提到成本限制是全球性的，由所有autovacuum worker共同承担时，这并不完全正确。与比例因子和阈值类似，可以使用per table定义成本限制和延迟:

ALTER TABLE t SET (autovacuum_vacuum_cost_limit = 1000);

ALTER TABLE t SET (autovacuum_vacuum_cost_delay = 10);

但是，使用自定义限制处理这些表的 worker不包括在全局成本中，而是独立地进行节流(即限制适用于此单个worker)。

在实践中，我们几乎从不使用这个特性，我们建议不要使用它。这使得清理行为更加难以预测和推理------有多个工作线程有时一起节流，有时单独节流，这使得清理变得非常复杂。您可能希望对后台清理使用单个全局限制。

最后总结

如果我必须总结出一些基本规则，那就是这五条:

1. 不要关闭autovacuum，除非你真的知道你在做什么。认真对待。
2. 对于繁忙的数据库(进行大量的更新和删除)，特别是大型数据库，您可能应该降低比例因子，以便更频繁地进行清理。
3. 在合理的硬件(良好的存储，多核)上，您可能需要增加节流参数，以便清理工作能够跟上。这尤其适用于在Postgres 14之前的老版本。
4. 在大多数情况下，单独增加autovacuum_max_workers不会有帮助，因为它不会增加清理吞吐量。你会得到更多速度更慢的过程。
5. 您可以使用ALTER table为每个表设置参数，但如果确实需要，请三思。这使得系统更复杂，更难以检查。

我最初包含了几个部分来解释autovacuum不能真正工作的情况，以及如何检测它们(以及什么是最好的解决方案)，但是这篇博文已经太长了，所以我将把它留给另一篇博文。

原文链接:

https://www.enterprisedb.com/blog/autovacuum-tuning-basics