PostgreSQL中Checkpoint和VACUUM的性能调优方法

摘要:在写入频繁复杂的系统上,Checkpoint性能调优对于获得良好性能至关重要。然而,检查点是我们经常在社区邮件列表和客户性能调整评估期间容易概念混淆和配置错误的技术点之一。
基于邮件列表和所支持客户得到的结论,PostgreSQL常见的第二个性能问题是autovacuum。因此,我们跟进关于autovacuum调校的帖子。这里将简要地解释一下必要的理论,包括死元组,膨胀以及autovacuum如何处理它,但这篇博文的主要焦点是有关autovacuum调校的配置选项,经验法则等。

Checkpoint性能调优

在写入频繁复杂的系统上,Checkpoint性能调优对于获得良好性能至关重要。然而,检查点是我们经常在社区邮件列表和客户性能调整评估期间容易概念混淆和配置错误的技术点之一。

检查点存在的意义

PostgreSQL是依赖于预写日志(WAL)的数据库之一 - 所有更改首先写入日志(更改流),然后再写入数据文件。这样做保证了数据库的持久性,因为在数据库发生崩溃的情况下,数据库可以使用WAL来执行恢复 - 从WAL读取更改并将其重新应用于数据文件。

用户只需等待WAL(刷新到磁盘),而数据文件仅在内存中修改,然后在后台刷新。虽然这可能会使写入量增加一倍,但实际上可能会提高性能。因为WAL写入本质上是顺序的,但数据文件的写入通常是随机的。

假设系统崩溃,数据库需要执行恢复。最简单的方法是从头开始并从一开始就重放整个WAL。最后,我们应该得到一个完整的(正确的)数据库。显而易见的缺点当然是需要保留和重放整个WAL。经常看到不是很大的数据库(比如几百GB)每天产生几TB的WAL。因此,想象一下在运行数据库一年时保留所有WAL所需的磁盘空间,以及恢复期间重放所需的时间。

但是,如果数据库可以保证给定WAL位置的所有更改(日志中的偏移量),所有数据文件更改到该位置都会刷新到磁盘。然后它可以在恢复期间确定此位置,并仅重放WAL的剩余部分,从而显着缩短恢复时间。并且它还可以在“已知良好”位置之前删除WAL。

这正是检查点的用途 - 保证在某个时间点之前不再需要WAL进行恢复,从而减少磁盘空间需求和恢复时间。

注意:如果您恰好是游戏玩家,您可能熟悉检查点的概念 - 您的角色通过游戏中的某个点,如果您未能击败下一个boss或掉入湖中的热熔岩,您可以从最后一点而不是从一开始就开始。让我们看看在PostgreSQL中是如何实现它的。

我们还讨论另一个极端 - 做非常频繁的检查点(比方说,每隔一秒左右)。这将允许只保留少量的WAL并且恢复将非常快(仅重放必要的非常少量的WAL数量)。但它也会将异步写入数据文件转换为同步写入文件,严重影响用户体验,例如增加COMMIT延迟,降低吞吐量。

因此,在实践过程中,您希望检查点不经常发生而不影响用户,但发生频率足以合理地限制恢复时间和磁盘空间要求。

何时触发检查点

可以触发检查点的原因大约有三到四个:

  • 直接执行CHECKPOINT命令
  • 执行需要检查点的命令(例如pg_start_backupCREATE DATABASEpg_ctl stop|restart等等)
  • 达到自上次检查点以来的配置时间
  • 自上一个检查点生成配置数量的WAL(又称“用完WAL”或“填充WAL”)

前两点无关紧要,那些操作是频次非常低,并且是手动触发的事件。本文是关于如何配置影响常规定期检查点的其他两个事件。

postgresql.conf中使用两个配置选项设置检查点的间隔时间和WAL的大小限制:

如果使用postgresql.conf的缺省值,PostgreSQL将每5分钟,或者WAL文件达到1GB之后触发一次CHECKPOINT。

