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

在 Amazon RDS 上使用 PostgreSQL autovacuum

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

默认情况下,为所有新的 Amazon RDS for PostgreSQL 数据库实例启用 Autovacuum,并且默认情况下将正确设置相关的 autovacuum 配置参数。由于默认值是某个通用值,因此,您可以从针对特定工作负载来优化参数中受益。以下部分可以帮助您执行所需的 autovacuum 优化。

为 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 重叠。为解决此问题,Amazon RDS for PostgreSQL 提供了自动调整 autovacuum 参数值的机制。适应性 autovacuum 参数优化 是 RDS for PostgreSQL 的一项功能。在 PostgreSQL 文档中找到了 TransactionID 重叠的详细说明。

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

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

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

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

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

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

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

您可以使用以下查询显示数据库中未执行 vacuum 操作的事务的数目。数据库的 pg_database 行的 datfrozenxid 列是显示在该数据库中的正常事务 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 是否正在运行、它已运行的时长以及它是否正在等待其他会话。

如果您使用的是 RDS for PostgreSQL 9.6+ 或更高版本,请使用以下查询:

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; +

如果您使用的 Amazon RDS for PostgreSQL 版本低于 9.6,请使用以下查询。

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

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

datname | usename | pid | waiting | xact_runtime | query --------+----------+-------+---------+-------------------------+---------------------------------------------------------------------------------------------- mydb | rdsadmin | 16473 | f | 33 days 16:32:11.600656 | autovacuum: VACUUM ANALYZE public.mytable1 (to prevent wraparound) mydb | rdsadmin | 22553 | f | 14 days 09:15:34.073141 | autovacuum: VACUUM ANALYZE public.mytable2 (to prevent wraparound) mydb | rdsadmin | 41909 | f | 3 days 02:43:54.203349 | autovacuum: VACUUM ANALYZE public.mytable3 mydb | rdsadmin | 618 | f | 00:00:00 | SELECT datname, usename, pid, waiting, 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 冻结详细 ___table___ 以查看准确原因。

执行手动 vacuum 冻结

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

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

手动执行 vacuum 冻结

  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. 在第二个会话中,计算该操作所需的内存量。在此示例中,我们确定自己最多可以为该操作使用 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 被阻止,您将在 pg_stat_activity 中看到 vacuum 会话的等待为“T”。在此情况下,您需要终止 autovacuum 过程,如下所示。

    SELECT pg_terminate_backend('the_pid');
  6. 此时,您的会话将开始。由于此表可能位于其工作列表中的最高位置,因此,了解 autovacuum 将立即重新启动很重要。在第 2 个会话中启动您的 vacuum freeze verbose 命令,然后终止第 1 个会话中的 autovacuum 过程。

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

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

mydb=# 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');
  5. 此时,您的会话将开始。由于此表可能位于其工作列表中的最高位置,因此,了解 autovacuum 将立即重新启动很重要。在第 2 个会话中启动您的命令,然后终止第 1 个会话中的 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 日志记录

默认情况下,postgresql.log 不包含有关 autovacuum 过程的信息。通过设置 rds.force_autovacuum_logging_level 参数,您可以在 autovacuum 工作线程操作所生成的 PostgreSQL 错误日志中查看输出。允许的值包括 disabled, debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal,panic。默认值为 disabled,因为其他允许的值会向日志添加大量信息。

建议您将 rds.force_autovacuum_logging_level 参数的值设置为 warning,并将 log_autovacuum_min_duration 参数的值设置为 1,000 到 5,000 毫秒。如果您将此值设置为 5000,则 Amazon RDS 会将所需时间多于 5 秒的任何活动写入日志。在应用程序锁定导致 autovacuum 故意跳过表时,它还会显示“vacuum skipped (已跳过 vacuum)”消息。如果您解决问题并且需要更多详细信息,您可以使用其他日志记录级别值,例如 debug1debug3。由于这些设置会生成写入到错误日志文件中的非常详细的内容,因此,使用这些调试参数一小段时间。有关这些调试设置的更多信息,请参阅 PostgreSQL 文档

注意

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