Share via

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

  • 项目

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

本文概述了 Azure Database for PostgreSQL 灵活服务器的自动清理功能，以及可用于监视数据库膨胀、自动清理阻止程序的功能故障排除指南，以及有关数据库与紧急情况或解决情况的距离的信息。

什么是自动清理

PostgreSQL 中的内部数据一致性是基于多版本并发控制 (MVCC) 机制，该机制允许数据库引擎维护行的多个版本，并提供更大的并发性，同时尽量减少不同进程之间的阻塞。

PostgreSQL 数据库需要适当的维护。 例如，删除某一行时，该行在物理上并未移除。 而是将行标记为“不活动”。 同样，对于更新，该行标记为“不活动”，并且插入了新版本的行。 这些操作留下不活动记录，称为不活动元组，即使在可能看到这些版本的所有事务完成之后也是如此。 除非清理，否则不活动元组会一直存在，占用磁盘空间并膨胀表和索引，从而导致查询性能缓慢。

PostgreSQL 使用一个名为“自动清理”的过程来自动清理死元组。

自动清理内幕

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

  • vacuum_cost_page_hit：读取已在共享缓冲区中且不需要磁盘读取的页面的开销。 默认值设置为 1。
  • vacuum_cost_page_miss：提取不在共享缓冲区中的页面的开销。 默认值设置为 10。
  • vacuum_cost_page_dirty：在某个页面中发现不活动元组时写入该页面的开销。 默认值设置为 20。

自动清理的工作量取决于两个参数：

  • autovacuum_vacuum_cost_limit 是自动清理一次性完成的工作量。
  • autovacuum_vacuum_cost_delay 是自动清理在达到 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。

每个操作的确切公式为：

  • Autoanalyze = autovacuum_analyze_scale_factor * tuples + autovacuum_analyze_threshold
  • Autovacuum = autovacuum_vacuum_scale_factor * tuples + autovacuum_vacuum_threshold

例如，在包含 100 行的表中发生 60 行更改后分析触发器，并在表中更改 70 行时清空触发器，使用以下公式：

Autoanalyze = 0.1 * 100 + 50 = 60
Autovacuum = 0.2 * 100 + 50 = 70

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

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

注意

该查询未考虑到可以使用“alter table”DDL 命令按表配置自动清理。

常见的自动清理问题

查看 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，则自动清理使用 vacuum_cost_limit 参数，但如果 autovacuum_vacuum_cost_limit 自身设置为大于 -1，则考虑 autovacuum_vacuum_cost_limit 参数。

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

参数 说明
autovacuum_vacuum_scale_factor 默认值：0.2，范围：0.05 - 0.1。 缩放因子特定于工作负荷，应根据表中的数据量进行设置。 在更改值之前，请调查工作负荷和单个表卷。
autovacuum_vacuum_cost_limit 默认：200。 可以提高成本限制。 应在更改之前和之后监视数据库的 CPU 和 I/O 利用率。
autovacuum_vacuum_cost_delay Postgres 版本 11 - 默认值：20 ms。 可将该参数减小至 2-10 ms。
Postgres 版本 12 及更高版本 - 默认值：2 ms。

注意

autovacuum_vacuum_cost_limit 值在正在运行的自动清理辅助角色之间按比例分配，因此如果有多个辅助角色，则每个辅助角色的限制总和不会超过 autovacuum_vacuum_cost_limit 参数的值

自动清理持续运行

持续运行自动清理可能会影响服务器上的 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 秒，那么自动清理工作器每秒启动一次 [自动清理_naptime/DB 数]。

如果群集中有更多数据库，最好增大 autovacuum_naptime。 同时，可以通过增大 autovacuum_cost_limit 和减少 autovacuum_cost_delay 参数以及将 autovacuum_max_workers 的默认值从 3 增加到 4 或 5 来使自动清理过程更加激进。

内存不足错误

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

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

如果自动清理消耗大量资源，用户可以执行以下操作：

自动清理参数

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

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

  • 增大 autovacuum_vacuum_cost_delay 的值，如果 autovacuum_vacuum_cost_limit 的设置值高于默认值 200，则减小。
  • 减少 autovacuum_max_workers 的数量（如果其设置高于默认值 3）。

自动清理工作器过多

增加自动清理工作进程的数量不一定会提高清理速度。 不建议使用过多的自动清理辅助角色。

增加自动清理工作器的数量会导致更多的内存消耗，并且根据 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.

注意

此错误消息是长期监督。 通常不需要切换到单用户模式。 相反，可以运行所需的 VACUUM 命令并执行优化以使 VACUUM 快速运行。 虽然你不能运行任何数据操作语言 (DML)，但仍然可以运行 VACUUM。

当数据库未清理或者有太多无法被自动清理移除的死元组时，就会出现回绕问题。 造成这种情况的原因可能是：

工作负荷过重

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

长时间运行的事务

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

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

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

特定于表的要求

可为单个表设置自动清理参数。 这对于小型和大型表尤其重要。 例如，对于仅包含 100 行的小型表，自动清理在 70 行发生变化时触发 VACUUM 操作（如前所述）。 如果此表经常更新，你每天可能会看到数百次自动清理操作。 这会导致自动清理无法维护更改百分比不太大的其他表。 换句话说，包含 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);

仅插入工作负荷

在 PostgreSQL 13 之前的版本中，自动清理不会对具有仅插入工作负载的表运行，因为如果未发生更新或删除，则不会出现死元组，因此也就不需要回收可用空间。 但是，由于存在新数据，将针对仅插入工作负载运行自动分析。 这样做的缺点是：

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

解决方案

13 之前的 Postgres 版本

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

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

Postgres 13 及更高版本

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

疑难解答指南

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

相关内容