注意:max_wal_size是总WAL大小的软限制,有两种可能的情况。首先,数据库将尝试不超过它的大小限制,但是允许超过配置的最大值,因此应当在WAL所在分区上保留足够的可用空间,并对可用空间进行监控;其次,它不是“每个检查点”限制 - 由于传播检查点(稍后解释),WAL配额分为2-3个检查点。因此,使用max_wal_size,数据库将在写入300MB到500MB的WAL后启动CHECKPOINT,具体取决于checkpoint_completion_target参数的配置。

和大多数postgresql.conf的其他配置的缺省值一样,默认值都设置的非常低,这样做的目的是确保即使实例运行在非常小型的硬件系统上也可以使用。

但是如何确定系统的良好价值?我们的目标不是过于频繁或过于频繁地检查,而且我们的调整是“最佳实践”包括两个步骤:

  • 选择一个相对合理的checkpoint_timeout值;
  • max_wal_size设置得足够大,使系统运行过程中很难达到;

很难说的相对合理的checkpoint_timeout值是什么,因为它取决于数据库系统发生崩溃时的恢复时间目标(RTO),即可接受的最大恢复持续时间。

但是,这有点棘手,因为checkpoint_timeout是用来限制生成WAL所需时间的,而对于恢复时间并不能起到直接决定性作用。遗憾的是,由于多种原因,无法精确估算恢复需要多长时间。WAL通常由多个进程(运行DML)生成,而恢复由单个进程执行(这种限制主要是固有的,不太可能很快消失)。这不仅会影响本地恢复,还会影响到使用流复制的备用数据库。当然,当文件系统缓存很冷时,本地恢复当然会在重启后立即发生。

但一般来说,默认值(5分钟)相当低,30分钟到1小时之间的值相当普遍。PostgreSQL 9.6甚至将最大值增加到1天(因此有些骇客认为这对某些用例来说是个好主意)。由于整页写入,低值也可能导致写入放大(我在此不再讨论)。

所以我们假设我们决定使用30分钟。

现在我们需要估计数据库在30分钟内产生多少WAL,以便我们可以将其用于max_wal_size。有几种方法可以确定生成多少WAL:

  • 使用pg_current_wal_insert_lsn()查看实际的WAL位置(基本上是文件中的偏移量),并计算每30分钟测量的位置之间的差异。
  • 启用log_checkpoints = on,然后从服务器日志中提取信息(每个完成的检查点都会有详细的统计信息,包括WAL的数量)。
  • 使用来自pg_stat_bgwriter视图的数据,该数据还包括有关检查点数量的信息(您可以将其与当前max_wal_size值的知识结合使用)。

例如,让我们使用pg_current_wal_insert_lsn测量5分钟产生WAL数量的方法。在运行pgbench的测试机上,间隔5分钟运行一次:

使用pg_wal_lsn_diff计算WAL的偏移量:

考虑到性能波动的影响,以上计算可以估计在5分钟内数据库生成了大约1GB的WAL,因此对于checkpoint_timeout = 30min,这将是大约6GB的WAL。但是如前所述,max_wal_size是2到3个检查点合计的配额,因此max_wal_size = 18GB(3 x 6GB)似乎是正确的。

其他方法使用不同的数据来源,但思路是相同的。

Spread Checkpoint

通过调整checkpoint_timeoutmax_wal_size两个参数的值,可以大致确定执行CHECKPOINT的时机,但这两个参数还不是所有决定因素。还有另一个参数叫做checkpoint_completion_target。但要调整它,你需要了解“Spread Checkpoint”的含义。

在CHECKPOINT期间,数据库需要执行以下三个基本步骤:

  • 识别共享缓冲区中的所有脏页(修改过的块);
  • 将所有缓冲区写入磁盘(或者更确切地说,写入文件系统缓存);
  • fsync所有修改过的文件到磁盘;

