VACUUM和ANALYZE最佳实践技巧

概要

VACUUM 和 ANALYZE 是PostgreSQL 数据库两个最重要的维护操作。

VACUUM 用于回收表中“死元组”占用的空间。当删除或更新记录（先删除后插入）时，会生成死元组。PostgreSQL 不会从表中物理删除旧行，而是在其上放置一个“标记”，以便查询不会返回该行。当 vacuum 进程运行时，这些死元组占用的空间被标记为可由其他元组重用。

ANALYZE操作顾名思义，它分析数据库表的内容并收集有关每个表的每一列中值分布的统计信息。PostgreSQL 查询引擎使用这些统计信息来找到最佳查询计划。随着数据库中行的插入、删除和更新，列统计信息也会发生变化。ANALYZE由 DBA 手动运行或在 autovacuum之后由 PostgreSQL 自动运行，确保统计信息是最新的。

虽然它们听起来相对简单，但在幕后，VACUUM 和 ANALYZE 是两个复杂的过程。幸运的是，DBA 不必太担心他们的内部结构。但是，他们常常对手动运行这些过程或为配置参数设置最佳值感到困惑。

在本文中，我们将分享一些 VACUUM 和 ANALYZE 的最佳实践。

1 不要无缘无故地运行手动 VACUUM 或 ANALYZE

PostgreSQL 清理（自动清理或手动清理）可最大限度地减少表膨胀并防止事务 ID 回绕。autovacuum 不恢复死元组占用的磁盘空间。但是，运行VACUUM FULL命令会这样做。不过，VACUUM FULL 对性能有影响。目标表在操作期间被独占锁定，甚至无法读取表。该进程还会制作表的完整副本，这在运行时需要额外的磁盘空间。我们建议不要运行 VACUUM FULL，除非膨胀率非常高，并且查询受到严重影响。我们还建议为其使用在数据库活动最少的时期。

最好不要在整个数据库上过于频繁地运行手动清理，目标数据库可能已经通过 autovacuum 进程进行了最佳清理。因此，手动清理可能不会删除任何死元组，但会导致不必要的 I/O 负载或 CPU 峰值。如有必要，手动清理应仅在需要时逐个表运行，例如活动行与死行的比率较低，或者自动清理之间存在较大差距。此外，应在用户活动最少时运行手动清理。

autovacuum 还使表的数据分布统计信息保持最新（它不会重建它们）。手动运行时，ANALYZE命令实际上重建这些统计信息而不是更新它们。同样，在统计数据已经由常规 autovacuum 优化更新时，重建统计数据可能会对系统资源造成不必要的压力。

必须手动运行 ANALYZE 的时间是在将数据批量加载到目标表之后。现有表中的大量（甚至几百个）新行将显着扭曲其列数据分布。新行将导致任何现有的列统计信息过时。当查询优化器使用此类统计信息时，查询性能可能会非常慢。在这些情况下，在数据加载后立即运行 ANALYZE 命令以完全重建统计信息是比等待 autovacuum 启动更好的选择。

2 微调 Autovacuum 阈值

必须检查或调整 autovacuum 并分析postgresql.conf文件或单个表属性中的配置参数，以在 autovacuum 和性能增益之间取得平衡。

PostgreSQL 使用两个配置参数来决定何时启动 autovacuum：

autovacuum_vacuum_threshold 	# 默认值为 50
autovacuum_vacuum_scale_factor 	# 默认值为 0.2

这些参数一起告诉 PostgreSQL 在表中的死行数超过该表中的行数乘以比例因子加上vacuum阈值时启动 autovacuum。换句话说，PostgreSQL 将在以下情况下对表启动 autovacuum：

pg_stat_user_tables.n_dead_tup > (pg_class.reltuples x autovacuum_vacuum_scale_factor ) + autovacuum_vacuum_threshold

对于中小型表，这可能就足够了。例如，一个有 10,000 行的表，在 autovacuum 开始之前，死行数必须超过 2,050（（10,000 x 0.2）+ 50）。

并非数据库中的每个表都经历相同的数据修改率。通常，一些大表会经历频繁的数据修改，因此会产生更多的死行。默认值可能不适用于此类表。例如，使用默认值，一个有 100 万行的表在 autovacuum 开始之前需要有超过 200,050 个死行 ((1000,000 x 0.2) + 50)。这可能意味着 autovacuum 之间的间隔更长，autovacuum 时间越来越长，更糟糕的是，如果表上的活动事务正在锁定它，autovacuum 根本不会运行。

