HDUO Lab

标签： 数据库内核

  • 深入 PostgreSQL MVCC 机制：从 500GB 表膨胀看 Autovacuum 与 xmin 陷阱实战

    PG 核心表突发 500GB 膨胀，查询 P99 从 50ms 飙升至 3s。根因是长事务或废弃复制槽拖住了全局 xmin 视界，导致 Autovacuum 彻底失效，引发 Dead Tuples 堆积的“死亡螺旋”。本文拆解 PG 14 的 MVCC 与 VACUUM 底层机制，并给出防御性调优基线。

    现场与线索

    某次排查过程中，监控大盘开始疯狂告警：某 PG 14.5 生产集群磁盘使用率突破 85%，核心业务读写耗时严重劣化。登录主机查看系统负载，Load Average 飙升至 60+（32核机型），I/O Wait 居高不下。

    通过 pg_stat_user_tables 排查表状态，发现一张核心订单表 n_dead_tup（死元组数量）高达数千万，表物理体积从原本的 50GB 暴涨到了 550GB。

    显然，这是一次典型的表膨胀（Table Bloat）。立刻手动触发分析：

    VACUUM VERBOSE orders;

    终端很快返回了令人窒息的日志：

    INFO:  vacuuming "public.orders"
INFO:  "orders": found 0 removable, 45120485 nonremovable row versions in 702581 pages
DETAIL:  45120485 dead row versions cannot be removed yet, oldest xmin: 245189012

    关键信息在 0 removable 和 cannot be removed yet。Autovacuum 其实在正常调度，只是它无权清理这些死元组。

    为什么正常执行的 Autovacuum 无法回收死元组（Dead Tuples）？

    要解释这个问题，必须扒开 PostgreSQL 基于多版本并发控制（MVCC）的底层实现。

    在 PG 中，执行 UPDATE 或 DELETE 并不会原地修改或删除数据。UPDATE 实际上是 DELETE + INSERT。旧的数据行被称为 Dead Tuple。PG 在每行数据（Tuple）头部维护了两个核心隐藏字段：

    • t_xmin：插入/更新该元组的事务 ID。

    • t_xmax：删除/更新该元组的事务 ID（如果是活元组，此值为 0）。

    当 Autovacuum 扫描表时，它需要判断一个 Dead Tuple 是否可以被物理回收。判断的唯一法则基于 全局最小活跃事务 ID（Global Xmin Horizon）。 如果一个 Dead Tuple 的 t_xmax 大于或等于 当前系统中正在运行的最老活跃事务的 ID（也就是 oldest xmin），那么这个 Dead Tuple 绝对不能被清理。因为那个老事务如果执行查询，根据 MVCC 可见性规则，它依然需要读取这行“历史数据”。

    因此，一旦系统中出现“刺客”拖住了全局 xmin，哪怕你把 Autovacuum 调得再激进，也是徒劳。常见的“刺客”有三种：

    1. 长事务（Long Transactions）：代码里忘了 COMMIT 的 idle in transaction 状态。

    2. 废弃的逻辑复制槽（Abandoned Replication Slots）：下游消费端宕机，导致主库一直保留 WAL 和 xmin 视图。

    3. 两阶段提交的孤儿事务（Prepared Transactions）。

    揪出元凶

    执行以下 SQL 抓取系统中最老的事务或复制槽：

    -- 查长事务
SELECT pid, usename, state, backend_xmin, backend_xid, 
       age(backend_xmin) AS xmin_age, query, state_change
FROM pg_stat_activity 
WHERE backend_xmin IS NOT NULL 
ORDER BY age(backend_xmin) DESC LIMIT 5;

-- 查复制槽
SELECT slot_name, plugin, slot_type, active, xmin, 
       catalog_xmin, age(xmin) AS xmin_age 
