在 Amazon RDS for PostgreSQL 上使用 PostgreSQL autovacuum - Amazon Relational Database Service
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 Amazon Web Services 服务入门

在 Amazon RDS for PostgreSQL 上使用 PostgreSQL autovacuum

我们强烈建议您使用 Autovacuum 功能来保持 PostgreSQL 数据库实例正常运行。Autovacuum 自动启动 VACUUM 和 ANALYZE 命令。它会检查包含大量插入的、更新的或删除的元组的表。进行此检查后,它会通过从 PostgreSQL 数据库中删除过时的数据或元组来回收存储。

在使用任何默认 PostgreSQL 数据库参数组创建的 Amazon RDS for PostgreSQL 数据库实例上,会默认启用 autovacuum。这些参数组包括 default.postgres10default.postgres11 等等。所有 PostgreSQL 数据库默认参数组都有设置为 1 的参数 rds.adaptive_autovacuum,从而激活该功能。默认情况下,还会设置与 Autovacuum 功能关联的其他配置参数。这些默认值是通用值,因此可以针对特定工作负载优化与 Autovacuum 功能关联的某些参数。

在下文中,您可以了解有关 autovacuum 功能以及如何为 RDS for PostgreSQL 数据库实例优化其部分参数的更多信息。有关高级信息,请参阅 使用 PostgreSQL 的最佳实践

为 Autovacuum 分配内存

影响 autovacuum 性能的最重要参数之一是 maintenance_work_mem 参数。该参数确定您为 Autovacuum 分配多少内存以用于扫描数据库表和保留将执行 vacuum 操作的所有行 ID。如果将 maintenance_work_mem 参数的值设得太小,则 vacuum 过程可能必须扫描表多次才能完成其工作。此类多次扫描可能会对性能产生负面影响。

在执行计算以确定 maintenance_work_mem 参数值时,需记住以下两点:

  • 该参数的默认单位为 KB。

  • maintenance_work_mem 参数可与 autovacuum_max_workers 参数结合使用。如果您有多个小型表,请分配更多的 autovacuum_max_workers 和更少的 maintenance_work_mem。如果您拥有大型表(假设表的大小大于 100GB),则请分配更多内存和更少工作进程。您需要分配有足够的内存才能对最大的表成功完成操作。每个 autovacuum_max_workers 均可使用您分配的内存。因此,请确保工件进程和内存的组合等于要分配的总内存。

一般来说,对于大型主机,将 maintenance_work_mem 参数设置为一个介于 1GB 和 2GB 之间(介于 1048576KB 和 2097152KB 之间)的值。对于特大型主机,将该参数设置为一个介于 2GB 和 4GB 之间(介于 2097152KB 和 4194304KB 之间)的值。为该参数设置的值取决于工作负载。Amazon RDS 已将该参数的默认值更新为按以下方式计算的 KB 值。

GREATEST({DBInstanceClassMemory/63963136*1024},65536).

减少事务 ID 重叠的可能性

在一些情况下,与 Autovacuum 相关的参数组设置可能不够积极,无法阻止事务 ID 重叠。为解决此问题,RDS for PostgreSQL 提供了自动调整 Autovacuum 参数值的机制。适应性 Autovacuum 参数优化 是 RDS for PostgreSQL 的一项功能。在 PostgreSQL 文档中找到了 TransactionID 重叠的详细说明。

默认情况下,为动态参数 rds.adaptive_autovacuum 设置为 ON 的 RDS for PostgreSQL 实例启用适应性 autovacuum 参数优化。强烈建议您保持启用此选项。不过,要关闭适应性 Autovacuum 参数优化,请将 rds.adaptive_autovacuum 参数设置为 0 或 OFF。

即使在 Amazon RDS 优化 Autovacuum 参数时,仍可能出现事务 ID 重叠。鼓励您为事务 ID 重叠实施 Amazon CloudWatch 警报。有关更多信息,请参阅 Amazon 数据库博客上的贴子在 RDS for PostgreSQL 中为事务 ID 重叠实施预警系统

