监控 Autovacuum 的常用查询
Laurenz Albe约 7 分钟引言
在多年的培训、咨询和支持 PostgreSQL 用户的过程中，我总结了一些用于监控 autovacuum 的查询语句。监控 autovacuum 并不是一个新需求，因此已经有许多现成的监控查询。然而，并非所有这些查询都有用。所以我认为写一篇文章来介绍我自己的查询集合是一个好主意，既作为我自己的参考资料，也为各地的 PostgreSQL 管理员提供服务。
Autovacuum：承担多种任务的主力军
大多数人都知道 VACUUM 和 autovacuum 会清除 UPDATE 或 DELETE 留下的“死亡元组”（不再对任何人可见的行版本）。但这只是 autovacuum 众多任务之一。它会启动 VACUUM 来执行以下操作：
·从表和索引中移除死亡元组
·维护可见性映射（visibility map），以实现高效的仅索引扫描
·“冻结”旧的可见元组，以防止事务ID 回卷和多重事务ID 回卷
此外，autovacuum 还充当自动分析（autoanalyze）的角色：它会启动ANALYZE 来收集优化器统计信息，以保证良好的查询性能。
所有这些任务对于数据库的健康和SQL 语句的性能都是至关重要的。因此，我们应该监控 autovacuum 是否完成了所有工作。这样，我们可以在数据库出现问题之前对 autovacuum 进行调优。
监控 Autovacuum 清除死亡元组
让我们从 autovacuum 的基本功能——移除死亡元组开始。这种“垃圾回收”在死亡元组数量超过以下阈值时触发：
vacuum阈值= minimum(autovacuum_vacuum_max_threshold,autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor ×行数)
以下查询计算了一个“vacuum 紧迫度”(vacuum urgency)因子。当该因子超过 1 时，autovacuum 应该触发。
SELECT schemaname, relname, total_rows, n_dead_tup,/*避免除以零*/coalesce(n_dead_tup / nullif(least(max_threshold,total_rows * scale_factor + threshold),0),100.0 /*如果有效阈值为0，使用一个任意的高值*/) AS vacuum_urgency,last_autovacuumFROM (SELECT st.schemaname, st.relname, t.reltuples AS total_rows, st.n_dead_tup,coalesce(/*首先使用表的"relopt"设置*/min(split_part(ro.o, '=', 2))FILTER (WHERE split_part(ro.o, '=', 1) = 'autovacuum_vacuum_scale_factor'),/*回退到参数*/current_setting('autovacuum_vacuum_scale_factor'))::float8 AS scale_factor,coalesce(min(split_part(ro.o, '=', 2))FILTER (WHERE split_part(ro.o, '=', 1) = 'autovacuum_vacuum_threshold'),current_setting('autovacuum_vacuum_threshold'))::float8 AS threshold,coalesce(min(split_part(ro.o, '=', 2))FILTER (WHERE split_part(ro.o, '=', 1) = 'autovacuum_vacuum_max_threshold'),current_setting('autovacuum_vacuum_max_threshold', TRUE),'Infinity')::float8 AS max_threshold,st.last_autovacuumFROM pg_stat_all_tables AS stJOIN pg_class AS tON st.relid = t.oidLEFT JOIN LATERAL unnest(t.reloptions) AS ro(o)ON ro.o ~~ ANY ('{autovacuum_vacuum_scale_factor=%,autovacuum_vacuum_threshold=%,autovacuum_vacuum_max_threshold=%}')GROUP BY t.oid, st.schemaname, st.relname, t.reltuples, st.n_dead_tup, st.last_autovacuum) AS subqORDER BY vacuum_urgency DESC;
这个查询之所以如此复杂，是因为 autovacuum_vacuum_max_threshold 在 PostgreSQL v18 之前不存在，而且可以使用存储选项为单个表覆盖全局参数。
Autovacuum 未清除死亡元组的可能原因
如果紧迫度(vacuum urgency)因子远大于 1，可能表明以下情况：
·autovacuum 正在运行，但某些因素阻止了它进行清理
·工作负载产生死亡元组的速度快于autovacuum 清理它们的速度
·autovacuum 没有处理该表，因为 autovacuum_max_workers 设置得太小
·频繁的并发语句获取了与 VACUUM 的 SHARE UPDATE EXCLUSIVE 锁冲突的强锁（例如使用 LOCK 语句）
·数据损坏导致 autovacuum 在完成清理之前失败
监控表膨胀
上一节中的查询可以让我们看到是否有不合理数量的死亡元组在没有被autovacuum 清除的情况下累积。然而，即使有太多的死亡行，VACUUM 最终也可能清除它们。但这会在表内留下过多的空闲空间（“膨胀”）。PostgreSQL 不跟踪表中的空白空间，这使得监控该空闲空间变得困难。有一些监控查询试图猜测表中的空闲空间量，但我发现它们经常出错。监控膨胀的唯一可靠方法是使用pgstattuple 扩展：
SELECT t.table_name,s.free_percent,s.free_spaceFROM (SELECT c.oid::regclass AS table_name,CASE WHEN s.size > 163840THEN FALSEELSE TRUEEND AS tinyFROM pg_class AS cCROSS JOIN LATERAL pg_relation_size(c.oid) AS s(size)WHERE c.relkind = ANY (_char '{r,t}')/* prevent subquery flattening */OFFSET 0) AS tCROSS JOIN LATERAL pgstattuple(t.table_name) AS sWHERE NOT t.tinyORDER BY free_percent DESCLIMIT 20;
此查询列出了空闲空间百分比最高的20 个表。它忽略小表（少于20 个块），因为它们会扭曲统计数据。
*****译者注：上面查询的返回结果s.free_space的单位为bytes。*****
此查询将对所有表执行顺序扫描，因此您应该只在空闲时间偶尔运行它。您可以考虑使用pgstattuple_approx() 函数代替pgstattuple()，因为该函数只扫描表的一部分来获取近似结果。
监控自动分析
我们可以轻松修改监控“vacuum 紧迫度”(vacuum urgency)的查询，来报告需要进行自动ANALYZE 的表：
SELECT schemaname, relname, total_rows, n_mod_since_analyze,n_mod_since_analyze / (total_rows * scale_factor + threshold) AS analyze_urgency,last_autoanalyzeFROM (SELECT st.schemaname, st.relname, t.reltuples AS total_rows, st.n_mod_since_analyze,coalesce(min(split_part(ro.o, '=', 2))FILTER (WHERE split_part(ro.o, '=', 1) = 'autovacuum_analyze_scale_factor'),current_setting('autovacuum_analyze_scale_factor'))::float8 AS scale_factor,coalesce(min(split_part(ro.o, '=', 2))FILTER (WHERE split_part(ro.o, '=', 1) = 'autovacuum_analyze_threshold'),current_setting('autovacuum_analyze_threshold'))::float8 AS threshold,st.last_autoanalyzeFROM pg_stat_all_tables AS stJOIN pg_class AS tON st.relid = t.oid AND t.relkind = 'r' AND t.oid <> 2619LEFT JOIN LATERAL unnest(t.reloptions) AS ro(o)ON ro.o ~~ ANY ('{autovacuum_analyze_scale_factor=%,autovacuum_analyze_threshold=%}')GROUP BY t.oid, st.schemaname, st.relname, t.reltuples, st.n_mod_since_analyze, st.last_autoanalyze) AS subqORDER BY analyze_urgency DESC;
这一次，我们需要排除 TOAST 表和 pg_statistic 本身，因为它们不会被 ANALYZE。公式更简单，因为没有 autovacuum_analyze_max_threshold。请注意，autoanalyze 很少会给您带来麻烦：唯一现实的问题是，如果 autovacuum_max_workers 设置得太小，某些表可能会被“饿死”。
监控Autovacuum 维护可见性映射
对于高效的仅索引扫描来说，表的大部分页面在可见性映射中被标记为“全部可见”非常重要。这允许PostgreSQL 跳过确定元组可见性所需的堆访问。要检查可见性映射，您需要 pg_visibility 扩展。查询可能如下所示：
SELECT t.oid::regclass AS table_name,/*将空表报告为1.0 */coalesce(vm.all_visible::float8 / nullif((rs.size / 8192)::float8, 0),1.0) AS all_visible_factorFROM pg_class AS tCROSS JOIN LATERAL pg_visibility_map_summary(t.oid) AS vmCROSS JOIN LATERAL pg_relation_size(t.oid) AS rs(size)WHERE t.relkind = 'r'ORDER BY all_visible_factor;
您可能希望将监控范围限制在您已知需要高效仅索引扫描的表上。如果某个表的all_visible_factor 降得太低，您需要降低autovacuum_vacuum_scale_factor 或autovacuum_vacuum_insert_scale_factor（对于以INSERT 为主的表）。您还应该考虑是否有任何阻止autovacuum 清除死亡元组的原因在阻碍autovacuum 的工作。
监控Autovacuum 防止回卷问题
事务ID 回卷
监控事务ID 回卷的最简单方法是使用pg_database，PostgreSQL 在其中跟踪任何表中任何未冻结行使用的最旧事务ID：
SELECT datname, age(datfrozenxid) AS transaction_ageFROM pg_databaseORDER BY transaction_age DESC;
如果该年龄远大于 autovacuum_freeze_max_age 的值，说明某些因素阻止了防回卷 autovacuumworkers冻结某些元组。请查看前面列出的 autovacuum 未清除死亡元组的原因。但是请注意，防回卷 autovacuum workers不会因为其锁与用户语句冲突而退出。此外，即使 autovacuum 被禁用，autovacuum 也会启动防回卷 autovacuum workers。
多重事务ID 回卷
当多个事务或子事务锁定同一行时，PostgreSQL 会创建一个多重事务（multixact）。这些多重事务的标识符与事务ID 非常相似，生成它们的计数器也会回卷。大多数工作负载生成多重事务ID 的频率远低于事务ID，因此多重事务回卷通常不构成问题。但情况并非总是如此，因此您也应该监控多重事务ID 回卷：
SELECT datname, mxid_age(datminmxid) AS multixact_ageFROM pg_databaseORDER BY multixact_age DESC;
在这里，如果年龄远大于 autovacuum_multixact_freeze_max_age，您应该采取行动。可能的原因与上一节相同。
总结
我向您展示了我用来监控autovacuum 的查询。现在您要做的就是将它们集成到您最喜欢的监控系统中！
原文链接：https://www.cybertec-postgresql.com/en/monitor-autovacuum-my-queries/