FROM pg_replication_slots 
ORDER BY age(xmin) DESC;

    排查发现，业务端有个定时任务触发了死锁异常被捕获，但在异常处理逻辑中漏掉了 Rollback，导致一个 idle in transaction 的会话挂了 3 天，彻底锁死了全局 xmin 推进。

    解决手段简单粗暴：直接 pg_terminate_backend(pid) 杀掉僵尸会话。随后 Autovacuum 迅速介入，大量死元组被标记为可复用空间（FSM, Free Space Map）。

    生产级 Autovacuum 防御性调优基线

    原生 PG 的默认配置极度保守，是为了能在树莓派或低配虚机上跑起来而设计的。把默认配置直接上到几十核的高并发生产环境，等于给系统埋雷。

    为了防止类似“死亡螺旋”的发生，我们在 postgresql.conf 中必须落地以下防御性配置策略（基于 PG 14）：

    1. 斩断长事务的黑手

    绝对不要相信业务代码能完美处理所有异常分支。在数据库侧兜底是运维的基本素养。

    # 防御性配置：强制终结空闲时间过长的事务（极度重要）
idle_in_transaction_session_timeout = '10min'

# （可选）针对高并发OLTP，设置单条语句最大执行时间
# statement_timeout = '30s'

    2. 限制复制槽的 WAL 与 xmin 保留

    PG 13 引入了关键配置，防止死掉的逻辑复制槽把主库磁盘撑爆。

    # 限制复制槽最大保留的WAL大小，超出此值将强制失效复制槽
max_slot_wal_keep_size = '50GB'

    3. 释放 Autovacuum 的 I/O 枷锁

    默认的 autovacuum_vacuum_cost_limit 是 200，它限制了 VACUUM 进程的 I/O 速率，导致在大表中 VACUUM 速度远落后于 UPDATE 产生垃圾的速度。

    # 降低触发阈值，避免累积过多才开始清理
# 默认是0.2（20%），对于1亿行的表，要等2000万行变更才触发，太晚了
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02

# 提升 VACUUM 的 I/O 配额限制（默认 200 太低，SSD 环境可以直接上 2000~5000）
autovacuum_vacuum_cost_limit = 2000
autovacuum_vacuum_cost_delay = 2ms

    避坑指南：很多人喜欢调大 autovacuum_max_workers（默认 3）。注意，所有 Worker 是平分 autovacuum_vacuum_cost_limit 这个 I/O 额度的。如果只加 Worker 不加 Limit，每个 Worker 的执行速度反而会变得像蜗牛一样慢，加剧锁竞争。

    常见问题 (FAQ)

    Q1：表已经膨胀到 500GB 了，杀死长事务后，Autovacuum 跑完了，为什么磁盘空间没有释放？ A：Autovacuum 只能将 Dead Tuple 占用的空间标记为 Free Space Map (FSM) 供后续 INSERT/UPDATE 复用，它不会将空间退还给操作系统（除非恰好死元组都在表文件的物理末尾）。如果急需释放磁盘空间，不能使用常规 VACUUM，需要使用 VACUUM FULL（会获取 8 级排他锁，阻塞读写），或在生产环境使用第三方工具 pg_repack 或 pg_squeeze 进行在线无锁空间重组。

    Q2：日志里疯狂打印 “WARNING: oldest xmin is far in the past”，并伴随 Transaction ID Wraparound 警告，怎么救？ A：XID 环绕是 PG 最严重的问题之一。PG 的事务 ID 是 32 位整数（约42亿），用尽后会绕回，导致未来的事务把旧数据看作不可见，造成“数据蒸发”错觉。当到达防环绕阈值时，PG 会强制进入只读模式。 一旦触发警告，需立即停止应用写入，找出拖住 xmin 的老事务/复制槽清理掉，并手工执行 VACUUM (FREEZE, VERBOSE) table_name 来冻结老数据的 XID。若已宕机，需进入单用户模式（Single-User Mode）手动执行 Freeze。

    Q3：频繁触发 VACUUM 会不会对 WAL 产生剧烈影响？ A：会。当开启了 wal_log_hints = on 或使用了数据校验和（Checksums），且发生 Checkpoint 后的第一次页面修改时，PG 会触发全页写入（Full Page Writes, FPW）。VACUUM 过程中如果是第一次 touch 某个页，也会产生大量的 FPW，导致 WAL 体积激增。这是保证 Crash Safe 的必要代价。应对策略是合理拉长 Checkpoint 间隔（调大 max_wal_size 和 checkpoint_timeout），降低 FPW 的发生频率。