启用适应性 autovacuum 参数优化后,当 CloudWatch 指标 MaximumUsedTransactionIDs 达到 autovacuum_freeze_max_age 参数值或 500000000 中的较大值时,Amazon RDS 将开始调整 autovacuum 参数。

如果表继续倾向于事务 ID 重叠,则 Amazon RDS 将继续调整 autovacuum 的参数。其中每次调整都会将更多资源专用于 Autovacuum 以避免重叠。Amazon RDS 更新以下与 Autovacuum 相关的参数:

仅当新值使 Autovacuum 更积极时,RDS 才会修改这些参数。在数据库实例上的内存中修改参数。不会更改参数组中的值。要查看当前内存中的设置,请使用 PostgreSQL SHOW SQL 命令。

当 Amazon RDS 修改其中任何 autovacuum 参数时,它会为受影响的数据库实例生成事件。此事件在 Amazon Web Services Management Console 上和通过 Amazon RDS API 显示。在 MaximumUsedTransactionIDs CloudWatch 指标返回的值低于阈值后,Amazon RDS 会将内存中与 autovacuum 相关的参数重置回参数组中指定的值。然后,它会生成另一个与此更改对应的事件。

确定数据库中的表是否需要 vacuum 操作

您可以使用以下查询显示数据库中未执行 vacuum 操作的事务的数目。数据库的 datfrozenxid 行的 pg_database 列是显示在该数据库中的正常事务 ID 的下限。此列是数据库中每个表的 relfrozenxid 值的最小值。

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) desc limit 20;

例如,运行上述查询的结果可能如下所示。

datname | age mydb | 1771757888 template0 | 1721757888 template1 | 1721757888 rdsadmin | 1694008527 postgres | 1693881061 (5 rows)

当数据库的期限达到 20 亿个事务 ID 时,事务 ID (XID) 重叠将出现,并且数据库将变成只读状态。您可以使用此查询来生成指标,并且一天可运行几次。默认情况下,将设置 Autovacuum 以确保事务期限不超过 200000000 ()。autovacuum_freeze_max_age

示例监控策略可能类似于:

  • autovacuum_freeze_max_age 值设置为 2 亿个事务。

  • 如果表达到 5 亿个未执行 vacuum 操作的事务,则这会触发低严重性警报。这不是一个不合理的值,但它可能指示 Autovacuum 未保持同步。

  • 如果表期限为 10 亿,这应被视为要采取操作的警报。通常,您出于性能原因,需要使期限更接近 autovacuum_freeze_max_age。建议您使用以下建议进行调查。

  • 如果表达到 15 亿个未执行 vacuum 操作的事务,则这会触发高严重性警报。根据数据库使用事务 ID 的频率,此警报将指示系统运行 Autovacuum 的时间不多了。在这种情况下,建议您立即解决此问题。

如果表持续违反这些阈值,请进一步修改 autovacuum 参数。默认情况下,手动使用 VACUUM(已禁用基于成本的延迟)比使用默认的 Autovacuum 更积极,但对整个系统来说也更具侵入性。

我们建议执行下列操作:

确定哪些表当前符合 Autovacuum 条件

通常,它是需要执行 vacuum 操作的一个或两个表。其 relfrozenxid 值大于 autovacuum_freeze_max_age 中的事务数的表始终是 Autovacuum 的目标。否则,如果元组数因上一个 VACUUM 超出 vacuum 阈值而变得过时,则对表执行 vacuum 操作。

Autovacuum 阈值的定义如下:

Vacuum-threshold = vacuum-base-threshold + vacuum-scale-factor * number-of-tuples

在连接到数据库时,运行以下查询可查看 autovacuum 认为有资格执行 vacuum 操作的表的列表。