因此，目标应该是将这些阈值设置为最佳值，以便 autovacuum 可以定期发生并且不会花费很长时间（并且影响用户会话），同时保持死行的数量相对较低。

一种方法是使用一个或另一个参数。因此，如果我们将 autovacuum_vacuum_scale_factor 设置为 0 而不是将 autovacuum_vacuum_threshold 设置为 5,000，那么当表的死行数超过 5,000 时，该表将自动清理。

3 微调 Autoanalyze 阈值

与 autovacuum 类似，autoanalyze 也使用两个参数来决定 autovacuum 何时也会触发自动分析：

autovacuum_analyze_threshold	 # 默认值为 50
autovacuum_analyze_scale_factor	 # 默认值为 0.1

与 autovacuum 一样，autovacuum_analyze_threshold 参数可以设置为一个值，该值指示在自动分析开始之前表中插入、删除或更新的元组的数量。我们建议在大型和高事务表上单独设置此参数。表配置将覆盖 postgresql.conf 值。

下面的代码片段显示了用于修改表的 autovacuum_analyze_threshold 设置的 SQL 语法。

ALTER TABLE <table_name> 
SET (autovacuum_analyze_threshold = <threshold rows>)

4 微调 Autovacuum Workers

另一个经常被 DBA 忽略的参数是autovacuum_max_workers ，它的默认值为 3。autovacuum 不是一个单独的进程，而是许多并行运行的独立 vacuum 线程。指定多个工作人员的原因是为了确保清理大表不会阻碍清理较小的表和用户会话。autovacuum_max_workers 参数告诉 PostgreSQL 增加 autovacuum 工作线程的数量来进行清理。

PostgreSQL DBA 的一个常见做法是增加最大工作线程数，希望它能加快 autovacuum 的速度。这不起作用，因为所有线程共享相同的autovacuum_vacuum_cost_limit，其默认值为 200。使用以下公式为每个 autovacuum 线程分配一个成本限制：

单个线程的 cost_limit =  autovacuum_vacuum_cost_limit / autovacuum_max_workers

autovacuum 线程完成的工作成本使用三个参数计算：

• vacuum_cost_page_hit：默认值为 1
• vacuum_cost_page_miss：默认值为 10
• vacuum_cost_page_dirty：默认值为 20

这些参数的意思是这样的：

• 当 vacuum 线程在共享缓冲区中找到它应该清理的数据页时，成本为 1。
• 如果数据页不在共享缓冲区中，而是在操作系统缓存中，则成本将为 10。
• 如果由于 vacuum 线程必须删除死行而必须将页面标记为脏，则成本将为 20。

工作线程数量的增加将降低每个线程的成本限制。由于每个线程都被分配了一个较低的成本限制，它会在很容易达到成本阈值时更频繁地进入休眠状态，最终导致整个 vacuum 过程运行缓慢。我们建议将 autovacuum_vacuum_cost_limit 增加到更高的值，例如 2000，然后调整最大工作线程数。

更好的方法是仅在必要时为单个表调整这些参数。例如，如果大型事务表的 autovacuum 花费的时间太长，则该表可能会临时配置为使用其自己的 vacuum 成本限制和成本延迟。成本限制和延迟将覆盖 postgresql.conf 中设置的系统范围值。

下面的代码片段显示了如何配置单个表。

ALTER TABLE <table_name> SET (autovacuum_vacuum_cost_limit = <large_value>) 
ALTER TABLE <table_name> SET (autovacuum_vacuum_cost_delay = <lower_cost_delay>)

使用第一个参数将确保分配给表的 autovacuum 线程在进入休眠状态之前执行更多工作。降低autovacuum_vacuum_cost_delay也意味着线程休眠的时间更少。

最后的想法

如您所见，更改 VACUUM 和 ANALYZE 的配置参数很简单，但首先需要仔细观察。每个数据库在大小、流量模式和事务速率方面都不同。我们建议 DBA 在更改参数或推出手动清理/分析机制之前，首先收集有关其数据库的足够信息，这些信息可能是：

• 每个表中的行数
• 每张表的死元组数
• 每个表的最后一次 vacuum 时间
• 每个表最后一次 analyze 时间
• 每张表插入/更新/删除数据的速率
• autovacuum 为每个表花费的时间
• 关于表未被清理的警告
• 大多数关键查询的当前性能及其访问的表
• 手动清理/分析后相同查询的性能

从这里，DBA 可以选择一些“试验”表来开始优化。他们可以开始更改表的 vacuum/analyze 属性并检查性能。PostgreSQL 是一个智能数据库引擎，DBA 经常会发现最好让 PostgreSQL 进行清理和分析，而不是手动进行这些工作。