只有完成所有这些步骤后,才能认为检查点已完成。您可以“尽可能快地”执行这些步骤,即一次性写入所有脏缓冲区,然后在文件上调用fsync,事实上PostgreSQL在8.2之前的版本就是这样的情况,带来的负面影响是,由于填充文件系统缓存使设备饱和,导致I/O阻塞,最终影响用户会话。

为了解决这个问题,PostgreSQL 8.3引入了“Spread Checkpoint”的概念。Spread Checkpoint不是一次写入所有数据,这些写入会在很长一段时间内传播。这使操作系统有时间在后台刷新脏数据,使最终的fsync成本更低。

根据进入下一个检查点的进度限制写入。数据库知道我们需要多少时间/ WAL,直到需要另一个检查点,并且它可以计算应该已经写出多少缓冲区。但是数据库必须在最后才发出写入 - 这意味着最后一批写入仍然在文件系统缓存中,使得最终的fsync调用(在开始下一个检查点之前发出)再次成本高昂。

因此数据库需要让内核有足够的时间以便脏数据在后台刷新到磁盘 - 而页面缓存(Linux文件系统缓存)的到期通常是由时间驱动的,特别是由这个内核参数驱动:

表示数据在30秒后失效(默认情况下)。

注意:说到内核参数,调整vm.dirty_background_bytes很重要。在具有大量内存的系统上,默认值太高,允许内核在文件系统缓存中累积大量脏数据。内核经常决定一次冲洗它们,降低了Spread Checkpoint的好处。

现在,回到checkpoint_completion_target = 0.5。此配置参数表示所有写入完成后到下一个检查点的间隔。例如,假设检查点仅由checkpoint_timeout = 5min触发,数据库将限制写入,以便在2.5分钟后完成最后一次写入。然后操作系统还有2.5分钟将数据刷新到磁盘,因此5分钟后发出的fsync调用既便宜又快。

当然,考虑到期限超时仅为30秒,离开系统2.5分钟可能看起来过分。您可以增加checkpoint_completion_target,例如到0.85,这将使系统大约45秒,比它需要的30秒多一点。但不建议这样做,因为在密集写入的情况下,max_wal_size可能比5分钟之后更快地触发检查点,从而使OS低于30秒。

但是,处理写入密集型工作负载的系统不太可能以更高的checkpoint_timeouts值运行,因此默认的completion_target值肯定太低。例如,将超时设置为30分钟,它会强制数据库在前15分钟内完成所有写入操作(写入速率加倍),然后闲置15分钟。相反,您可以尝试使用此公式粗略设置checkpoint_completion_target

其中30分钟约为0.93。有时建议不要超过0.9。大多数时候可能并不能发现这两个值之间有任何显着差异。(当使用非常高的checkpoint_timeout值时,这可能会改变,现在在PostgreSQL 9.6上可以长达1天)。

Checkpoint优化点总结

  • 大多数检查点应该是基于时间的,即由checkpoint_timeout触发;
    • 性能(infrequent checkpoints,低频次检查点)与恢复所需时间(frequent checkpoints,高频次检查点)之间的妥协;
    • 15-30分钟之间的值是最常见的,但是上升到1小时也不会很差;
  • 在确定超时后,通过估计WAL的数量来选择max_wal_size的值;
  • 设置checkpoint_completion_target,以便内核有足够的(但不是必要的)时间将数据刷新到磁盘;
  • 调整vm.dirty_background_bytes以防止内核在页面缓存中累积大量脏数据;

Autovacuum性能调优

基于邮件列表和所支持客户得到的结论,PostgreSQL常见的第二个性能问题是autovacuum。因此,我们跟进关于autovacuum调校的帖子。这里将简要地解释一下必要的理论,包括死元组,膨胀以及autovacuum如何处理它,但这篇博文的主要焦点是有关autovacuum调校的配置选项,经验法则等。

死元祖

