Azure Database for PostgreSQL–灵活服务器中的自动清理优化

适用于： Azure Database for PostgreSQL 灵活服务器

本文概述了用于 Azure Database for PostgreSQL 灵活服务器 的 autovacuum 功能，以及可用于监视数据库膨胀的故障排除指南功能（即 autovacuum 阻碍因素）。 它还提供有关数据库还差多少便会达到紧急或回绕情况的信息。

什么是自动清理

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

• VACUUM - 通过移除死元组释放磁盘空间。
• ANALYZE - 收集统计信息以帮助 PostgreSQL 优化器选择查询的最佳执行路径。

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

自动清理内幕

Autovacuum 读取页面以查找死元组，如果未找到死元组，则 autovacuum 会丢弃该页面。 当自动清理找到不活动元组时，则会删除这些不活动元组。 成本是基于：

参数	说明
vacuum_cost_page_hit	读取已在共享缓冲区中且不需要磁盘读取的页面的成本。 默认值设置为 1。
vacuum_cost_page_miss	提取不在共享缓冲区中的页面的成本。 默认值设置为 10。
vacuum_cost_page_dirty	在页面中找到死元组时写入该页面的成本。 默认值设置为 20。

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

参数	说明
autovacuum_vacuum_cost_limit	autovacuum 一次完成的工作量。
autovacuum_vacuum_cost_delay	autovacuum 在达到 autovacuum_vacuum_cost_limit 参数指定的成本限制后休眠的毫秒数。

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

至于 autovacuum_vacuum_cost_delay，在 Postgres 版本 11 中，它默认为 20 毫秒，而在 Postgres 版本 12 及更高版本中，它默认为 2 毫秒。

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

这意味着，自动清理在一秒内可以执行以下操作：

• 大约 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/秒。

监视自动清理

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

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;

以下列有助于确定自动清理是否跟上了表活动：

参数	说明
dead_pct	与活元组相比时死元组的百分比。
last_autovacuum	上次自动清空表的日期。
last_autoanalyze	上次自动分析表的日期。

PostgreSQL 在什么情况下触发 自动清理

当死元组的数量超过取决于两个因素的特定数量时，将触发自动清理操作（ANALYZE 或 VACUUM）：表中的总行数加上固定阈值。 默认情况下，当表的 10% 外加 50 行发生更改时，会触发 ANALYZE；当表的 20% 外加 50 行发生更改时，会触发 VACUUM。 由于 VACUUM 阈值是 ANALYZE 阈值的两倍，因此 ANALYZE 的触发时间早于 VACUUM。 如果 PG 版本 >=13，默认情况下，当表的 20% 外加 1000 行发生插入时，会触发 ANALYZE。

每个操作的确切公式为：

• Autoanalyze = autovacuum_analyze_scale_factor * tuples + autovacuum_analyze_threshold 或 autovacuum_vacuum_insert_scale_factor * tuples + autovacuum_vacuum_insert_threshold（对于 PG 版本 >= 13 的情况）
• Autovacuum = autovacuum_vacuum_scale_factor * tuples + autovacuum_vacuum_threshold

例如，如果有一个包含 100 行的表。 然后，以下公式将提供有关何时会触发分析和清理的信息：

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

在表上更改 60 行后会触发分析，在表上更改 70 行后会触发清理。

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

在表中插入 1,020 行后会触发分析

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

参数	说明
autovacuum_analyze_scale_factor	在表中触发 ANALYZE 的插入/更新/删除百分比。
autovacuum_analyze_threshold	指定将导致分析 (ANALYZE) 表的最小插入/更新/删除元组数。
autovacuum_vacuum_insert_scale_factor	在表中触发 ANLYZE 的插入百分比。
autovacuum_vacuum_insert_threshold	指定将导致分析 (ANALYZE) 表的最小插入元组数。
autovacuum_vacuum_scale_factor	在表中触发 VACUUM 的更新/删除百分比。

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

 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 进程可能出现的以下常见问题的列表。

跟不上繁忙的服务器

自动清理进程估计每个 I/O 操作的成本，在达到成本上限后，会为其执行的每个操作累加总成本并暂停。 进程中使用的两个服务器参数为 autovacuum_vacuum_cost_delay 和 autovacuum_vacuum_cost_limit。

默认情况下，autovacuum_vacuum_cost_limit 设置为 -1，这意味着自动清理成本限制与参数 vacuum_cost_limit 的值相同，默认为 200。 vacuum_cost_limit 是手动清理的成本。

