在PostgreSQL中管理事务ID耗尽（回绕）

在管理PostgresSQL数据库时，需要了解的最关键的主题之一是事务ID（TXID）的概念，如果没有正确监控，它们可能会被耗尽。这篇博文将要介绍的是一种简单的方法来监控它，以及可以做些什么来防止它成为一个问题。

监控

大多数人最初意识到这个问题，是监控到了 TXID 自身回绕，但从技术上讲，TXID 的耗尽才是真正的问题。PostgreSQL在技术上能够很好地处理 TXID 值。然而，如果达到回绕点，并且 TXID 接近用完，那么这将会是一个令人担忧的问题。

下面的查询是我们在 PGmonitor 工具的 Crunchy Data 中使用的一个查询，用于提供非常简单的数据点以进行趋向分析/警报。

WITH max_age AS ( 
   SELECT 2000000000 as max_old_xid
       , setting AS autovacuum_freeze_max_age 
       FROM pg_catalog.pg_settings 
       WHERE name = 'autovacuum_freeze_max_age' )
, per_database_stats AS ( 
   SELECT datname
       , m.max_old_xid::int
       , m.autovacuum_freeze_max_age::int
       , age(d.datfrozenxid) AS oldest_current_xid 
   FROM pg_catalog.pg_database d 
   JOIN max_age m ON (true) 
   WHERE d.datallowconn ) 
SELECT max(oldest_current_xid) AS oldest_current_xid
   , max(ROUND(100*(oldest_current_xid/max_old_xid::float))) AS percent_towards_wraparound
   , max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float))) AS percent_towards_emergency_autovac 
FROM per_database_stats

percent_towards_wraparound指标对于警报的设置非常重要。查询使用 age()
函数来确定 TXID 值，因此需要考虑它们是否真的处于耗尽状态，以确定回绕是否是一个真正存在的问题。如果耗尽，数据库将被迫关闭，并可能为了修复而导致停机时间的不确定。这个查询中存在一点缓冲，因为它检查的上限（确切地说是20亿）小于导致耗尽的实际最大整数值。但这已经足够接近了，达到100%的警报应该立即采取行动。

percent_towards_emergency_autovac指标是我们建议监测的附加值，特别是对于以前从未监测过此指标的系统（有关何时可以降低或删除此警报优先级，请参阅下面有关近期冻结成效的说明）。这将监视数据库的最高 TXID 值是否达到autovacuum_freeze_max_age。这是一个用户可调值，默认值为2亿，当任何表的最高 TXID 值达到该值时，该表上会出现更高优先级的autovacuum
。您将认识到这个特殊的vacuum
会话，因为在pg_stat_activity
中它将被标记（以防止回绕）。它的优先级更高，即使禁用autovacuum
，它也会运行，如果手动取消vacuum
，它几乎会立即重新启动。它还需要一些不同的内部低级锁，因此它可能会导致这些表上的争用稍微更高，这取决于它们在紧急vacuum
期间的使用方式。如果您确实遇到争用/锁的问题，并且是紧急vacuum
造成的，则完全可以安全地取消争用/锁，以允许其他事务完成。请注意，它将继续重新启动，直到vacuum
能够成功完成或手动运行vacuum
。

对于事务速率较高的数据库，增加autovacuum_freeze_max_age
可能有助于避免频繁出现紧急vacuum
。增加这这个值的主要问题是，它会增加数据目录中pg_xact
和pg_commit_ts
文件夹中的存储。我经常将该值设置为10亿，没有太多问题，但只有在我确定正在监视回绕并且磁盘空间可用时才这样做。

那么，当这些警报中的任何一个发出时，您如何修复它？

简单修复

获取最高 TXID 年龄的最简单（但不一定是最快）方法是强制整个数据库集群处于vacuum
状态。实现集群范围vacuum
的最佳方法是PostgreSQL附带的vacuumdb
程序。

vacuumdb --all --freeze --jobs=2 --echo --analyze

–all
选项确保所有数据库都被vacuum
，因为 TXID 是一个全局值。–freeze
选项确保运行更积极的vacuum
，以确保在该表中冻结尽可能多的元组。–jobs=2
允许多个vacuum
并行运行。这应该设置为系统能够处理的最高值，以加快速度，但请小心设置过高，因为它会导致额外的IO和更快的WAL生成（增加磁盘使用量）。–echo
只是提供一些最小的反馈，这样你就可以看到一些进展。–analyze
确保统计数据得到更新。如果需要时间来完成此vacuum
的运行，可以停止此操作，稍后使用 –analyze only
选项作为单独的步骤运行。

近期冻结成效