WITH vbt AS (SELECT setting AS autovacuum_vacuum_threshold FROM pg_settings WHERE name = 'autovacuum_vacuum_threshold'), vsf AS (SELECT setting AS autovacuum_vacuum_scale_factor FROM pg_settings WHERE name = 'autovacuum_vacuum_scale_factor'), fma AS (SELECT setting AS autovacuum_freeze_max_age FROM pg_settings WHERE name = 'autovacuum_freeze_max_age'), sto AS (select opt_oid, split_part(setting, '=', 1) as param, split_part(setting, '=', 2) as value from (select oid opt_oid, unnest(reloptions) setting from pg_class) opt) SELECT '"'||ns.nspname||'"."'||c.relname||'"' as relation, pg_size_pretty(pg_table_size(c.oid)) as table_size, age(relfrozenxid) as xid_age, coalesce(cfma.value::float, autovacuum_freeze_max_age::float) autovacuum_freeze_max_age, (coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples) AS autovacuum_vacuum_tuples, n_dead_tup as dead_tuples FROM pg_class c join pg_namespace ns on ns.oid = c.relnamespace join pg_stat_all_tables stat on stat.relid = c.oid join vbt on (1=1) join vsf on (1=1) join fma on (1=1) left join sto cvbt on cvbt.param = 'autovacuum_vacuum_threshold' and c.oid = cvbt.opt_oid left join sto cvsf on cvsf.param = 'autovacuum_vacuum_scale_factor' and c.oid = cvsf.opt_oid left join sto cfma on cfma.param = 'autovacuum_freeze_max_age' and c.oid = cfma.opt_oid WHERE c.relkind = 'r' and nspname <> 'pg_catalog' AND (age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float) OR coalesce(cvbt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples <= n_dead_tup -- or 1 = 1) ORDER BY age(relfrozenxid) DESC LIMIT 50;

确定 Autovacuum 当前是否正在运行以及运行时长

如果需要手动对表执行 vacuum 操作,确保确定 autovacuum 当前是否正在运行。如果它当前正在运行,则您可能需要调整参数以使其更高效地运行,或者暂时禁用 autovacuum 以便手动运行 VACUUM。

使用以下查询来确定 Autovacuum 是否正在运行、它已运行的时长以及它是否正在等待其他会话。

SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY xact_start;

在运行查询后,您应看到类似以下内容的输出。

datname | usename | pid | state | wait_event | xact_runtime | query --------+----------+-------+--------+------------+-------------------------+-------------------------------------------------------------------------------------------------------- mydb | rdsadmin | 16473 | active | | 33 days 16:32:11.600656 | autovacuum: VACUUM ANALYZE public.mytable1 (to prevent wraparound) mydb | rdsadmin | 22553 | active | | 14 days 09:15:34.073141 | autovacuum: VACUUM ANALYZE public.mytable2 (to prevent wraparound) mydb | rdsadmin | 41909 | active | | 3 days 02:43:54.203349 | autovacuum: VACUUM ANALYZE public.mytable3 mydb | rdsadmin | 618 | active | | 00:00:00 | SELECT datname, usename, pid, state, wait_event, current_timestamp - xact_start AS xact_runtime, query+ | | | | | | FROM pg_stat_activity + | | | | | | WHERE query like '%VACUUM%' + | | | | | | ORDER BY xact_start; +

有多个问题可能会导致长时间运行 Autovacuum 会话(即,多天)。最常见的问题是,对于表的大小或更新速率来说,设置的 maintenance_work_mem 参数值太小。

建议您使用以下公式来设置 maintenance_work_mem 参数值。

GREATEST({DBInstanceClassMemory/63963136*1024},65536)

短时间运行的 Autovacuum 会话还可以指示以下问题:

  • 它可以指示,对于工作负载而言,autovacuum_max_workers 不足。在这种情况下,您将需要指示工作线程数。

  • 它可以指示存在索引损坏(autovacuum 将发生崩溃并在同一关系上重新启动,但毫无进展)。在这种情况下,运行手动 vacuum freeze verbose table 以查看准确原因。

执行手动 vacuum 冻结

您可能需要对已具有正在运行的 vacuum 进程的表执行手动 vacuum 操作。如果您已使用接近 20 亿个事务(或高于您监控的任何阈值)的期限标识表,则这会很有用。

以下步骤是指导原则,此过程存在几种变化。例如,在测试期间,假设您发现设定的 maintenance_work_mem 参数值过小,并且您需要立即对表采取措施。不过,可能您不希望此时恢复实例。通过使用前几节中的查询,您可以确定哪个表存在问题,并找到长时间运行的 Autovacuum 会话。您知道您需要更改 maintenance_work_mem 参数设置,但您还需要立即采取行动,对有问题的表执行 vacuum 操作。以下过程说明了在此情况下应采取的措施。

