在 Azure Database for PostgreSQL - 单一服务器中优化 autovacuumOptimize autovacuum on an Azure Database for PostgreSQL - Single Server

本文介绍如何在 Azure Database for PostgreSQL 服务器中有效优化 autovacuum。This article describes how to effectively optimize autovacuum on an Azure Database for PostgreSQL server.

Autovacuum 概述Overview of autovacuum

PostgreSQL 使用多版本并发控制 (MVCC) 实现更高的数据库并发性。PostgreSQL uses multiversion concurrency control (MVCC) to allow greater database concurrency. 每次更新都会导致插入和删除,而每次删除都会导致要删除的行被软标记。Every update results in an insert and delete, and every delete results in rows being soft-marked for deletion. 软标记用于标识随后要清除的死元组。Soft-marking identifies dead tuples that will be purged later. 为执行这些任务,PostgreSQL 将运行一个清扫作业。To carry out these tasks, PostgreSQL runs a vacuum job.

清扫作业可以手动触发或自动触发。A vacuum job can be triggered manually or automatically. 在数据库进行大量更新或删除操作时,死元组会更多。More dead tuples exist when the database experiences heavy update or delete operations. 数据库空闲时,死元组较少。Fewer dead tuples exist when the database is idle. 数据库负载过大时,需要更频繁地运行清扫作业,因此手动运行清扫作业会有所不便。You need to vacuum more frequently when the database load is heavy, which makes running vacuum jobs manually inconvenient.

可以配置 autovacuum 并从优化中获益。Autovacuum can be configured and benefits from tuning. PostgreSQL 附带的默认值尝试确保产品在所有类型的设备上正常运行。The default values that PostgreSQL ships with try to ensure the product works on all kinds of devices. 这些设备包括 Raspberry Pi。These devices include Raspberry Pis. 理想的配置值取决于:The ideal configuration values depend on the:

  • 可用资源总数,例如 SKU 和存储大小。Total resources available, such as SKU and storage size.
  • 资源使用情况。Resource usage.
  • 单独对象特征。Individual object characteristics.

Autovacuum 的优势Autovacuum benefits

如果不时常运行清扫作业,累积的死元组可能会导致以下问题:If you don't vacuum from time to time, the dead tuples that accumulate can result in:

  • 数据膨胀,例如数据库和表变大。Data bloat, such as larger databases and tables.
  • 更大的非最优索引。Larger suboptimal indexes.
  • I/O 增加。Increased I/O.

使用 autovacuum 查询监视膨胀情况Monitor bloat with autovacuum queries

以下示例查询的目的是确定名为 XYZ 的表中的非活动元组和活动元组的数量:The following sample query is designed to identify the number of dead and live tuples in a table named XYZ:

SELECT relname, n_dead_tup, n_live_tup, (n_dead_tup/ n_live_tup) AS DeadTuplesRatio, last_vacuum, last_autovacuum FROM pg_catalog.pg_stat_all_tables WHERE relname = 'XYZ' order by n_dead_tup DESC;

Autovacuum 配置Autovacuum configurations

控制 autovacuum 的配置参数基于两个关键问题的答案:The configuration parameters that control autovacuum are based on answers to two key questions:

  • 何时应该启动它?When should it start?
  • 启动它以后应清除多少内容?How much should it clean after it starts?

下面是一些可以基于以上问题更新的 autovacuum 配置参数以及一些指导信息。Here are some autovacuum configuration parameters that you can update based on the previous questions, along with some guidance.

