transaction ID wraparound 위험 측정(데이터베이스 및 테이블별 XID age)

명령어

-- Per database: distance to autovacuum_freeze_max_age and the ~2^31 limit
SELECT datname,
       age(datfrozenxid) AS xid_age,
       mxid_age(datminmxid) AS mxid_age,
       round(100.0 * age(datfrozenxid) / current_setting('autovacuum_freeze_max_age')::int, 1) AS pct_of_freeze_max_age,
       round(100.0 * age(datfrozenxid) / 2147483647, 1) AS pct_of_wraparound
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

-- Oldest tables in the current database (including TOAST)
SELECT c.oid::regclass AS table_name,
       greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS xid_age,
       mxid_age(c.relminmxid) AS mxid_age,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
       s.last_autovacuum,
       s.last_vacuum
FROM pg_class c
LEFT JOIN pg_class t ON t.oid = c.reltoastrelid
LEFT JOIN pg_stat_all_tables s ON s.relid = c.oid
WHERE c.relkind IN ('r', 'm')
ORDER BY 2 DESC
LIMIT 20;

하는 일

각 데이터베이스의 가장 오래된 unfrozen XID와 multixact age를 autovacuum_freeze_max_age 및 wraparound 한도 대비로 보여 준다. 현재 데이터베이스에서는 가장 오래된 테이블(TOAST 포함)을 크기와 마지막 vacuum 시각과 함께 보여 준다.

사용 시점

정기 모니터링(일반적 임계값: pct_of_wraparound 50% 경고 / 75% 위험), 로그에 'must be vacuumed within N transactions'가 나타날 때, freeze 작업의 우선순위를 정할 때.

주의

pct_of_freeze_max_age가 100을 넘는 테이블은 autovacuum이 의무적인(anti-wraparound) vacuum을 시작한다. I/O 부하가 크며 종료해도 다시 시작된다. age가 줄지 않으면 원인은 거의 항상 xmin horizon을 붙잡는 무언가(장시간 트랜잭션, prepared transaction, replication slot, hot_standby_feedback)이다. 이를 먼저 해결하지 않으면 VACUUM FREEZE로도 age를 낮출 수 없다. 한도에 가까워지면 서버는 먼저 경고하고, 수백만 개만 남으면 새 XID 할당(쓰기)을 거부한다. 대상 지정 freeze: VACUUM (FREEZE, VERBOSE) <table>;