手动执行 vacuum 冻结

  1. 打开针对包含要执行 vacuum 操作的表的数据库的两个会话。对于第二个会话,使用“screen”或其他维护会话的实用工具 (如果您的连接已中断)。

  2. 在第一个会话中,获取正在表上运行的 autovacuum 会话的进程 ID (PID)。

    运行以下查询可获取 Autovacuum 会话的 PID。

    SELECT datname, usename, pid, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) LIKE '%VACUUM%' ORDER BY xact_start;
  3. 在第二个会话中,计算该操作所需的内存量。在此示例中,我们确定自己最多可以为该操作使用 2GB 的内存,因此,我们将当前会话的 maintenance_work_mem 设置为 2 GB。

    SET maintenance_work_mem='2 GB'; SET
  4. 在第二个会话中,为表发出 vacuum freeze verbose 命令。详细设置很有用,因为虽然 PostgreSQL 中当前没有进度报告,但您可以查看活动。

    \timing on Timing is on. vacuum freeze verbose pgbench_branches;
    INFO: vacuuming "public.pgbench_branches" INFO: index "pgbench_branches_pkey" now contains 50 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "pgbench_branches_test_index" now contains 50 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pgbench_branches": found 0 removable, 50 nonremovable row versions in 43 out of 43 pages DETAIL: 0 dead row versions cannot be removed yet. There were 9347 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM Time: 2.765 ms
  5. 在第一个会话中,如果 autovacuum 阻止 vacuum 会话,您将在 pg_stat_activity 中看到 vacuum 会话的等待为“T”。在此情况下,您需要终止 autovacuum 过程,如下所示。

    SELECT pg_terminate_backend('the_pid');

    此时,您的会话将开始。由于此表可能位于其工作列表中的最高位置,因此,了解 Autovacuum 将立即重新启动很重要。

  6. 在第二个会话中启动您的 vacuum freeze verbose 命令,然后终止第一个会话中的 autovacuum 过程。

在 Autovacuum 正在运行时重新为表建立索引

如果索引已损坏,Autovacuum 将继续处理表并失败。在此情况下,如果您尝试执行手动 vacuum 操作,您将收到一条与以下内容类似的错误消息。

postgres=> vacuum freeze pgbench_branches; ERROR: index "pgbench_branches_test_index" contains unexpected zero page at block 30521 HINT: Please REINDEX it.

当索引损坏并且 autovacuum 尝试在表上运行时,您将处理已经正在运行的 autovacuum 会话。在您发出 REINDEX 命令时,将删除表上的排他锁。将阻止写入操作以及使用该特定索引的读取操作。

在对表运行 Autovacuum 时重新为表建立索引

  1. 打开针对包含要执行 vacuum 操作的表的数据库的两个会话。对于第二个会话,使用“screen”或其他维护会话的实用工具 (如果您的连接已中断)。

  2. 在第一个会话中,获取正在表上运行的 Autovacuum 会话的 PID。

    运行以下查询可获取 Autovacuum 会话的 PID。

    SELECT datname, usename, pid, current_timestamp - xact_start AS xact_runtime, query FROM pg_stat_activity WHERE upper(query) like '%VACUUM%' ORDER BY xact_start;
  3. 在第二个会话中,发出 reindex 命令.

    \timing on Timing is on. reindex index pgbench_branches_test_index; REINDEX Time: 9.966 ms
  4. 在第一个会话中,如果 autovacuum 阻止该过程,您将在 pg_stat_activity 中看到 vacuum 会话的等待为“T”。在此情况下,您将终止 autovacuum 过程。

    SELECT pg_terminate_backend('the_pid');

    此时,您的会话将开始。由于此表可能位于其工作列表中的最高位置,因此,了解 Autovacuum 将立即重新启动很重要。

  5. 在第二个会话中启动您的命令,然后终止第一个会话中的 autovacuum 过程。

其他影响 Autovacuum 的参数

以下查询将显示直接影响 Autovacuum 及其行为的一些参数的值。PostgreSQL 文档中完整介绍了 Autovacuum 参数

SELECT name, setting, unit, short_desc FROM pg_settings WHERE name IN ( 'autovacuum_max_workers', 'autovacuum_analyze_scale_factor', 'autovacuum_naptime', 'autovacuum_analyze_threshold', 'autovacuum_analyze_scale_factor', 'autovacuum_vacuum_threshold', 'autovacuum_vacuum_scale_factor', 'autovacuum_vacuum_threshold', 'autovacuum_vacuum_cost_delay', 'autovacuum_vacuum_cost_limit', 'vacuum_cost_limit', 'autovacuum_freeze_max_age', 'maintenance_work_mem', 'vacuum_freeze_min_age');

所有这些参数都会影响 Autovacuum,其中一些最重要的参数为:

设置表级别 Autovacuum 参数

您可以在表级别设置与 autovacuum 相关的存储参数,这可能优于更改整个数据库的行为。对于大型表,您可能需要设置主动设置,并且可能不希望 Autovacuum 对所有表的行为都相同。

以下查询将显示哪些表当前拥有表级别选项。

SELECT relname, reloptions FROM pg_class WHERE reloptions IS NOT null;

例如,对于比您的其他表大得多的表,这可能会很有用。假设您具有一个 300GB 表和另外 30 个小于 1GB 的表。在这种情况下,可以为大型表设置一些特定的参数,这样便无需更改整个系统的行为。

ALTER TABLE mytable set (autovacuum_vacuum_cost_delay=0);

这样便可通过使用系统中的更多资源来禁用此表的基于成本的 autovacuum 延迟。通常,每次达到 autovacuum_cost_limit 时,autovacuum 会暂停 autovacuum_vacuum_cost_delay。有关更多详细信息,请参阅 PostgreSQL 文档中有关基于成本的 vacuum 操作的内容。

记录 autovacuum 和 vacuum 活动

根据 rds.force_autovacuum_logging_level 参数中指定的级别,有关 autovacuum 活动的信息将发送到 postgresql.log。以下是此参数允许的值以及该值为默认设置的 PostgreSQL 版本:

  • disabled(PostgreSQL 10、PostgreSQL 9.6)

  • debug5, debug4, debug3, debug2, debug1

  • info(PostgreSQL 12、PostgreSQL 11)

  • notice

  • warning(PostgreSQL 14、PostgreSQL 13)

  • error、日志、fatalpanic

rds.force_autovacuum_logging_levellog_autovacuum_min_duration 参数结合使用。log_autovacuum_min_duration 参数的值为阈值(以毫秒为单位),超出该值后将记录 autovacuum 操作。设置为 -1 不会记录任何内容,而设置 0 将记录所有操作。和 rds.force_autovacuum_logging_level 一样,log_autovacuum_min_duration 的默认值取决于版本,如下所示:

  • 10000 ms - PostgreSQL 14、PostgreSQL 13、PostgreSQL 12 和 PostgreSQL 11

  • (empty) - PostgreSQL 10 和 PostgreSQL 9.6 没有默认值

建议您将 rds.force_autovacuum_logging_level 设置为 LOG。我们还建议您将 log_autovacuum_min_duration 设置为 1000 到 5000 之间的值。设置为 5000 的记录活动耗时将超过 5000 毫秒。如果由于冲突锁定或并行删除关系跳过了 autovacuum 操作,则并非 -1 的任何其他设置也会记录消息。有关更多信息,请参阅 PostgreSQL 文档中的 Automatic Vacuuming

要对问题进行故障排除,可以将 rds.force_autovacuum_logging_level 参数更改为调试级别之一,从 debug1 到最高 debug5 以获取最详细的信息。我们建议您在短时间内使用调试设置,并且仅用于故障排除目的。要了解更多信息,请参阅 PostgreSQL 文档中的何时记录

注意

PostgreSQL 允许 rds_superuser 账户查看 pg_stat_activity 中的 Autovacuum 会话。例如,您可识别并结束阻止命令运行或运行速度慢于手动发出的 vacuum 命令的 Autovacuum 会话。