首先,让我们简要解释什么是死元祖(Dead tuples)和“膨胀”。

在PostgreSQL中执行DELETE时,不会立即从数据文件中删除行(也称为元组)。而是仅通过在标头中设置xmax字段将其标记为已删除。类似地,对于UPDATE,可以在PostgreSQL中看作DELETE + INSERT。

这是PostgreSQL MVCC背后的基本思想之一,因为它允许更大的并发性,在不同的进程之间只有最小的锁定。这个MVCC实现的缺点当然是它留下了已删除的元组,即使在所有可能看到这些版本的事务完成之后也是如此。

如果没有vacuum,那些对于任何事务实际上是不可见的死元组将永远驻留在数据文件中,浪费磁盘空间,对于具有许多DELETE和UPDATE的表,死元组可能很容易占据绝大多数磁盘空间。当然,那些死元组也将从索引中引用,进一步增加了浪费的磁盘空间。这就是我们在PostgreSQL中称之为“膨胀”的东西。当然,查询必须处理更多的数据(即使99%的数据已经呗标记为“死元祖”),查询也会越慢。

VACUUM和autovacuum

回收死元组占用空间,并使其可用于新行的最直接方法是手动运行VACUUM命令。此维护命令将扫描表并从表和索引中删除死元组,它通常不会将磁盘空间返回给操作系统,但新行可以使用这些磁盘空间。

注意:VACUUM FULL会回收空间并将其返回给操作系统,但是有许多缺点。首先,它获取表上的独占锁,阻塞所有操作(包括SELECT)。其次,它基本上创建了一个表的副本,在VACUUM FULL期间所需两倍于表的磁盘空间,因此在磁盘空间不足时它也不实用。

VACUUM的问题在于它完全是手动操作 - 只有在您决定运行它时才会发生,而不是在需要时。您可以将它放入定时任务并在所有表上每5分钟运行一次,但大多数运行的机会实际上不会清理任何内容,唯一的影响是系统上的CPU和I/O使用率更高。或者你可以每天只运行一次,在这种情况下,你可能会积累更多你不想要的死元组。

因此,解决死元祖的问题应该更依赖autovacuum:按需清理,控制浪费的空间。数据库确实知道随着时间的推移产生了多少死元组(每个事务报告它删除和更新的元组数),因此当表累积一定数量的死元组时会触发autovacuum(默认情况下是表中数据的20%,我们将看到)。因此,它将在繁忙时段更频繁地执行,而在数据库大部分空闲时更少。

autoanalyze

清除死元组并不是autovacuum的唯一任务。它还负责更新优化器在规划查询计划时使用的数据分布统计信息。您可以通过运行ANALYZE手动收集它们,但它遇到类似VACUUM的问题,人们可能过于频繁或偶尔运行它。

与vacuum的解决方案类似的,数据库可以监视表中更改的行数,并自动运行ANALYZE。

注意:对于ANALYZE,有诸多负面影响,因为虽然VACUUM的成本与死元组的数量成正比(当少数/无时,相当低),ANALYZE必须在每次执行时从头开始重建统计数据。另一方面,如果你没有经常运行ANALYZE,导致查询优化器选择了糟糕的查询计划,同样造成严重的性能问题。

为了简洁起见,我将在本文的其余部分中忽略autovacuum中的autoanalyze任务,她的配置与autovacuum完全相似,并遵循大致相同的规则。

监控

在进行任何类型的调整之前,您需要能够收集相关数据,否则无法证实需要进行怎样的调校,也无法评估配置的变更对于性能的影响。应该有一些基本的监控,从数据库中收集指标。对于vacuum,至少需要查看以下值:

  • pg_stat_all_tables.n_dead_tup:每个表中的死元组数,包括用户表和系统表
  • (n_dead_tup / n_live_tup):每个表中死/活元组的比率
  • (pg_class.relpages / pg_class.reltuples):每行“空格”

