PostgreSQL autovacuum 调优指南：深入分析常见问题与解决方案

你好，我是老 K，一个在数据库领域摸爬滚打了多年的老家伙。今天咱们聊聊 PostgreSQL 里面让人又爱又恨的 autovacuum。为啥说又爱又恨呢？因为这玩意儿能帮你自动清理表里的垃圾数据，保证数据库的性能，但有时候它也会抽风，导致数据库卡顿，甚至直接崩溃。遇到这种问题，估计你得抓狂吧？别慌，老 K 帮你捋一捋，带你彻底搞懂 autovacuum 的那些事儿。

1. autovacuum 是个啥？为啥这么重要？

首先，得搞清楚 autovacuum 到底是个啥。简单来说，它就是 PostgreSQL 的一个后台进程，负责自动清理和分析数据库中的表。它就像个勤劳的清洁工，定期检查表里的“垃圾”，比如已删除但还没被真正清理掉的行（dead tuples），以及表和索引的统计信息。清理垃圾是为了腾出空间，提高查询效率；更新统计信息则是为了让查询优化器能生成更好的执行计划。

autovacuum 的重要性体现在以下几个方面：

• 避免表膨胀： 随着数据的不断增删改，表中会产生大量的“垃圾”数据。如果不及时清理，表会越来越大，导致查询、更新速度变慢，甚至磁盘空间耗尽。
• 提高查询性能： autovacuum 会更新表的统计信息，这些统计信息对查询优化器至关重要。优化器会根据这些信息选择最佳的查询路径，从而提高查询性能。
• 维护数据完整性： 通过清理和分析，autovacuum 可以间接帮助维护数据完整性，例如，当事务回滚时，它会清理掉未提交的更改。
• 预防事务 ID 耗尽： PostgreSQL 使用事务 ID 来管理并发事务。autovacuum 可以防止事务 ID 耗尽，避免数据库因为事务 ID 溢出而出现问题。

2. 哪些原因会导致 autovacuum 无法及时运行或运行缓慢？

autovacuum 虽然好用，但有时候也会“罢工”，或者干活效率低下。以下是一些常见的原因，也是咱们排查问题的重点：

2.1 并发事务阻塞

如果数据库里有很多长时间运行的事务，尤其是那些持有表锁的事务，就会阻塞 autovacuum 的运行。因为 autovacuum 在清理和分析表的时候，也需要获取表的锁。如果锁被其他事务占用了，autovacuum 就只能干等着，或者被取消。

症状：

• autovacuum 进程长时间处于等待状态。
• pg_stat_activity 视图中，可以看到 autovacuum 进程被其他事务阻塞。
• 数据库性能下降，查询响应变慢。

排查方法：

1. 查看当前活动连接：

   SELECT pid, usename, client_addr, client_port, application_name, state, query
   FROM pg_stat_activity
   WHERE state != 'idle' AND pid <> pg_backend_pid();

   这条 SQL 可以帮你找到当前正在运行的连接，以及它们的状态和正在执行的 SQL 语句。重点关注那些运行时间长、持有表锁的事务。

2. 查看锁信息：

   SELECT blocked_locks.pid     AS blocked_pid,
          blocked_activity.usename  AS blocked_user,
          blocked_activity.query    AS blocked_query,
          blocking_locks.pid     AS blocking_pid,
          blocking_activity.usename AS blocking_user,
          blocking_activity.query   AS blocking_query
   FROM   pg_catalog.pg_locks         blocked_locks
   JOIN   pg_catalog.pg_stat_activity blocked_activity  ON blocked_locks.pid = blocked_activity.pid
   JOIN   pg_catalog.pg_locks         blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
   AND blocked_locks.database = blocking_locks.database
   AND blocked_locks.relation = blocking_locks.relation
   AND blocked_locks.pid != blocking_locks.pid
   JOIN   pg_catalog.pg_stat_activity blocking_activity ON blocking_locks.pid = blocking_activity.pid
   WHERE NOT blocked_locks.granted;

   这条 SQL 可以帮你找到阻塞 autovacuum 的进程，以及它阻塞的原因。重点关注 blocked_query 和 blocking_query，它们分别表示被阻塞的 SQL 语句和阻塞其他 SQL 语句的 SQL 语句。

解决方案：

