PostgreSQL 清理死亡元祖 dead tuples 详解

1.Dead tuples 

  在Postgresql做delete操作时，数据集(也叫做元组 (tuples))是没有立即从数据文件中移除的，仅仅是通过在行头部设置xmax做一个删除标记。update操作也是一样的，在postgresql中可以看作是先delete再insert;

  这是Postgresql MVCC的基本思想之一，因为它允许在不同进程之间只进行最小的锁定就可以实现更大的并发性。这个MVCC实现的缺点当然是它会留下被标记删除的 元组（ dead tuples），即使在这些版本的所有事务完成之后。

  如果不清理掉那些dead tuples（对任何事务都是不可见的）将会永远留在数据文件中，浪费磁盘空间，对于表来说，有过多的删除和更新，dead tuples很容易占绝大部分磁盘空间。而且dead tuples也会在索引中存在，更加加重磁盘空间的浪费。这是在PostgreSQL中常说的膨胀（bloat）。自然的，需要处理的数据查询越多，查询的速度就越慢。

2.VACUUM与autovacuum

  回收dead tuples 占用的空间，并使其对新行可用，最直接的方法是手动运行vacuum命令。这个维护命令将扫描这个表，并且从表和索引中删除dead tuples ---它通常不会将磁盘空间返回给操作系统，但它将使它对新行可用。

  注意： v acuum full 会回收空间并返回给操作系统，但是它有很多缺点。首先，它会产生独立锁，阻塞所有操作(包括select).其次，它实际上创建了一个表的副本，使所需的磁盘空间加倍，因此当系统可用磁盘空间很少时，就不太实用。

  vacuum的问题在于它完全是手动操作--只有当你决定运行时它才会发生，而不是在需要的时候。你可以将其放入计划任务中，并在所有表上每5分钟运行一次，但是大多数运行并不会真正清理任何东西，唯一的影响是系统上更高的CPU和I/O使用率。或者可以一天只在晚上运行一次，在这种情况下，可能会积累更多的dead tuples。

  这就引出了 autovacuum的主要目的：根据需要进行清理，以控制浪费的空间数量。数据库知道在一段时间内生成了多少个死元组(每个事务报告它删除和更新的元组的数量)，因此当表累积一定数量的dead tuples 时(默认情况下，这是表的20%)可以触发清理。因此，它将在繁忙期间更频繁地执行，而在数据库大部分处于空闲状态时很少执行。

3.autoanalyze

清除dead tuples并不是 autovacuum的唯一任务。它还负责更新数据分布统计信息，优化器在规划查询时使用这些统计信息。您可以通过运行ANALYZE手工收集这些数据，但是它也有与VACUUM类似的问题——您可能经常运行它，也可能不经常运行。解决方法也是相似的，数据库能够监视表中改变了多少行，然后自动的运行analyze。

  注意：对于analyze来说，负面影响要更糟一些，虽然vacuum的执行成本与dead tuples的数量成正比(dead tuples很少的时候成本相当低)，但是analyze必须在每次执行时从头重新构建统计数据。另一方面，如果你不经常运行它，选择糟糕的执行计划带来的代价可能同样严重。

4.监控

  需要一些基本的监控，从数据库中收集指标，清除dead tuples：

# 每个表dead tuples的数量（包括用户表和系统表）

pg_stat_all_tables.n_dead_tup# dead/live tuples在每个表中的比率(n_dead_tup / n_live_tup)# 每一行的空间(pg_class.relpages / pg_class.reltuples) 

这是一个非常方便的扩展： pgstattuple ，允许对表和索引执行analyze，包括计算空闲空间的数量、dead tuples等。

5.调优目标

