Azure Database for PostgreSQL 弹性群集中的自动清理（Autovacuum）优化调整

本文概述了适用于 Azure Database for PostgreSQL 弹性群集的 autovacuum 功能以及可用于监视数据库膨胀和 autovacuum 阻止器的故障排除指南。 它还提供有关数据库与紧急或环绕情况距离的信息。

什么是 autovacuum？

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

• VACUUM - 通过删除死元组并将这些空间标记为可供 PostgreSQL 重用，从而回收数据库文件中的空间。 它不一定减少磁盘上数据库文件的物理大小。 若要将空间返还给操作系统，请使用会重写表的操作（例如 VACUUM FULL 或 pg_repack），这些操作涉及其他注意事项，如独占锁或维护时段。

  以下查询在所有节点上运行 VACUUM。

  VACUUM;
  

  以下查询对所有节点上的订单表执行 VACUUM 操作。

  VACUUM (VERBOSE, ANALYZE) public.orders;
  

• ANALYZE - 收集表和索引的统计信息，这些信息被 PostgreSQL 查询规划器用于选择高效的执行计划。 若要确保 autovacuum 正常工作，请将 autovacuum 服务器参数设置为 ON。 启用后，PostgreSQL 会自动决定何时对表运行 VACUUM 或 ANALYZE，以确保数据库保持高效并得到优化。

  以下查询将在所有节点上执行 ANALYZE。

  ANALYZE;
  

  以下查询针对所有节点中的订单表运行 ANALYZE。

  ANALYZE public.orders;
  

自动清理内幕

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 开始，使用 vacuum_buffer_usage_limit 参数来控制 VACUUM、ANALYZE 和 autovacuum 操作期间的内存使用情况。

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

autovacuum 的最大阈值（PostgreSQL 18+）

从 PostgreSQL 版本 18 开始，使用 autovacuum_vacuum_max_threshold 参数设置触发 autovacuum 的元组更新或删除数上限。

此参数特别适用于大型表，其中基于默认缩放系数的触发可能导致 autovacuum 在运行前等待过久。

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

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

• 如果共享缓冲区中找到具有死元组的所有页面，则为大约 80 MB/s [ （200 pages/vacuum_cost_page_hit） * 每页 50 * 8 KB]。
• 大约 8 MB/s [ (200 页/vacuum_cost_page_miss) * 50 * 每页 8 KB] 如果从磁盘读取包含死元组的所有页面。
• 大约 4 MB/秒 [(200 页/vacuum_cost_page_dirty) * 50 * 每页 8 KB]，autovacuum 最多可写入 4 MB/秒。

监视自动清理

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

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

如何启用 autovacuum 指标

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

autovacuum 指标列表

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

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

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

使用以下查询来监视autovacuum

以下查询返回所有工作器节点的膨胀信息。

SELECT * FROM run_command_on_all_nodes( $$ SELECT json_agg(t)
FROM (SELECT schemaname, relname, n_live_tup, n_dead_tup, n_dead_tup/(n_live_tup)* 100 as Bloat, last_autovacuum, last_autoanalyze, last_vacuum, last_analyze,autovacuum_count
FROM pg_stat_all_tables WHERE n_live_tup > 0 ORDER BY n_dead_tup DESC ) t $$ );

以下的列将帮助你确定 autovacuum 是否跟得上表活动的进度：

触发 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 会触发。

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

使用以下查询列出所有工作节点上数据库中的表，并确定适用于 autovacuum 进程的表：

SELECT * FROM run_command_on_all_nodes( $$ SELECT json_agg(t)
FROM (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) t $$ );

常见的自动清理问题

查看 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 不断运行

如果 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。

内存不足错误

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

Autovacuum 影响性能

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

自动真空参数

评估参数autovacuum_vacuum_cost_delay、autovacuum_vacuum_cost_limit和autovacuum_max_workers。 设置 autovacuum 参数不当可能会导致 autovacuum 中断性能的情况。

如果 autovacuum 影响系统性能，请考虑采取以下措施：

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

自动清理工作器过多

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

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

