Azure Database for PostgreSQL 中的 Autovacuum 优化

** 本文概述了Azure Database for PostgreSQL的 autovacuum 功能，并提供监控数据库膨胀和 autovacuum 阻塞问题的故障排除指南。 它还提供有关数据库与紧急或环绕情况距离的信息。

什么是 autovacuum？

autovacuum 是一个 PostgreSQL 后台进程，可自动清理死元组并更新统计信息。 它通过自动运行两个关键维护任务来帮助维护数据库性能：

• VACUUM - 通过删除死元组并将这些空间标记为可供 PostgreSQL 重用，从而回收数据库文件中的空间。 它不一定减少磁盘上数据库文件的物理大小。 要将空间返回到操作系统，请使用重写表的操作（例如 VACUUM FULL 或 pg_repack），此类操作存在其他注意事项，例如排他锁或维护时段。
• ANALYZE - 收集表和索引的统计信息，这些信息被 PostgreSQL 查询规划器用于选择高效的执行计划。

若要确保 autovacuum 正常工作，请将 autovacuum 服务器参数设置为 ON。 启用后，PostgreSQL 会自动决定何时对表运行 VACUUM 或 ANALYZE，以确保数据库保持高效并得到优化。

自动清理内幕

Autovacuum 读取页面以查找死元组。 如果没有找到任何死元组，autovacuum 会丢弃该页面。 当自动清理找到不活动元组时，会删除这些不活动元组。 成本基于以下参数：

执行autovacuum的工作量取决于两个参数：

在所有当前支持的 PostgreSQL 版本中，默认值 autovacuum_vacuum_cost_limit 为 200（实际上设置为 -1，这使得它等于常规 vacuum_cost_limit的值（默认情况下为 200）。

PostgreSQL 版本 12 及更高版本中的默认值 autovacuum_vacuum_cost_delay 为 2 毫秒（版本 11 为 20 毫秒）。

缓冲区使用限制（PostgreSQL 16+）

从 PostgreSQL 版本 16 开始，可以使用 vacuum_buffer_usage_limit 参数来控制 VACUUM、ANALYZE 和 autovacuum 操作期间的内存使用情况。

此参数有助于防止 VACUUM 和 autovacuum 从共享缓冲区中逐出过多有用的页面，这可以提高维护作期间的整体数据库性能。 默认值通常基于shared_buffers设置，你可以将其配置为平衡清理性能与常规数据库操作的需求。

自动清理每秒唤醒 50 次 (50*20 ms=1000 ms)。 每次唤醒时，自动清理都会读取 200 页。

这意味着，在一秒钟内，autovacuum 可以执行以下操作：

• 大约 80 MB/秒 [(200 页/vacuum_cost_page_hit) * 每页 50 * 8 KB] 如果在共享缓冲区中找到具有不活动元组的所有页面。
• 大约 8 MB/秒 [ (200 页/vacuum_cost_page_miss) * 50 * 每页 8 KB]，如果所有包含不活动元组的页面都从磁盘读取。
• 大约 4 MB/秒 [(200 页/vacuum_cost_page_dirty) * 每页 50 * 8 KB]自动清理的最高写入速度为 4 MB/秒。

监视自动清理

Azure Database for PostgreSQL 提供用于监视 autovacuum 的以下指标。

Autovacuum 指标可用于监视和优化 Azure Database for PostgreSQL 灵活服务器的自动清理性能。 每项指标以 30 分钟的间隔发出，保留期最多为 93 天。 可以为特定指标创建警报，并且可以使用 DatabaseName 维度拆分和筛选指标数据。

如何启用 autovacuum 指标

• 默认情况下，自动清空指标处于禁用状态。
• 要启用这些指标，请将 metrics.autovacuum_diagnostics 服务器参数设置为“ON”。
• 此参数是动态的，因此不需要重启实例。

autovacuum 指标列表

有关使用自动清理指标的注意事项

• 使用 DatabaseName 维度的自动清空指标限制为 30 个数据库。
• 对于可突发的 SKU，使用 DatabaseName 维度的指标限制为 10 个数据库。
• DatabaseName 维度限制适用于 OID 列，这反映了数据库的创建顺序。