• 清除dead tuples：保持磁盘空间合理的较低的占用，不浪费磁盘空间，防止索引膨胀并且保持快速查询。
• 让清除的影响减到最小：不要太经常的执行清除，他将会浪费CPU、I/O、内存资源并且降低性能。

  需要找到一个正确的平衡，因为执行过多或者执行过少都会有坏的影响。平衡在很大程度上取决于你管理的数据量、业务上的负载类型(删除/更新的数量)。

  postgresql.conf中的许多默认值是相当保守的，有两个原因：第一，默认值是几年前根据当时通用的配置(CPU、RAM、…)确定的。第二，我们希望默认配置可以在任何地方工作，包括像Raspberry Pi这样的小型机器或小型VPS服务器。对于许多部署(特别是较小的部署 和/或 处理以读为主的工作负载)，默认配置参数将正常工作。

  随着数据库大小 和/或 写入量的增加，问题开始出现。典型的问题是清理工作并不经常发生或者当清理工作发生时并且它必须要处理大量垃圾时，它会严重影响性能。如果这些情况下，你应该遵循这个简单的规则:

  如果它对性能的影响很大，说明清理工作次数不足。那就需要调整参数让清理工作做得更频繁，减少每次处理dead tuples的数量。

注意：人们有时会遵循不同的规则——如果对性能的影响很大，就不要去做，并且完全关闭autovacuum。请不要这样做，除非你真的(真的真的)知道你在做什么，并且有定期的到位的清理脚本。否则，您将陷入困境，您将不得不处理严重的性能下降，甚至可能出现停机。

6.阈值与比例因子

  首先要调整的是清理工作何时被触发，这受到两个参数的影响，以下是默认值:

• autovacuum_vacuum_threshold = 50
• autovacuum_vacuum_scale_factor = 0.2

当dead tuples的数量(可以从aspg_stat_all_tables.n_dead_tup看到)

超过

threshold + pg_class.reltuples * scale_factor时，表就会被认为需要清理，就会触发清理工作。

该公式表达的意思主要是：表中的dead tuples达到20%将会被清理，（50行的阈值是为了预防对微小的表非常频繁的清理）。

对于中小型表，默认的比例因子可以很好的工作在中小型表上，但是对于非常大的表就不那么好了——在10GB的表中，大约是2GB的dead tuples，而在1TB的表中，大约是200GB。

这是一个积累大量dead tuples的例子，并且一次处理所有的元组，这会很影响性能。根据前面提到的规则，解决方案是通过显著降低比例因子来更频繁地执行，甚至可能是这样:

• autovacuum_vacuum_scale_factor = 0.01

它将把上限降低到只有表的1%。另一种解决方案是完全放弃比例因子，只使用阈值。

• autovacuum_vacuum_scale_factor = 0
• autovacuum_vacuum_threshold = 10000

这将在生成一万个dead tuples之后触发清理。

有一个问题是这些改变在postgresql.conf中，所以是影响所有的表（实际上是整个数据库集簇），我们可能不希望影响小表和系统表的清理。当小表被更频繁地清理时，最简单的解决方案就是完全忽略这个问题。清理小表的成本相当低，而对大表的改进通常非常显著，即使忽略了小表上的清理成本，总体效果仍然非常积极。

但是，如果您决定以一种显著延迟小表清理的方式更改配置(例如设置scale_factor=0和threshold=10000)，那么最好只使用ALTER TABLE将这些更改应用于特定的表:

ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0);ALTER TABLE t SET (autovacuum_vacuum_threshold = 10000);

尽量保持配置简单，并覆盖尽可能少的表的参数。在你的内部文档中包含这一点也是一个好主意，包括对特定值的推理。

7.限流

  Autovacuum一个相当好的特色是限流。清理是在后台运行的维护任务，对用户查询的影响最小。换句话说，它不应该消耗太多的资源(CPU和磁盘I/O)，这正是限流内置到autovacuum中的目的。清理过程相当简单，它从数据文件中读取页面(8kB的数据块)，并检查是否需要清理。如果没有dead tuples，页面将被简单地丢弃，而不进行任何更改。否则，它将被清理(删除dead tuples)，被标记为“脏的”，并最终被写出来。成本计算是基于以下三个基本操作的成本定义的:

• vacuum_cost_page_hit = 1
• vacuum_cost_page_miss = 10
• vacuum_cost_page_dirty = 20

也就是说，如果从shared_buffers读取页面，则计数为1。如果shared_buffers中没有找到它而需要从操作系统中读取，计数为10(它可能仍然由RAM提供，但我们不知道)。最后，如果页面被清理弄脏了，则计数为20。这让我们可以计算autovacuum的“工作成本”。