收集这些指标的总体目标是获得稳定的行为,而不会对任何指标进行突然/重大更改。

还有一个方便的pgstattuple扩展,允许您对表和索引执行分析,包括计算可用空间量,死元组等。

调优的目标

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

  • 清理死元组 - 保持磁盘空间量合理地低,不浪费不合理的磁盘空间,防止索引膨胀并加速查询。
  • 最大限度地减少vacuum的影响 - 不要过于频繁的执行autovacuum,因为它会抢占CPU,I/O和RAM资源,并可能会严重影响性能。

运行vacuum的频率太高或太低一样糟糕,需要找到合适的平衡点。在很大程度上这个平衡点取决于数据量,以及处理的工作负载类型,DELETE或UPDATE的频次占比是一个重要的参考值。

postgresql.conf中的大多数默认值都非常保守,原因有两个。首先,默认值是在几年前根据当时常见的CPU,RAM等硬件资源决定的。其次,我们希望默认配置可以在任何地方使用,包括VPS服务器这样的小型机器。对于许多部署,特别是较小的部署或处理以读为主的工作负载,默认配置参数都能够正常工作。

随着数据库大小或写入量的增加,问题开始出现。典型的问题是vacuum不会经常发生,然后当它发生时会有显著的性能波动甚至破坏,因为vacuum必须处理掉大量的垃圾。如果有这些情况发生,应该遵循这个简单的规则:调整参数以便更频繁地进行vacuum,并且每次处理较少量的死元组。

注意:一朝被蛇咬,十年怕井绳。有些管理者因为autovacuum造成过严重的波动而完全禁用autovacuum。这是非常不可取的,除非真的非常清楚自己在做什么,并部署了低峰时段定时的vacuum脚本。否则就不仅仅是某种程度上的性能降低,而是将不得不处理严重的性能问题甚至可能宕机。

阈值和比例因子

关于autovacuum参数调整的第一步是明确何时触发vacuum,这会受到两个参数的影响:

只要死元组的数量(你可以看作pg_stat_all_tables.n_dead_tup)超过threshold + pg_class.reltuples * scale_factor的值,该表将被视为需要清理,并触发autovacuum。该公式基本上表示在清理之前,表中的死元组已高达20%(50行的阈值是为了防止非常频繁地清理极小的表)。

默认的比例因子适用于中小型表,但对于非常大的表则不适用。在10GB表上,上述配置情况下,大约是2GB的死元组,而在1TB表上则有大约200GB的死元祖。根据前面提到的规则,解决方案是通过显着降低比例因子来更频繁地进行autovacuum,甚至可能是这样的:autovacuum_vacuum_scale_factor = 0.01。这将出发autovacuum的阈值减少到表的1%。另一种解决方案是仅使用阈值,完全放弃比例因子:

在生成10000个死元组后便触发清理动作。

一个问题是postgresql.conf中的这些更改会影响所有表,即整个集群,并且它可能会不利于小表的清理,包括系统表。当更频繁地清理小表时,最简单的解决方案是完全忽略问题。小表的清理成本非常低甚至可以忽略,大表的改进通常更重要,即使你忽略了小表上的效率问题,整体效果仍然是非常积极的。

但是如果您决定以更改配置的方式显着延迟小表的清理动作(例如设置scale_factor = 0和threshold = 10000),则最好使用ALTER TABLE将这些更改仅应用于特定表:

限流

配置autovacuum的一个很重要的原则是合理的使用资源。autovacuum是在后台运行的维护任务,确保autovacuum对用户查询的影响最小,它不应该消耗太多CPU和磁盘的I/O资源。

清理过程非常简单:它从数据文件中读取页面(8kB数据块),并检查是否需要清理。如果没有死元组,页面就会被丢弃而不做任何更改。否则它被清理(死元组被移除),被标记为“脏”并最终被写出来。粒度控制基于三个基本操作的因素:

也就是说,如果从shared_buffers读取页面,则计为1.如果在shared_buffers中找不到并且需要从OS读取,则计为10(它可能仍然从RAM提供,但我们不知道)。最后,如果页面被清理弄脏,则计为20.这使我们可以计算autovacuum完成的“工作成本”。

然后通过限制可以一次完成的工作量来完成限制,默认情况下设置为200,每次清理完成这项工作时,它将睡眠20ms:

那么,这实际上允许了多少工作?延迟20ms,清理可以每秒进行50轮,每轮200令牌,这意味着每秒10000令牌。对PostgreSQL来说意味着:

  • 从shared_buffers读取80MB/s(假设它没有被弄脏)
  • 从OS(可能来自磁盘)读取8MB/s
  • 写入4MB/s(由autovacuum进程弄脏的页面)

考虑到当前硬件的功能,以及vacuum的读/写主要是顺序的,这些限制太低。通常增加cost_limit参数,例如到1000或2000,吞吐量增加5倍或10倍。您当然可以调整其他vacuum_cost_page_hit/miss/dirty参数,但一般很少这样做,通过改变成本限制就足够了。

工作进程数量

还没有提到的一个配置选项是autovacuum_max_workers,那是什么呢?好吧,清理的动作不会发生在单个autovacuum进程中,数据库将在autovacuum_max_workers个进程上清理不同的数据库和表。对于大表,清理过程可能需要相当长的时间,我们不希望在清理单个大表期间停止清理小表,因此多个工作进程是非常必要的。

问题是用户假设autovacuum进程数量与可能发生的清理量成正比。如果你将autovacuum工作进程的数量增加到6,那么肯定会比默认的3个工作进程多做两倍的工作,是这样的吗?答案是:非也。上文所描述的成本限制是全局性的,由所有autovacuum工作进程共享。每个工作进程只获得总成本限制的1/autovacuum_max_workers,因此增加工作进程数量只会使它们变慢。这有点像高速公路,将汽车数量增加一倍但让它们降低一半的速度只会让每小时到达目的地的人数趋于相同。

因此,如果数据库上的清理进度无法跟上用户活动,那么增加工作进程数量并不是解决方案,除非还调整了其他参数。

表级的限流

当我说成本限制是全局性的并且由所有autovacuum工作进程共享。与比例因子和阈值类似,可以设置每个表的成本限制和延迟时间:

处理此类表的工作进程不会被包括在全局成本核算中,并且会被独立限制。

这给你带来了相当大的灵活性和力量,但不要忘记,能力越强,责任越大!在实践中,我们几乎从不使用此功能,原因有两个。首先,您通常希望在后台清理中使用统一的全局限制。其次,有多个工作进程时,有时会一起工作但有时会被独立运行,这使得监控和分析系统行为变得更加困难。

Autovacuum优化点总结

如何调整autovacuum归结为这五个基本规则:

  1. 除非你真的知道你在做什么,否则不要禁用autovacuum。讲真!
  2. 在繁忙的数据库(执行大量UPDATE和DELETE),特别是大型数据库时,您应该减小比例因子,以便更频繁地进行清理。
  3. 在合理的硬件(良好的存储,多核)上,您应该增加限制参数,以便清理进度可以跟上数据的活跃成都。
  4. 在大多数情况下,单独增加autovacuum_max_workers并不会有所帮助。只会启动更多进程,总体上清理过程变得更慢。
  5. 您可以使用ALTER TABLE设置每个表的参数,但如果确实需要,请三思而后行。它使系统更复杂,更难以检查。

我最初包括一些部分解释autovacuum不能正常工作的情况,以及如何检测它们(以及最佳解决方案),但是博客帖子已经太长了所以我会在几天内单独发布。

参考

https://www.2ndquadrant.com/en/blog/basics-of-tuning-checkpoints/
https://www.2ndquadrant.com/en/blog/autovacuum-tuning-basics/