如果将 autovacuum_vacuum_cost_limit 设置为 -1，autovacuum 将使用 vacuum_cost_limit 参数，但如果将 autovacuum_vacuum_cost_limit 本身设置为大于 -1，则考虑使用 autovacuum_vacuum_cost_limit 参数。

如果自动清理跟不上进度，可更改以下参数：

参数	说明
autovacuum_vacuum_cost_limit	默认：200。 可以提高成本限制。 应在更改之前和之后监视数据库的 CPU 和 I/O 利用率。
autovacuum_vacuum_cost_delay	Postgres 版本 11 - 默认值：20 ms。 可将该参数减小至 2-10 ms。 Postgres 版本 12 及更高版本 - 默认值：2 ms。

自动清理持续运行

持续运行自动清理可能会影响服务器上的 CPU 和 IO 使用率。 下面是一些可能的原因：

maintenance_work_mem

自动清理守护程序使用 autovacuum_work_mem，后者默认设置为 -1，这表示 autovacuum_work_mem 将具有与参数 maintenance_work_mem 相同的值。 本文档假定 autovacuum_work_mem 设置为 -1，并且自动清理守护程序使用 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_max_workers 的默认值从 3 增加到 4 或 5 来使自动清理过程更加激进。

内存不足错误

过于激进的 maintenance_work_mem 值可能会周期性地导致系统中的内存不足错误。 在对 maintenance_work_mem 参数进行任何更改之前，了解服务器上的可用 RAM 非常重要。

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

如果 autovacuum 消耗更多资源，可以执行以下操作：

自动清理参数

评估参数 autovacuum_vacuum_cost_delay、autovacuum_vacuum_cost_limit 和 autovacuum_max_workers。 不当地设置自动清理参数可能导致自动清理扰乱性太大。

如果 autovacuum 扰乱性太大，请考虑以下措施：

• 增大 autovacuum_vacuum_cost_delay 的值，如果 autovacuum_vacuum_cost_limit 的设置值高于默认值 200，则减小。
• 减少 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 正在运行时移除死元组。 它们是清理进程的阻止程序。 在自动清理运行时，移除长时间运行的事务可以释放死元组以供删除。

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

    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;

预定义语句

如果有未提交的预定义语句，它们会阻止移除死元组。
以下查询可帮助查找未提交的预定义语句：

    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 能够继续并完成。 还可以通过将 autovacuum_cost_delay 设置为 0 并将 autovacuum_cost_limit 增大为大于 200 的值来提高自动清理的速度。 但是，对这些参数的更改不适用于现有的 autovacuum 工作进程。 重启数据库或手动终止现有工作器以应用参数更改。

特定于表的要求

可为单个表设置自动清理参数。 这一点对于小型和大型表尤其重要。 例如，对于仅包含 100 行的小型表，自动清理在 70 行发生变化时触发 VACUUM 操作（如前所述）。 如果此表经常更新，则每天可能会看到数百个 autovacuum 操作，这将会妨碍 autovacuum 维护其他表（这些表的更改百分比将会较小）。 换句话说，包含 10 亿行的表需要更改 2 亿行才能触发 自动清理自动清理操作。 适当地设置自动清理参数可以防止这种情况。

要为每个表设置自动清理设置，请更改服务器参数，如下例所示：

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

仅插入工作负荷

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

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

解决方案

Postgres 版本 <= 13

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

有关使用 pg_cron 的分步指南，请查看扩展。

Postgres 13 及更高版本

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

疑难解答指南

使用 Azure Database for PostgreSQL 灵活服务器门户上提供的故障排除指南功能，可以监视数据库或单个架构级别的膨胀，并识别 autovacuum 进程的潜在阻碍因素。 有两个故障排除指南可用，第一个是自动清理监视，可用于监视数据库或单个架构级别的膨胀。 第二个故障排除指南是 autovacuum 阻碍因素和回绕，这有助于识别潜在的 autovacuum 阻碍因素。 它还提供有关服务器上的数据库还差多少便会达到回绕或紧急情况的信息。 这两个故障排除指南还分享了缓解潜在问题的建议。 有关如何设置故障排除指南以使用它们的信息，请参阅设置故障排除指南。

相关内容

• CPU 使用率过高
• 内存使用率过高
• 服务器参数