每个 autovacuum 工作进程仅获取总量的 (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.

若要检查协调器节点和计算节点上接近溢出的百分比，请使用以下查询：

SELECT * FROM run_command_on_all_nodes( $$ SELECT json_agg(t) FROM (SELECT datname
    , age(datfrozenxid)
    , current_setting('autovacuum_freeze_max_age')
FROM pg_database where datname = 'postgres'
ORDER BY 2 DESC
) t $$ );

SELECT * FROM run_command_on_all_nodes( $$ SELECT json_agg(t) FROM (WITH max_age AS (
    SELECT 2000000000 AS max_old_xid
        , setting AS autovacuum_freeze_max_age
        FROM pg_catalog.pg_settings
        WHERE name = 'autovacuum_freeze_max_age' )
, per_database_stats AS (
    SELECT datname
        , m.max_old_xid::int
        , m.autovacuum_freeze_max_age::int
        , age(d.datfrozenxid) AS oldest_current_xid
    FROM pg_catalog.pg_database d
    JOIN max_age m ON (true)
    WHERE d.datallowconn )
SELECT max(oldest_current_xid) AS oldest_current_xid
    , max(ROUND(100*(oldest_current_xid/max_old_xid::float))) AS percent_towards_wraparound
    , max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float))) AS percent_towards_emergency_autovac
FROM per_database_stats) t $$ );

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

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

工作负荷过重

繁重的工作负荷在短时间内导致过多的死元组，使得 autovacuum 难以赶上。 系统中的死元组随时间推移而增加，导致查询性能下降，并导致回绕现象。 这种情况的一个原因可能是“autovacuum”参数设置不足，无法跟上繁忙的服务器。

长时间运行的事务

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

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

SELECT pg.nodeport,age(backend_xid) AS age_in_xids,
NOW() - xact_start AS xact_age,
NOW() - query_start AS query_age, cs.*
, state
, query
FROM citus_stat_activity cs
join pg_dist_node pg on cs.nodeid = pg.nodeid
WHERE state != 'idle'
AND pid <> pg_backend_pid() AND state IN ('idle in transaction', 'active')
ORDER BY NOW() - query_start DESC
LIMIT 10;

预定义语句

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

在工作节点上运行以下查询以查找任何未提交的预备事务。

SELECT * FROM run_command_on_all_nodes( $$ SELECT json_agg(t) FROM (
  SELECT * FROM pg_prepared_xacts
) t $$ );

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

未使用的复制槽

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

运行以下查询，在工作器节点上查找任何未使用的复制槽。

SELECT * FROM run_command_on_all_nodes( $$ SELECT json_agg(t) FROM (
  SELECT slot_name, slot_type, database, xmin,active FROM pg_replication_slots
) t $$ );

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

当数据库遇到事务 ID 环绕保护时，检查之前提到的任何阻碍因素，并手动删除这些阻碍因素，以便 autovacuum 可以继续运行并完成任务。 还可以通过将 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);
    ALTER TABLE <table name> SET (vacuum_buffer_usage_limit = 'xx MB');

无法设置表分片的参数值。

可以在协调器节点上使用以下查询来检查设置值：

SELECT relname, reloptions FROM pg_class WHERE relname = <table name>;

可以在所有节点上查看查询：

SELECT * FROM run_command_on_all_nodes( $$ SELECT json_agg(t) FROM (
  SELECT relname, reloptions FROM pg_class WHERE relname = <tablename>
) t $$ );

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

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

• 在终止之前对 DDL作进行排队：
  • 第一部分：在协调器节点上准备并运行 DDL 语句。
  • 会话 2：终止自动清理进程。

在终止之后，立即在协调器节点上执行 DDL 语句。

避免重复冲突的步骤：

1. 向用户授予角色

   GRANT pg_signal_autovacuum_worker TO app_user;
   

2. 标识 autovacuum 进程 ID

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

3. 终止“autovacuum”

   SELECT pg_terminate_backend(<pid>);
   

4. 立即执行 DDL 语句

   ALTER TABLE my_table ADD COLUMN new_col TEXT;
   

相关内容

• Azure Database for PostgreSQL 中的服务器参数