• 优化长时间运行的事务： 尽量缩短事务的执行时间，例如，拆分大的事务为小的事务，或者优化 SQL 语句，减少锁的持有时间。
• 手动清理或分析： 如果确实需要长时间运行的事务，可以考虑手动执行 VACUUM 和 ANALYZE 命令，避免让 autovacuum 等待太久。
• 调整 autovacuum_vacuum_cost_delay 和 autovacuum_vacuum_cost_limit： 这两个参数可以控制 autovacuum 的 I/O 消耗。适当增加它们的值，可以让 autovacuum 在不影响其他事务的情况下运行。

2.2 autovacuum 参数配置不当

autovacuum 的行为可以通过一系列参数进行配置。如果这些参数设置不合理，也会导致 autovacuum 无法正常工作。以下是一些关键的参数：

• autovacuum： 启用或禁用 autovacuum。默认是开启的。如果禁用了，那数据库就没人清理了，等着爆炸吧！
• autovacuum_max_workers： autovacuum 进程的最大数量。默认是 3。如果你的 CPU 核心数很多，数据量也很大，可以适当增加这个值，提高 autovacuum 的并发处理能力。
• autovacuum_naptime： autovacuum 进程的休眠时间，单位是秒。默认是 1 分钟。这个参数决定了 autovacuum 检查数据库的频率。如果你的数据更新很频繁，可以适当缩短这个时间。
• autovacuum_vacuum_threshold： 触发 VACUUM 的最小 dead tuples 数量。默认是表行数的 10%。如果你的表数据量很大，可以适当增加这个值，减少 autovacuum 的运行频率。
• autovacuum_analyze_threshold： 触发 ANALYZE 的最小行数。默认是表行数的 50%。如果你的表数据量很大，可以适当增加这个值，减少 ANALYZE 的运行频率。
• autovacuum_vacuum_scale_factor： 触发 VACUUM 的 dead tuples 数量占表行数的比例。默认是 0.2。可以理解为：VACUUM 的触发条件是 autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * 表的行数。
• autovacuum_analyze_scale_factor： 触发 ANALYZE 的行数占表行数的比例。默认是 0.1。可以理解为：ANALYZE 的触发条件是 autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * 表的行数。
• autovacuum_vacuum_cost_delay： VACUUM 操作的 I/O 消耗的延迟时间，单位是毫秒。默认是 2 毫秒。这个参数可以控制 autovacuum 的 I/O 消耗，避免它影响其他事务。适当增加这个值，可以降低 autovacuum 的 I/O 压力。
• autovacuum_vacuum_cost_limit： VACUUM 操作的 I/O 消耗的限制，单位是 cost units。默认是 200。这个参数和 autovacuum_vacuum_cost_delay 配合使用，控制 autovacuum 的 I/O 消耗。适当增加这个值，可以提高 autovacuum 的 I/O 效率。

症状：

• autovacuum 运行频率太低，导致表膨胀。
• autovacuum 运行频率太高，导致数据库 CPU 或 I/O 负载过高。
• autovacuum 运行时间过长，导致数据库卡顿。

排查方法：

1. 查看 postgresql.conf 文件： 检查 autovacuum 相关参数的配置情况。看看是不是设置得太保守或者太激进了。

2. 使用 pg_settings 视图： 查询数据库的参数配置，可以查看每个参数的当前值，以及是否被修改过。

   SELECT name, setting, source FROM pg_settings WHERE name LIKE 'autovacuum%';
   

3. 监控 autovacuum 的运行情况： 使用 pg_stat_all_tables 视图或者其他监控工具，查看表的 autovacuum 统计信息，比如 n_dead_tup (dead tuples 数量), last_autovacuum (最后一次 autovacuum 时间), last_autovacuum_time (最后一次 autovacuum 耗时)等。通过这些信息，可以判断 autovacuum 是否在正常运行，以及运行效率如何。

解决方案：

• 根据实际情况调整参数： 根据数据库的负载、数据更新频率、表大小等因素，合理调整 autovacuum 的参数。没有一套通用的参数配置，需要根据实际情况进行调整和优化。

• 针对特定表进行配置： 除了全局的 autovacuum 参数，你还可以针对单个表进行配置。例如，对于更新频繁的表，可以降低 autovacuum_vacuum_threshold 和 autovacuum_vacuum_scale_factor，提高 autovacuum 的运行频率。

  ALTER TABLE table_name SET (autovacuum_vacuum_threshold = 10000);
  ALTER TABLE table_name SET (autovacuum_vacuum_scale_factor = 0.1);

2.3 资源限制

如果数据库服务器的 CPU、内存、I/O 等资源不足，也会导致 autovacuum 运行缓慢。因为 autovacuum 需要消耗大量的 CPU 和 I/O 资源来清理和分析表。