参数Parameter 说明Description 默认值Default value
autovacuum_vacuum_thresholdautovacuum_vacuum_threshold 指定在任一表中触发清扫操作所需的已更新或已删除元组的最小数量。Specifies the minimum number of updated or deleted tuples needed to trigger a vacuum operation in any one table. 默认值为 50 个元组。The default is 50 tuples. 只能在 postgresql.conf 文件中或服务器命令行上设置此参数。Set this parameter only in the postgresql.conf file or on the server command line. 若要替代单个表的设置,请更改表存储参数。To override the setting for individual tables, change the table storage parameters. 5050
autovacuum_vacuum_scale_factorautovacuum_vacuum_scale_factor 指定在决定是否触发清扫操作时要添加到 autovacuum_vacuum_threshold 的表大小的占比。Specifies a fraction of the table size to add to autovacuum_vacuum_threshold when deciding whether to trigger a vacuum operation. 默认值为 0.2,即表大小的 20%。The default is 0.2, which is 20 percent of table size. 只能在 postgresql.conf 文件中或服务器命令行上设置此参数。Set this parameter only in the postgresql.conf file or on the server command line. 若要替代单个表的设置,请更改表存储参数。To override the setting for individual tables, change the table storage parameters. 0.20.2
autovacuum_vacuum_cost_limitautovacuum_vacuum_cost_limit 指定自动清扫操作中使用的成本限制值。Specifies the cost limit value used in automatic vacuum operations. 如果指定为 -1(默认值),则会使用常规的 vacuum_cost_limit 值。If -1 is specified, which is the default, the regular vacuum_cost_limit value is used. 如果存在多个辅助角色,则该值会按比例分配给这些运行 autovacuum 的辅助角色。If there's more than one worker, the value is distributed proportionally among the running autovacuum workers. 每个辅助角色的限制的总和不超过此变量的值。The sum of the limits for each worker doesn't exceed the value of this variable. 只能在 postgresql.conf 文件中或服务器命令行上设置此参数。Set this parameter only in the postgresql.conf file or on the server command line. 若要替代单个表的设置,请更改表存储参数。To override the setting for individual tables, change the table storage parameters. -1-1
autovacuum_vacuum_cost_delayautovacuum_vacuum_cost_delay 指定自动清扫操作中使用的成本延迟值。Specifies the cost delay value used in automatic vacuum operations. 如果指定为 -1,则会使用常规的 vacuum_cost_delay 值。If -1 is specified, the regular vacuum_cost_delay value is used. 默认值为 20 毫秒。The default value is 20 milliseconds. 只能在 postgresql.conf 文件中或服务器命令行上设置此参数。Set this parameter only in the postgresql.conf file or on the server command line. 若要替代单个表的设置,请更改表存储参数。To override the setting for individual tables, change the table storage parameters. 20 ms20 ms
autovacuum_naptimeautovacuum_naptime 指定任一给定数据库上运行的 autovacuum 之间的最小延迟。Specifies the minimum delay between autovacuum runs on any given database. 守护程序会在每一轮中检查数据库并根据需要为数据库中的表发出清扫和分析命令。In each round, the daemon examines the database and issues VACUUM and ANALYZE commands as needed for tables in that database. 延迟以秒为单位。The delay is measured in seconds. 只能在 postgresql.conf 文件中或服务器命令行上设置此参数。Set this parameter only in the postgresql.conf file or on the server command line. 15 s15 s
autovacuum_max_workersautovacuum_max_workers 指定在任何时候可能运行的 autovacuum 进程(不是 autovacuum 启动器)的最大数量。Specifies the maximum number of autovacuum processes, other than the autovacuum launcher, that can run at any one time. 默认值为三个。The default is three. 只能在服务器启动时设置此参数。Set this parameter only at server start. 33

若要替代单个表的设置,请更改表存储参数。To override the settings for individual tables, change the table storage parameters.

Autovacuum 成本Autovacuum cost

以下是运行清扫操作的“成本”:Here are the "costs" of running a vacuum operation:

  • 锁定运行清扫作业的数据页面。The data pages that the vacuum runs on are locked.
  • 运行清扫作业时使用计算和内存。Compute and memory are used when a vacuum job is running.

因此,清扫作业的运行频率不宜过高或过低。As a result, don't run vacuum jobs either too frequently or too infrequently. 清扫作业需要适应工作负荷。A vacuum job needs to adapt to the workload. 由于不同的 autovacuum 参数存在利弊,请对所有参数更改进行测试。Test all autovacuum parameter changes because of the tradeoffs of each one.

Autovacuum 启动触发器Autovacuum start trigger

当死元组的数量超过 autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples 时,会触发 autovacuum。Autovacuum is triggered when the number of dead tuples exceeds autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples. 此处的 reltuples 为一个常数。Here, reltuples is a constant.

通过 autovacuum 进行的清理工作必须与数据库负载保持一致。Cleanup from autovacuum must keep up with the database load. 否则可能会耗尽存储并导致查询速度普遍下降。Otherwise, you might run out of storage and experience a general slowdown in queries. 在随时间分摊后,清扫操作清理死元组的速率应该等于创建死元组的速率。Amortized over time, the rate at which a vacuum operation cleans up dead tuples should equal the rate at which dead tuples are created.

带有很多更新和删除内容的数据库具有更多的死元组,且需要更多的空间。Databases with many updates and deletes have more dead tuples and need more space. 通常情况下,对于带有很多更新和删除内容的数据库,将 autovacuum_vacuum_scale_factor 和 autovacuum_vacuum_threshold 设为较低的值比较有利。Generally, databases with many updates and deletes benefit from low values of autovacuum_vacuum_scale_factor and autovacuum_vacuum_threshold. 较低的值可以防止死元组长时间累积。The low values prevent prolonged accumulation of dead tuples. 而较小的数据库可将这两个参数设为较高的值,因为它们的清扫需求没有那么迫切。You can use higher values for both parameters with smaller databases because the need for vacuuming is less urgent. 频繁的清扫作业会带来计算和内存成本。Frequent vacuuming comes at the cost of compute and memory.

