前言
几周前,我介绍了检查点调优的基础知识,在那篇文章中,我还提到自动清理是第二个常见的性能问题来源(根据我们在邮件列表和客户那里看到的情况)。让我在这篇文章中继续介绍如何调优自动清理,以最大限度地降低性能问题的风险。在这篇文章中,我将简要解释我们为什么需要自动清理(死行、膨胀以及自动清理如何处理这些问题),然后转到这篇博文的重点 - 调优。我将介绍所有相关的配置选项,以及调优它们的一些基本规则。
注意:这是我 2016 年撰写的一篇博客文章的更新版本,更新后反映了 PostgreSQL 配置中的各种变化。除此之外,整体调整方法基本保持不变。
什么是死行?
在开始讨论调整 autovacuum 之前,我们首先需要了解什么是“死行”,以及为什么 autovacuum 执行清理实际上是需要的……
注意:有些资料说的是“死元组”而不是“死行”——这几乎只是同一事物的不同名称。术语“元组”来自关系代数(关系数据库的基础),因此更抽象,而“行”的含义通常更接近实现。但这些差异大多被忽略,并且这些术语可以互换使用。在这篇文章中,我将坚持使用“行”。
当您在 Postgres 中执行DELETE时,行(又名 row)不会立即从数据文件中删除。相反,它只是通过在标题中设置一个xmax字段来标记为已删除。对于UPDATE,在 Postgres 中,它基本上相当于DELETE + INSERT。
这是 Postgres 中 MVCC(多版本并发控制)的基本思想之一,允许不同的进程根据它们获取快照的时间查看行的不同子集。例如,在DELETE提交之前启动的SELECT进程应该看到旧行,而在DELETE提交之后启动的SELECT进程应该看到新版本(至少对于默认隔离级别READ COMMITTED而言)。
其他 MVCC 实现采用不同的方法,但 Postgres 会创建行的副本。此 MVCC 实现的缺点是,它会留下已删除的行,即使在可能看到这些版本的所有事务完成后也是如此。
因此,最后您将得到一个“标记”为已删除但仍占用数据文件空间的行。如果您更新了某一行 100 次,则该行将有 101 个副本。如果不清理,这些“死行”(实际上对任何未来事务都是不可见的)将永远保留在数据文件中,浪费磁盘空间。对于包含大量DELETEs 和/或UPDATEs 的表,死行可能很容易占据绝大多数磁盘空间。当然,这些死行仍从索引中引用,从而进一步增加了浪费的磁盘空间量。
这就是我们在 PostgreSQL 中所说的“膨胀”——与所需的最小空间量相比,表和索引都过于膨胀。当然,如果查询必须处理更多数据(即使其中 99% 的数据被立即丢弃为“不可见数据”),这也会对这些查询的性能产生影响。
VACUUM 和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扩展,允许您对表和索引执行分析,包括计算可用空间量、死行等。
调整目标
在查看实际的配置参数之前,我们先简单讨论一下高层的调优目标是什么,即改变参数时我们想要实现什么:
1. 清理死行- 保持磁盘空间量合理地较低,不要浪费不合理的磁盘空间,防止索引膨胀并保持查询快速。
2. 尽量减少清理影响- 不要过于频繁地进行清理,因为这会浪费资源(CPU、I/O 和 RAM)并可能严重损害性能。
也就是说,您需要找到正确的平衡点 - 过于频繁地运行清理可能与运行不够频繁一样糟糕。权衡主要取决于数据量、您处理的工作负载类型(尤其是DELETE/UPDATE的数量)。
大多数配置选项的默认值都非常保守。首先,默认值通常是多年前根据当时可用的资源(CPU、RAM 等)选择的。其次,我们希望默认配置可以在任何地方使用,包括 Raspberry Pi 等小型机器或小型 VPS 服务器。我们确实会不时调整默认值(正如将在本博文后面看到的那样),但即便如此,我们还是倾向于采取谨慎的小步骤。
对于较小的系统和/或处理主要读取工作负载的系统,默认配置参数工作正常,但对于大型系统,需要进行一些调整。随着数据库大小和/或写入量的增加,问题开始出现。
使用自动清理时,典型的问题是清理频率不够高,当清理终于发生时,它会严重影响查询的性能,因为它必须处理大量垃圾。在这些情况下,您应该遵循以下简单规则:
如果疼痛,那说明你锻炼的还不够频繁。(If it hurts, you're not doing it often enough.)
也就是说,您需要调整autovacuum配置,以便更频繁地进行清理,但每次执行时处理较少量的死行。
现在我们知道了我们想要实现的目标,接下来让我们看看配置参数...
注意:人们有时会遵循不同的规则 - 如果有害,就不要做 - 只需禁用autovacuum。请不要这样做,除非您真的(真的)知道自己在做什么,并且有定期执行的清理脚本(例如来自 cron)。否则,您就是在把自己逼入绝境,最终您将不得不处理严重下降的性能甚至中断,而不是性能有所下降。
阈值和比例因子
当然,您可能要调整的第一件事是清理的触发时间,它受两个参数的影响(具有以下默认值):
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仅修改它们的参数:
ALTER TABLE large_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 的好处要小得多。不值得冒过早触发清理的风险(在实际清理行之前,强制再次尝试清理)。
节流
自动清理系统内置的一个重要功能是节流。清理旨在成为一项后台维护任务,对用户查询等的影响最小。我们当然不希望清理消耗太多资源(CPU 和磁盘 I/O)以影响常规用户活动(例如使查询速度变慢)。这需要限制清理随时间可以利用的资源量。
清理过程相当简单 - 它从数据文件中读取页面(8kB 数据块),并检查页面是否需要清理。如果没有死行,则直接丢弃该页面而不做任何更改。否则,页面将被清理(删除死行),标记为“脏”,并最终写出(写入缓存,最终写入磁盘)。
让我们将“成本”分配给基本案例,表示所需的资源数量(这些值在 PG 14 中发生了变化,除非另有明确说明,否则我将使用新值):
这表示如果在共享缓冲区中找到一个页面,则需要花费 1 个令牌。如果必须从操作系统(也可能从磁盘)读取,则成本会更高一些,需要花费 2 个令牌。最后,如果页面因清理而变脏并需要写出,则计为 20。这使我们能够计算自动清理随时间推移所做的工作。
然后通过限制一次性可以完成的工作量(默认设置为 200)来实现节流,每次清理工作完成这么多工作时,它都会休眠一小会儿。休眠时间以前是 20 毫秒,但在 PG 12 中减少到了 2 毫秒。
autovacuum_vacuum_cost_delay = 2ms
autovacuum_vacuum_cost_limit = 200
让我们计算一下实际允许的工作量。延迟在 PG 12 中发生了变化,这意味着我们有三组版本,具有不同的参数值。
延迟 2 毫秒,清理可以每秒运行 500 次,每轮 200 个令牌,这意味着每秒的预算为 100000 个令牌(在 PG 12 之前的旧版本中,限制为 10000)。考虑到前面讨论的清理操作的成本,这意味着:
考虑到当前硬件的功能(假设本地存储),PG 14 之前版本的默认限制可能太低。最好增加成本限制(可能增加到 1000-2000,将吞吐量提高 5-10 倍),或者以类似的方式降低成本延迟。当然,您可以调整其他参数(每页操作成本、睡眠延迟),但我们不经常这样做 - 更改成本限制就足够了。在 PG 14 中,限制明显更高,使得默认值更为合适。
注意:常规 VACUUM 具有相同的限制机制,但默认情况下禁用(vacuum_cost_delay设置为 0)。但如果您需要运行手动 VACUUM,则可以启用限制以限制对数据库其余部分(用户查询等)的影响。
工人数量
尚未提及的一个配置选项是autovacuum_max_workers,那么它是什么呢?清理不是由单个autovacuum进程执行的 - 相反,数据库启动执行实际清理的autovacuum_max_workers进程(每个工作进程一次处理一个表)。默认配置最多允许 3 个这样的工作进程。
这绝对很有用,因为例如,您不想停止清理小表,直到清理完一个大表(由于限制,这可能需要相当长的时间)。
问题是用户认为增加工作者的数量也会增加可能发生的清理量。如果你允许6个自动清理工作程序而不是默认的3个,它将做两倍的清理工作,对吧?
不幸的是,不行。前几段所述的成本限制是全局的,由所有运行中的自动清理工作进程共享。每个工作进程只获得全局成本限制的一小部分(大约为1/autovacuum_max_workers)。因此,增加工作线程的数量只会使它们运行得更慢,而不会增加清理吞吐量。
这有点像高速公路——汽车数量加倍但速度减半,每小时到达目的地的人数大致相同。
因此,如果数据库清理速度跟不上用户活动,增加工作进程数量也无济于事。您需要先调整其他参数。
每个表的限制
实际上,当我提到成本限制是全局的并由所有自动清理工作者共享时,这并不完全正确。与比例因子和阈值类似,成本限制和延迟可以使用每个表来定义:
ALTER TABLE t SET (autovacuum_vacuum_cost_limit = 1000);
ALTER TABLE t SET (autovacuum_vacuum_cost_delay = 10);
但是,处理具有自定义限制的此类表的工作者不包括在全局成本中,而是受到独立限制(即,限制适用于该单个工作者)。
实际上,我们几乎从不使用此功能,我们建议不要使用它。它使清理行为更难预测和推理 - 多个工作者有时一起被限制,有时独立被限制,这使得情况变得非常复杂。您可能希望对后台清理使用单个全局限制。
概括
如果我必须将其总结为几个基本规则,那就是以下五条:
1. 不要禁用自动清理功能,除非您真的知道自己在做什么。
2. 在繁忙的数据库(执行大量更新和删除)上,特别是大型数据库,您可能应该降低比例因子,以便更频繁地进行清理。
3. 在合理的硬件(良好的存储、多核)上,您可能需要增加节流参数,以便清理工作能够跟上。这尤其适用于 Postgres 14 之前的旧版本。
4. 在大多数情况下,单独增加 autovacuum_max_workers 不会有帮助,因为它不会增加清理吞吐量。您将得到更多运行速度较慢的进程。
5. 您可以使用 ALTER TABLE 设置每个表的参数,但如果真的需要这样做,请三思。这会使系统更加复杂,更难检查。
原文链接:https://www.enterprisedb.com/blog/autovacuum-tuning-basics