我将在这里提到的 –freeze
选项的另一个好处是，在未来的 vacuum
操作中，可以大大减少IO和WAL的生成。PostgreSQL 9.6引入了一项功能，允许vacuum
在页面中的所有元组都标记为冻结时跳过页面。PG11在这一点上对此功能用于索引做了更多改进。因此，如果有很多旧表不再进行写操作，这就使得当出于任何原因需要对它们进行vacuum
时，会是一个消耗非常少的操作。这也使得“percent_towards_emergency_autovac”警报不那么令人担忧，因为它不会像预期的活动高峰那么严重。所以一旦你把事情调整好了，你可以认为这个警告是一个低优先级的警告，或者甚至删除它，只需自身回绕的监控。

每表修复

如果确实你已经到达了回绕或者正在接近回绕并且您负担不起集群范围内的vacuum
，并且希望尽快控制 TXID 年龄，那么这当然是可能的，但所涉及的步骤不仅仅是调用单个二进制命令。

首先要检查的是每个数据库的高 TXID 年龄，以确定哪些需要注意。下面的查询提供了当前的autovacuum_freeze_max_age
值，以供比较。

SELECT datname
   , age(datfrozenxid)
   , current_setting('autovacuum_freeze_max_age') 
FROM pg_database 
ORDER BY 2 DESC;


datname  |   age    | current_setting 
-----------+-----------+-----------------
postgres  | 170604895 | 200000000
mydb      | 169088197 | 200000000
template0 | 142915024 | 200000000
template1 | 142914999 | 200000000

在这里，您可以看到默认的 postgres 数据库和 mydb 数据库是最接近紧急vacuum
的。谢天谢地，还差了一点。接下来，我们需要连接到相关数据库并查看表本身的状态。

postgres=# SELECT c.oid::regclass
   , age(c.relfrozenxid)
   , pg_size_pretty(pg_total_relation_size(c.oid)) 
FROM pg_class c
JOIN pg_namespace n on c.relnamespace = n.oid
WHERE relkind IN ('r', 't', 'm') 
AND n.nspname NOT IN ('pg_toast')
ORDER BY 2 DESC LIMIT 100;


                  oid                     |   age    | pg_size_pretty 
--------------------------------------------+-----------+----------------
pg_proc                                    | 170606208 | 936 kB
pg_description                             | 170606208 | 480 kB
pg_depend                                  | 109192353 | 1336 kB
pg_attribute                               | 109192353 | 728 kB
pg_shdepend                                |  89774141 | 2976 MB
pg_db_role_setting                         |  77594841 | 16 kB
pg_tablespace                              |  77594841 | 72 kB
pg_pltemplate                              |  77594841 | 56 kB
pg_auth_members                            |  77594841 | 16 kB
pg_authid                                  |  77594841 | 72 kB
[...]

在这里，您可以看到只有少数几个表具有较高的年龄，但在更现实的场景中，您可能会看到更多的高年龄表。下一步，我们将只对这些特定的表进行vacuum
。如果只是几个表，手动键入VACUUM
命令也没什么大不了的。但如果你有100+个表，那可能会很乏味，而且容易出错。因此，我们可以使用一些字符串连接和一些psql命令来生成一些语句，并将它们放在一个文件中，然后为我们自动运行。

\t
\o /tmp/vacuum.sql
select 'vacuum freeze analyze verbose ' || oid::regclass || ';' from pg_class where relkind in ('r', 't', 'm') order by age(relfrozenxid) desc limit 100;
\o
\t
\set ECHO all
\i /tmp/vacuum.sql

我强烈建议查看psql文档 ，了解所有这些斜杠命令的作用，基本上面代码的作用是：

• 关闭列标题

• 将之后的所有输出发送到/tmp/vacuum.sql文件

• 为查询返回的前100个表生成VACUUM 语句

• 关闭文件输出并重新打开列标题

• 输出在此之后运行的每个命令

• 一个个运行vacuum.sql 文件中包含的vacuum语句

即使有100多张表需要清理，我通常一批做100次，只是为了给IO和WAL一些时间冷静下来。我通常会尝试将任何表的最大XID降到autovacuum_freeze_max_age
的50%，如果不太麻烦的话，通常甚至降到30-40%。还要注意，您必须登录每个数据库才能运行这些VACUUM
命令。如果需要，您可以登录template1进行修复，但您将无法登录template0。template0可以安全地被忽略，并让它运行到autovacuum_freeze_max_age
，因为它非常小，几乎可以在瞬间完成。

结论

如果你是PG9.6及之后的版本，你可能很少需要关心这种人工干预，而且希望只有一次。因此，一旦您正确调整了autovacuum
，并且旧的静态数据标记为冻结，达到autovacuum_freeze_max_age
就不再是一个真正的问题，因为：

• 您经常使用的表在最好的点进行vacuum
  

• 您的静态表已标记为冻结，vacuum
  可以跳过它们

这意味着所有你需要担心的是监控的实际耗竭。除非数据库的使用模式发生变化，否则这可能不再是一个问题，但无论发生什么情况，都应该始终对其进行监控，因为如果到达回绕，那么会引起数据库的中断，你将会没有任何准备。