默认比例因子 20% 适用于死元组占比较低的表。The default scale factor of 20 percent works well on tables with a low percentage of dead tuples. 它不适用于死元组占比较高的表。It doesn't work well on tables with a high percentage of dead tuples. 例如,对于 20 GB 的表,此比例因子相当于存在 4 GB 的死元组。For example, on a 20-GB table, this scale factor translates to 4 GB of dead tuples. 对于 1 TB 的表,相当于存在 200 GB 的死元组。On a 1-TB table, it’s 200 GB of dead tuples.

使用 PostgreSQL,可以在表级别或实例级别设置这些参数。With PostgreSQL, you can set these parameters at the table level or instance level. 目前,只能在 Azure Database for PostgreSQL 中的表级别设置这些参数。Today, you can set these parameters at the table level only in Azure Database for PostgreSQL.

预估 autovacuum 的成本Estimate the cost of autovacuum

运行 autovacuum 的成本比较高昂,有一些参数可以控制清扫操作的运行时。Running autovacuum is "costly," and there are parameters for controlling the runtime of vacuum operations. 以下参数有助于预估运行清扫作业的成本:The following parameters help estimate the cost of running vacuum:

  • vacuum_cost_page_hit = 1vacuum_cost_page_hit = 1
  • vacuum_cost_page_miss = 10vacuum_cost_page_miss = 10
  • vacuum_cost_page_dirty = 20vacuum_cost_page_dirty = 20

清扫进程读取物理页面并检查死元组。The vacuum process reads physical pages and checks for dead tuples. shared_buffers 中的每个页面产生的成本为 1 (vacuum_cost_page_hit)。Every page in shared_buffers is considered to have a cost of 1 (vacuum_cost_page_hit). 至于所有其他页面,如果存在死元组,则产生的成本为 20 (vacuum_cost_page_dirty),如果不存在死元组,则成本为 10 (vacuum_cost_page_miss)。All other pages are considered to have a cost of 20 (vacuum_cost_page_dirty), if dead tuples exist, or 10 (vacuum_cost_page_miss), if no dead tuples exist. 在进程超过 autovacuum_vacuum_cost_limit 时会停止清扫操作。The vacuum operation stops when the process exceeds the autovacuum_vacuum_cost_limit.

达到此限制后,进程会进入睡眠状态,在经过 autovacuum_vacuum_cost_delay 参数所指定的持续时间后,会再次启动。After the limit is reached, the process sleeps for the duration specified by the autovacuum_vacuum_cost_delay parameter before it starts again. 如果未达到该限制,会在经过 autovacuum_nap_time 所指定的时间后启动 autovacuum。If the limit isn't reached, autovacuum starts after the value specified by the autovacuum_nap_time parameter.

总之,autovacuum_vacuum_cost_delay 和 autovacuum_vacuum_cost_limit 参数用于控制每个时间单位允许清除多少数据。In summary, the autovacuum_vacuum_cost_delay and autovacuum_vacuum_cost_limit parameters control how much data cleanup is allowed per unit of time. 请注意,对于大多数定价层而言,默认值都过低。Note that the default values are too low for most pricing tiers. 这些参数的最佳值取决于不同的定价层,应根据定价层进行相应的配置。The optimal values for these parameters are pricing tier-dependent and should be configured accordingly.

autovacuum_max_workers 参数确定能同时运行的最大 autovacuum 进程数。The autovacuum_max_workers parameter determines the maximum number of autovacuum processes that can run simultaneously.

使用 PostgreSQL,可以在表级别或实例级别设置这些参数。With PostgreSQL, you can set these parameters at the table level or instance level. 目前,只能在 Azure Database for PostgreSQL 中的表级别设置这些参数。Today, you can set these parameters at the table level only in Azure Database for PostgreSQL.

优化每个表的 autovacuumOptimize autovacuum per table

可以针对每个表配置上述所有配置参数。You can configure all the previous configuration parameters per table. 下面是一个示例:Here's an example:

ALTER TABLE t SET (autovacuum_vacuum_threshold = 1000);
ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0.1);
ALTER TABLE t SET (autovacuum_vacuum_cost_limit = 1000);
ALTER TABLE t SET (autovacuum_vacuum_cost_delay = 10);

Autovacuum 一个针对每个表的同步进程。Autovacuum is a per-table synchronous process. 死元组在一个表中的占比越高,执行 autovacuum 的“成本”就越高。The larger percentage of dead tuples that a table has, the higher the "cost" to autovacuum. 可将更新和删除内容占比较高的表拆分为多个表。You can split tables that have a high rate of updates and deletes into multiple tables. 拆分表有助于并行化 autovacuum,并降低在一个表上完成 autovacuum 的“成本”。Splitting tables helps to parallelize autovacuum and reduce the "cost" to complete autovacuum on one table. 还可以增加并行 autovacuum 辅助角色,从而确保安排了充足的辅助角色。You also can increase the number of parallel autovacuum workers to ensure that workers are liberally scheduled.

后续步骤Next steps

若要详细了解如何使用和优化 autovacuum,请参阅以下 PostgreSQL 文档:To learn more about how to use and tune autovacuum, see the following PostgreSQL documentation: