postgresql xid回卷预防及排查
监控
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
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
排查
# 查看每个库的年龄 SELECT datname, age(datfrozenxid) FROM pg_database;
SELECT c.oid::regclass, age(c.relfrozenxid), pg_size_pretty(pg_total_relation_size(c.oid))FROM pg_class cJOIN pg_namespace n on c.relnamespace = n.oidWHERE relkind IN ('r', 't', 'm')AND n.nspname NOT IN ('pg_toast')ORDER BY 2 DESC LIMIT 100;
# 1个库每个表的年龄排序 SELECT c.oid::regclass as table_name, greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHERE c.relkind IN ('r', 'm') order by age desc;
# 查看1个表的年龄select oid::regclass,age(relfrozenxid) from pg_class where oid='schema名称.表名称'::regclass::oid;
#这查询按照最老的XID排序，查看大于1G而且是排名前20的表：
select relname, age(relfrozenxid) as xid_age, pg_size_pretty(pg_table_size(oid)) as table_sizefrom pg_class where relkind = 'r' and pg_table_size(oid) > 1073741824order by xid_age desc limit 20;--vacuum前事务年龄为 61436 relname | xid_age | table_size----------------+---------+------------ test_tab | 31260 | 4327 MB
#通过以下语句可以查找出age年龄大于vacuum_freeze_table_age的表：
select datname,age(datfrozenxid) from pg_database where datname not in ('postgres','template0','template1') and age(datfrozenxid)>(select setting::int from pg_settings where name='vacuum_freeze_table_age')order by age(datfrozenxid) desc;
运维脚本
# 对指定数据库中年龄最大的前 50 张表进行 vacuum freeze
for cmd in `psql -U用户名 -p端口号 -h连接串 -d数据库名 -c "SELECT 'vacuum freeze '||c.oid::regclass||';' as vacuum_cmd FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHERE c.relkind IN ('r', 'm') order by greatest(age(c.relfrozenxid),age(t.relfrozenxid)) desc offset 50 limit 50;" | grep -v vacuum_cmd | grep -v row | grep vacuum`; do
python脚本
from multiprocessing import Pool
import psycopg2
args = dict(host='pgm-bp10xxxx.pg.rds.aliyuncs.com', port=5432, dbname='数据库名',
user='用户名', password='密码')
def vacuum_handler(sql):
sql_str = "SELECT c.oid::regclass as table_name, greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHERE c.relkind IN ('r', 'm') order by age desc limit 10; "
try:
conn = psycopg2.connect(**args)
cur = conn.cursor()
cur.execute(sql)
conn.commit()
cur = conn.cursor()
cur.execute(sql_str)
print cur.fetchall()
conn.close()
except Exception as e:
print str(e)
# 对指定数据库中年龄最大的前 1000 张表进行 vacuum freeze，32 个进程并发执行
def multi_vacuum():
pool = Pool(processes=32)
sql_str = "SELECT 'vacuum freeze '||c.oid::regclass||';' as vacuum_cmd FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHERE c.relkind IN ('r', 'm') order by greatest(age(c.relfrozenxid),age(t.relfrozenxid)) desc limit 1000;";
try:
conn = psycopg2.connect(**args)
cur = conn.cursor()
cur.execute(sql_str)
rows = cur.fetchall()
for row in rows:
cmd = row['vacuum_cmd']
pool.apply_async(vacuum_handler, (cmd, ))
conn.close()
pool.close()
pool.join()
except Exception as e:
print str(e)
multi_vacuum()