症状：

• CPU 使用率过高。
• 内存使用率过高，出现 swap。
• 磁盘 I/O 负载过高。
• autovacuum 进程长时间处于运行状态。

排查方法：

1. 监控系统资源： 使用 top、iotop、free 等工具，监控服务器的 CPU、内存、I/O 等资源的使用情况。

2. 查看 pg_stat_activity 视图： 查看 autovacuum 进程的 CPU 和 I/O 消耗情况。

   SELECT pid, usename, application_name, state, query, backend_cpu_time, backend_wait_event, backend_wait_event_type
   FROM pg_stat_activity
   WHERE application_name = 'autovacuum';

   backend_cpu_time 表示后端进程的 CPU 时间，backend_wait_event 和 backend_wait_event_type 表示后端进程的等待事件。如果 autovacuum 进程的 CPU 使用率很高，或者经常处于等待状态，说明资源可能存在瓶颈。

解决方案：

• 升级服务器硬件： 如果资源确实不足，可以考虑升级服务器的 CPU、内存、磁盘等硬件。
• 优化数据库配置： 调整 autovacuum 的参数，减少其资源消耗。例如，适当增加 autovacuum_vacuum_cost_delay 和 autovacuum_vacuum_cost_limit，限制 autovacuum 的 I/O 消耗。
• 优化数据库 schema 和 SQL： 优化表的 schema 和 SQL 语句，减少对资源的消耗。例如，避免使用过大的表，避免使用全表扫描的 SQL 语句。

2.4 数据库对象问题

数据库中的某些对象，比如大事务、未清理的临时表、或者损坏的索引，也可能导致 autovacuum 运行缓慢。

症状：

• autovacuum 运行时间过长，或者频繁失败。
• 数据库出现锁等待，或者死锁。

排查方法：

1. 检查大事务： 使用 pg_stat_activity 视图，查找长时间运行的事务。

   SELECT pid, usename, application_name, state, query, now() - query_start AS runtime
   FROM pg_stat_activity
   WHERE state != 'idle' AND now() - query_start > interval '10 minutes';

   这条 SQL 可以帮你找到运行时间超过 10 分钟的事务。根据实际情况调整时间间隔。

2. 检查未清理的临时表： 临时表创建后，如果没有被正确清理，会占用磁盘空间，导致 autovacuum 运行缓慢。可以通过查询 pg_catalog.pg_class 视图，找到未被删除的临时表。

   SELECT relname, relkind, relpersistence
   FROM pg_catalog.pg_class
   WHERE relpersistence = 't';

   relpersistence = 't' 表示临时表。你需要根据实际情况判断这些临时表是否应该存在。

3. 检查索引： 损坏的索引会影响查询性能，也会导致 autovacuum 运行缓慢。可以使用 REINDEX 命令重建索引。

   REINDEX INDEX index_name;
   

   或者重建整个表上的索引：

   REINDEX TABLE table_name;
   

解决方案：

• 优化事务管理： 避免长时间运行的事务，及时提交或回滚事务。
• 清理临时表： 确保临时表在使用完毕后被正确删除。
• 重建索引： 定期检查和重建索引，确保索引的健康状态。

3. autovacuum 调优实战：案例分析与参数调整建议

光说不练假把式，下面咱们结合实际案例，聊聊如何进行 autovacuum 调优。

案例 1：表膨胀导致查询缓慢

问题描述：

用户反馈，数据库中的 orders 表查询越来越慢，尤其是在查询历史订单的时候。经过排查，发现 orders 表的 n_dead_tup 指标很高，说明表里存在大量的“垃圾”数据。

排查过程：

1. 查看 orders 表的统计信息：

   SELECT relname, n_dead_tup, last_autovacuum, last_analyze
   FROM pg_stat_all_tables
   WHERE relname = 'orders';

   结果显示，n_dead_tup 很高，但 last_autovacuum 很久没有更新了。

2. 检查 autovacuum 参数：

   SELECT name, setting, source
   FROM pg_settings
   WHERE name LIKE 'autovacuum%';

   发现全局的 autovacuum 参数配置比较保守，比如 autovacuum_vacuum_scale_factor 设置为 0.2，autovacuum_vacuum_threshold 设置为 50。对于一个数据量比较大的表来说，这个触发条件可能太高了。

3. 手动执行 VACUUM：

   VACUUM VERBOSE ANALYZE orders;
   

   手动执行 VACUUM 命令后，orders 表的查询性能明显提升。

解决方案：