通过限制一次性完成的工作量(默认设置为200)来实现限流，每次清理工作完成这么多工作(计数达到autovacuum_vacuum_cost_limit )，它就会休眠20毫秒:

• autovacuum_vacuum_cost_delay = 20ms
• autovacuum_vacuum_cost_limit = 200

那么，实际上允许做多少工作呢?使用20ms延迟，清理工作每秒可以执行50轮，每轮200意味着每秒10000。这意味着:

• 每秒从share_buffer读80M（假设不脏）。
• 每秒从操作系统读8M（也可能来自磁盘）。
• 每秒写4M（被autovacuum进程弄脏的页）。

考虑到当前硬件的能力，读/写大多是连续的，这些限制太低了。

我们通常做的是将cost_limit参数增加到1000(或2000)，这将使吞吐量增加5倍(或10倍)。当然，也可以调整其他参数(每页操作的成本、睡眠延迟)，但是我们很少这样做——更改成本限制就足够了。

8.进程数量

  还有一个配置选项还没有提到，那就是autovacum_max_workers，那是关于什么的呢?当然，清理不会在一个单独的autovacuum进程中发生，但是数据库可以启动到autovacum_max_workers个进程，这些进程实际上是在清理不同的数据库/表。这很有用，因为例如，在清理单个大表完成之前，您不希望停止清理小表(这可能需要相当长的时间，因为进行了节流)。

  这里有一个问题：用户认为进程的数量与可能发生的清理量成正比。如果你把autovacuum进程的数量增加到6个，它肯定会比默认的3个进程多做两倍的工作，这样对吗?

  嗯,没有。前几段描述的成本限制是全局级别的，由所有的autovacuum进程共同承担。每个进程只会得到总成本限制的1/ autovacum_max_workers，因此增加进程数量只会让他们走得更慢。

  这有点像高速公路，把汽车的数量增加一倍，但让它们以一半的速度行驶，每小时到达目的地的人数只会大致相同。

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

9.针对于个别表的限流

  实际上，说成本限制是全局级别的，并且由所有的autovacuum进程共同承担时，并不十分准确。与比例因子和阈值类似，可以设置每个表的成本限制和延迟:

ALTER TABLE t SET (autovacuum_vacuum_cost_limit = 1000);ALTER TABLE t SET (autovacuum_vacuum_cost_delay = 10);

然后，处理此类表的进程不包括在全局成本计算中，而是独立地进行控制。

  这给了你相当多的灵活性和权力，但别忘了——权力越大，责任越大!在实践中，我们几乎从不使用这些特征，主要原因有两个：第一，您通常希望在后台清理中使用单个全局限制。其次，让多个进程一起工作，有时候会相互限流，有时单独工作，使得监控和分析系统的行为变得更加困难。

10.总结

（1）触发情况

• 当update,delete的tuples数量超过：autovacuum_vacuum_scale_factor * table_size + autovacuum_vacuum_threshold
• 指定表上事务的最大年龄配置参数autovacuum_freeze_max_age，默认为2亿，达到这个阀值将触发 autovacuum进程，从而避免 wraparound。

（2）基本的规则

• 不要禁用autovacuum，除非你真的知道你自己在做什么。很严肃。
• 在忙碌的数据库上（做大量的更新和删除），特别是大的数据库，应该减小比例因子，让清理发生的更频繁。
• 在合理的硬件上（好的存储，多个核心），应该增加限流参数让清除工作不落后。
• 在大部分情况下，单独增加autovacuum_max_worker并没有真实的帮助。而且会让更多的进程变慢。
• 可以使用alter table设置单个表的参数，但请三思而后行。他会让你的系统变得更复杂也更难检查。

（3）建议

• autovacuum_max_workers的建议值为CPU核数/3。CPU资源充足，I/O性能较好时，可以适当加大。
• 对于更新频繁的交易系统，如果系统资源充足，可以缩小autovacuum_vacuum_scale_factor 与 autovacuum_vacuum_threshold，让vacuum清理频繁