有关详细信息，请参阅 Autovacuum 指标。

使用以下查询来监视自动清理：

select schemaname,relname,n_dead_tup,n_live_tup,round(n_dead_tup::float/n_live_tup::float*100) dead_pct,autovacuum_count,last_vacuum,last_autovacuum,last_autoanalyze,last_analyze from pg_stat_all_tables where n_live_tup >0;

以下这些列可帮助你确定 autovacuum 是否能跟上表的活动情况：

触发自动清理

当死元组数超过特定阈值时，会触发 autovacuum 操作（ANALYZE 或 VACUUM）。 此数字取决于两个因素：表中的行总数以及固定阈值。 当表的 10% 加上 50 行发生变化时，默认触发 ANALYZE，而当表的 20% 加上 50 行发生变化时，触发 VACUUM。 由于 VACUUM 阈值是 ANALYZE 阈值的两倍， 因此 ANALYZE 触发的时间早于 VACUUM。

对于 PostgreSQL 版本 13 及更高版本，默认情况下，当表发生 20% 变化且插入行数达到 1,000 行时，ANALYZE 自动执行。

每个操作的确切公式为：

• Autoanalyze = autovacuum_analyze_scale_factor * 元组 + autovacuum_analyze_threshold或 autovacuum_vacuum_insert_scale_factor * 元组 + autovacuum_vacuum_insert_threshold （对于 PostgreSQL 版本 13 及更高版本）
• Autovacuum = autovacuum_vacuum_scale_factor * 元组 + autovacuum_vacuum_threshold

例如，如果有一个包含 100 行的表，则以下公式显示分析和清理操作的触发条件：

对于更新和删除： Autoanalyze = 0.1 * 100 + 50 = 60Autovacuum = 0.2 * 100 + 50 = 70

ANALYZE 会在表上更改 60 行后触发，而 VACUUM 会在表上更改 70 行时触发。

对于插入： Autoanalyze = 0.2 * 100 + 1000 = 1020

在表中插入 1,020 行后，ANALYZE 会触发。

下面是有关公式中使用的参数的说明：

使用以下查询列出数据库中的表，并确定符合自动清理进程的表：

 SELECT *
      ,n_dead_tup > av_threshold AS av_needed
      ,CASE
        WHEN reltuples > 0
          THEN round(100.0 * n_dead_tup / (reltuples))
        ELSE 0
        END AS pct_dead
    FROM (
      SELECT N.nspname
        ,C.relname
        ,pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins
        ,pg_stat_get_tuples_updated(C.oid) AS n_tup_upd
        ,pg_stat_get_tuples_deleted(C.oid) AS n_tup_del
        ,pg_stat_get_live_tuples(C.oid) AS n_live_tup
        ,pg_stat_get_dead_tuples(C.oid) AS n_dead_tup
        ,C.reltuples AS reltuples
        ,round(current_setting('autovacuum_vacuum_threshold')::INTEGER + current_setting('autovacuum_vacuum_scale_factor')::NUMERIC * C.reltuples) AS av_threshold
        ,date_trunc('minute', greatest(pg_stat_get_last_vacuum_time(C.oid), pg_stat_get_last_autovacuum_time(C.oid))) AS last_vacuum
        ,date_trunc('minute', greatest(pg_stat_get_last_analyze_time(C.oid), pg_stat_get_last_autoanalyze_time(C.oid))) AS last_analyze
      FROM pg_class C
      LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
      WHERE C.relkind IN (
          'r'
          ,'t'
          )
        AND N.nspname NOT IN (
          'pg_catalog'
          ,'information_schema'
          )
        AND N.nspname !~ '^pg_toast'
      ) AS av
    ORDER BY av_needed DESC ,n_dead_tup DESC;

常见的自动清理问题

查看 autovacuum 进程的常见问题的以下列表。

跟不上繁忙的服务器

autovacuum 过程估算每个 I/O操作的成本，为其执行的每个操作累积分数总计，并在达到上限后暂停。 此过程使用两个服务器参数： autovacuum_vacuum_cost_delay 和 autovacuum_vacuum_cost_limit。