1. 针对 orders 表，调整 autovacuum 参数：

   ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.1);
   ALTER TABLE orders SET (autovacuum_vacuum_threshold = 10000);

   适当降低 autovacuum_vacuum_scale_factor，并增加 autovacuum_vacuum_threshold，让 autovacuum 更频繁地清理 orders 表。

2. 监控 autovacuum 运行情况： 使用 pg_stat_all_tables 视图，持续监控 orders 表的 n_dead_tup 指标，以及 last_autovacuum 和 last_analyze 的时间，确保 autovacuum 能够及时清理和分析。

案例 2：autovacuum 导致 CPU 负载过高

问题描述：

数据库服务器 CPU 使用率经常飙升到 100%，导致其他业务受到影响。经过排查，发现 autovacuum 进程占用了大量的 CPU 资源。

排查过程：

1. 监控系统资源： 使用 top 命令，发现 autovacuum 进程的 CPU 使用率很高。

2. 查看 pg_stat_activity 视图：

   SELECT pid, usename, application_name, state, query, backend_cpu_time
   FROM pg_stat_activity
   WHERE application_name = 'autovacuum';

   结果显示，autovacuum 进程的 backend_cpu_time 很高，说明它确实占用了大量的 CPU 资源。

3. 检查 autovacuum 参数：

   SELECT name, setting, source
   FROM pg_settings
   WHERE name LIKE 'autovacuum%';

   发现 autovacuum_max_workers 设置为 3，可能有点多。因为服务器的 CPU 核心数不多，过多的 autovacuum 进程会争抢 CPU 资源。

解决方案：

1. 降低 autovacuum_max_workers：

   ALTER SYSTEM SET autovacuum_max_workers = 2;
   

   降低 autovacuum_max_workers 的值，减少并发的 autovacuum 进程数量，减轻 CPU 负载。

2. 调整 autovacuum_vacuum_cost_delay 和 autovacuum_vacuum_cost_limit：

   ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '10ms';
   ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 400;

   适当增加 autovacuum_vacuum_cost_delay 和 autovacuum_vacuum_cost_limit，限制 autovacuum 的 I/O 消耗，避免它过度占用 CPU 资源。

3. 优化 SQL 语句： 检查数据库中的 SQL 语句，看看是否有可以优化的部分，例如，避免使用全表扫描的 SQL 语句，使用索引等。

参数调整建议：

• autovacuum_max_workers： 根据 CPU 核心数和数据库负载情况进行调整。一般来说，可以设置为 CPU 核心数的 1-2 倍。但是，也要考虑 I/O 压力。如果 I/O 压力大，可以适当降低这个值。
• autovacuum_naptime： 根据数据更新的频率进行调整。如果数据更新频繁，可以缩短这个时间，比如设置为 30 秒或者更短。
• autovacuum_vacuum_threshold 和 autovacuum_vacuum_scale_factor： 根据表的大小和数据更新频率进行调整。对于大表，可以适当增加 autovacuum_vacuum_threshold，减少 autovacuum 的运行频率。对于更新频繁的表，可以适当降低 autovacuum_vacuum_scale_factor，提高 autovacuum 的运行频率。
• autovacuum_analyze_threshold 和 autovacuum_analyze_scale_factor： 与 VACUUM 类似，根据表的大小和数据更新频率进行调整。
• autovacuum_vacuum_cost_delay 和 autovacuum_vacuum_cost_limit： 根据 I/O 负载情况进行调整。如果 I/O 负载高，可以适当增加 autovacuum_vacuum_cost_delay 和 autovacuum_vacuum_cost_limit，限制 autovacuum 的 I/O 消耗。

重要提示：

• 不要盲目照抄别人的配置。 每个数据库的实际情况都不一样，需要根据实际情况进行调整和优化。
• 逐步调整，观察效果。 每次调整一个参数，观察数据库的性能变化，然后根据效果再进行调整。不要一次性调整多个参数，这样不利于找到最佳的配置。
• 监控是关键。 使用监控工具，持续监控数据库的性能指标，及时发现问题，并进行调整。

4. 总结

autovacuum 是 PostgreSQL 中非常重要的一个特性，它能自动清理和分析表，保证数据库的性能和数据完整性。但是，如果配置不当，或者遇到其他问题，autovacuum 也会成为数据库性能的瓶颈。希望通过今天的分享，你能够更深入地理解 autovacuum 的工作原理，掌握排查问题和调优的方法，让你的 PostgreSQL 数据库跑得更顺畅！记住，没有一劳永逸的配置，只有持续的观察和调整。加油，老铁！