默认情况下， autovacuum_vacuum_cost_limit 设置为 -1，这意味着 autovacuum 成本限制使用与参数相同的值 vacuum_cost_limit 。 默认值 vacuum_cost_limit 为 200。 vacuum_cost_limit 表示手动真空的成本。

如果设置为 autovacuum_vacuum_cost_limit -1，autovacuum 将使用 vacuum_cost_limit 参数。 如果将 autovacuum_vacuum_cost_limit 设置为大于 -1 的值，则 autovacuum 使用 autovacuum_vacuum_cost_limit 参数。

如果 autovacuum 无法有效运行，请考虑更改以下参数：

自动清理持续运行

如果 autovacuum 持续运行，则可能会影响服务器上的 CPU 和 I/O 利用率。 可能的原因如下：

maintenance_work_mem

autovacuum 守护程序使用 autovacuum_work_mem，其默认设置为 -1。 此默认设置意味着 autovacuum_work_mem 使用与参数相同的值 maintenance_work_mem 。 本文假定 autovacuum_work_mem 被设置为 -1，且 autovacuum 守护程序使用 maintenance_work_mem。

如果 maintenance_work_mem 较低，可以在 Azure Database for PostgreSQL 灵活服务器实例上将其增加到 2 GB。 一般的经验法则是，为每 1 GB 的 RAM 为 maintenance_work_mem 分配 50 MB。

大量数据库

自动清理每隔 autovacuum_naptime 秒尝试在每个数据库上启动一个工作器。

例如，如果服务器有 60 个数据库并且 autovacuum_naptime 设置为 60 秒，则 autovacuum 工作进程将每秒启动一次 [autovacuum_naptime/数据库数]。

如果群集中有更多的数据库，请增加 autovacuum_naptime。 同时，通过增加 autovacuum_cost_limit 参数和减少 autovacuum_cost_delay 参数，提高 autovacuum 进程的活跃度。 还可以 autovacuum_max_workers 从默认值 3 增加到 4 或 5。

内存不足错误

过于激进的 maintenance_work_mem 值可能会偶尔在系统中导致内存不足错误。 更改参数之前 maintenance_work_mem ，请先了解服务器上的可用 RAM。

自动清理产生中断的可能性很高

如果 autovacuum 消耗的资源过多，请尝试以下措施：

自动真空参数

评估参数autovacuum_vacuum_cost_delay、autovacuum_vacuum_cost_limit和autovacuum_max_workers。 不正确地设置自动清理参数可能会出现自动清理经常导致中断的情况。

如果自动清理经常导致中断，请考虑以下操作：

• 如果将其设置为高于默认值 200，则增加 autovacuum_vacuum_cost_delay 和减少 autovacuum_vacuum_cost_limit 。
• 如果将 autovacuum_max_workers 设置为大于默认值 3，请减少其数量。

自动清理工作器过多

增加自动清理工作区的数量并不一定会提高清理速度。 不要使用大量 autovacuum 辅助角色。

增加 autovacuum 辅助角色的数量会导致更多的内存消耗。 根据maintenance_work_mem的值，可能会导致性能下降。

每个自动清理工作进程只获得总数 autovacuum_cost_limit 的 (1/autovacuum_max_workers)，因此拥有大量工作器会导致每个工作进程变慢。

如果增加工人数，请增加 autovacuum_vacuum_cost_limit 和/或减少 autovacuum_vacuum_cost_delay 以加快真空过程。

但是，如果在表级别设置 autovacuum_vacuum_cost_delay 或 autovacuum_vacuum_cost_limit 参数，那么在这些表格上运行的工作线程将不被 [autovacuum_cost_limit/autovacuum_max_workers] 均衡算法考虑。

自动清理事务 ID (TXID) 回卷保护

当数据库遇到事务 ID 环绕保护问题时，您将看到类似以下的错误信息：

Database isn't accepting commands to avoid wraparound data loss in database 'xx'
Stop the postmaster and vacuum that database in single-user mode.

当数据库未进行清理或 autovacuum 未能有效删除足够的死元组时，会出现环绕问题。

此问题的可能原因包括以下原因：

工作负荷过重

繁重的工作负荷在短时间内导致过多的死元组，使得 autovacuum 难以赶上。 系统中的死元组在一段时间内累积会导致查询性能下降并导致回卷情况。 出现这种情况的原因之一可能是自动清理参数设置不充分，无法跟上繁忙服务器的进度。

长时间运行的事务

系统中任何长时间运行的事务都会阻止 autovacuum 删除死元组。 它们是清理进程的阻止程序。 在自动清理运行时，移除长时间运行的事务可以释放死元组以供删除。

可以使用以下查询检测长时间运行的事务：

    SELECT pid, age(backend_xid) AS age_in_xids,
    now () - xact_start AS xact_age,
    now () - query_start AS query_age,
    state,
    query
    FROM pg_stat_activity
    WHERE state != 'idle'
    ORDER BY 2 DESC
    LIMIT 10;

预定义语句

如果存在未提交的准备语句，它们将阻止 autovacuum 删除死元组。 以下查询有助于查找已准备就绪但未提交的语句：

    SELECT gid, prepared, owner, database, transaction
    FROM pg_prepared_xacts
    ORDER BY age(transaction) DESC;

使用 COMMIT PREPARED 或 ROLLBACK PREPARED 提交或回滚这些语句。

未使用的复制槽

未使用的复制槽可防止自动清理声明死元组。 以下查询有助于识别未使用的复制槽：

    SELECT slot_name, slot_type, database, xmin
    FROM pg_replication_slots
    ORDER BY age(xmin) DESC;

使用 pg_drop_replication_slot() 可删除未使用的复制槽。

当数据库遇到事务 ID 回卷保护时，请检查前面提到的任何阻止程序，并手动删除这些阻止程序，以便自动清理继续并完成。 还可以通过将 autovacuum_cost_delay 设置为 0 并将 autovacuum_cost_limit 增大为大于 200 的值来提高自动清理的速度。 但是，对这些参数的更改不会应用于现有的自动清理工作器。 重启数据库或手动终止现有工作器以应用参数更改。

特定于表的要求

可以为单个表设置 autovacuum 参数。 这些设置对小型和大型表格都尤其重要。 例如，对于仅包含 100 行的小表，当 70 行更改时，autovacuum 会触发 VACUUM 操作（如之前计算）。 如果频繁更新这个表，您可能每天会看到数百次 autovacuum 操作。 这些操作会阻止 autovacuum 维护其他表，因为这些表的更改百分比不太显著。 换句话说，包含 10 亿行的表需要更改 2 亿行，才能触发自动清理操作。 恰当设置自动清理参数可以避免这种情况。

若要为每个表设置 autovacuum 设置，请更改服务器参数，如以下示例所示：

    ALTER TABLE <table name> SET (autovacuum_analyze_scale_factor = xx);
    ALTER TABLE <table name> SET (autovacuum_analyze_threshold = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_scale_factor = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_threshold = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_cost_delay = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_cost_limit = xx);
    -- For PostgreSQL 16 and later:
    ALTER TABLE <table name> SET (vacuum_buffer_usage_limit = 'xx MB');

仅插入工作负载

在 PostgreSQL 版本 13 及更早版本中，autovacuum 不会在具有仅插入工作负荷的表上运行，因为没有死元组，也没有需要回收的可用空间。 但是，由于有新数据，自动分析将针对仅插入工作负载运行。 此行为的缺点是：

• 表的可见性映射不会更新，因此查询性能会不断下降，尤其是存在仅限索引扫描的情况下。
• 数据库可能会遇到事务 ID 回卷保护。
• 未设置提示位。

解决方案

PostgreSQL 版本 13 及更早版本

通过使用 pg_cron 扩展，可以设置 cron 作业来计划对表进行定期真空分析。 cron 作业的频率取决于工作负荷。

有关指导，请参阅 有关在 Azure Database for PostgreSQL 中使用pg_cron的特殊注意事项。

PostgreSQL 13 及更高版本

自动清理将在具有仅插入工作负载的表上运行。 两个服务器参数， autovacuum_vacuum_insert_threshold 以及 autovacuum_vacuum_insert_scale_factor帮助控制何时可以在仅插入表上触发 autovacuum。

疑难解答指南

Azure Database for PostgreSQL 灵活服务器在门户中提供故障排除指南，帮助你监控数据库或单个架构级别的冗余增长，并识别导致 autovacuum 进程潜在阻塞的因素。

提供了两个故障排除指南：

• Autovacuum 监控 - 使用本指南监控数据库或单个模式层面的膨胀。
• Autovacuum 阻塞因素和回绕 - 本指南可帮助你识别潜在的 autovacuum 阻塞因素，并提供有关服务器上的数据库与回绕或紧急状态的距离的信息。

这两个故障排除指南还分享了缓解潜在问题的建议。 有关如何设置和使用故障排除指南的信息，请参阅 设置故障排除指南。

终止 autovacuum 进程：pg_signal_autovacuum_worker 角色

Autovacuum 是一个重要的后台过程，因为它有助于在数据库中进行高效的存储和性能维护。 在正常的总清理进程中，它会在 deadlock_timeout 之后自动取消自身。 如果用户在表上执行 DDL 语句，可能需要等到 deadlock_timeout 时间间隔。 Autovacuum 不允许对不同连接请求请求的表执行读取或写入，这增加了事务中的延迟。

我们从 PostgreSQL 引入了新角色 pg_signal_autovacuum_worker，它允许非超级用户成员终止正在进行的 autovacuum 任务。 新角色可帮助用户获得对 autovacuum 过程的安全和控制访问权限。 非超级用户在被授予pg_signal_autovacuum_worker角色后，可使用pg_terminate_backend命令取消autovacuum进程。 该角色 pg_signal_autovacuum_worker 在 PostgreSQL 版本 15 及更高版本中的 Azure Database for PostgreSQL 中提供。

关于重复运行的 autovacuum 进程的建议方法

在极少数情况下，例如防止环绕的自动清理，工作人员可能会在终止后立即重启，因为它们对于防止事务 ID 耗尽至关重要。 若要最大程度地减少重复冲突，请执行以下步骤：

• 在终止之前将 DDL 操作进行排队。

  • 会话 1：准备和执行 DDL 语句。

  • 会话 2：终止自动清理进程。

    重要

    必须背靠背执行这两个步骤。 如果 DDL 语句被阻塞时间过长，它会在服务器上保持锁并阻止其他 DML 操作。

• 终止“autovacuum”并执行DDL：如果必须立即运行DDL：

  • 使用 pg_terminate_backend() 函数终止 autovacuum 进程。
  • 在终止后立即执行 DDL 语句。

避免重复冲突的步骤：

1. 向用户授予角色

   GRANT pg_signal_autovacuum_worker TO app_user;
   

   1. 标识 autovacuum 进程 ID

   SELECT pid, query FROM pg_stat_activity WHERE query LIKE '%autovacuum%' and pid!=pg_backend_pid();
   

2. 终止“autovacuum”

   SELECT pg_terminate_backend(<pid>);
   

3. 立即执行 DDL 语句

   ALTER TABLE my_table ADD COLUMN new_col TEXT;
   

Azure 顾问建议

Azure Advisor 建议主动识别服务器是否具有过度膨胀比例或服务器是否正在接近事务回绕情况。 还可为建议创建 Azure 顾问警报。

这三个建议是：

• 高膨胀率：高膨胀率可以通过多种方式影响服务器性能。 一个重要问题是 PostgreSQL 引擎优化器可能难以选择最佳执行计划，从而导致查询性能下降。 因此，当服务器上的膨胀百分比达到特定阈值时将触发建议，以避免此类性能问题。

• 事务回绕：此情形是服务器可能会遇到的最严重问题之一。 服务器处于此状态后，可能会停止接受任何其他事务，从而导致服务器变为只读。 因此，当服务器超过 10 亿个事务阈值时，将触发建议。

相关内容

• 在 Azure Database for PostgreSQL 中使用pg_repack完全清空
• 排查 Azure Database for PostgreSQL 中的 CPU 使用率过高问题
• 排查 Azure Database for PostgreSQL 中的高内存利用率问题
• 排查 Azure Database for PostgreSQL 中的高 IOPS 使用率问题
• 排查和识别 Azure Database for PostgreSQL 中运行缓慢的查询
• Azure Database for PostgreSQL 中的服务器参数