长事务对VACUUM与空间回收的影响——批处理任务的MVCC旧快照、监控SQL与治理规则实战

文章目录每日一句正能量1. 背景与问题2. 环境与数据2.1 为什么长事务不一定是“正在执行很久的SQL”2.2 关键监控字段2.3 表侧指标3. 复现过程3.1 Session A故意建立旧事务3.2 Session B制造大量旧版本3.3 观察清理前状态3.4 Session A不结束执行VACUUM3.5 Session A结束再VACUUM3.6 为什么普通VACUUM后表文件还是没明显变小3.7 VACUUM FULL为什么看起来更“有效”4. 方案实施4.1 第一层给“事务年龄”建立SLA4.2 第二层把批处理改成可恢复小事务4.3 为什么不是固定“5万行最好”4.4 第三层事务里禁止远程IO4.5 第四层治理idle in transaction4.6 第五层持续监控dead tuple ratio4.7 第六层优化表级Autovacuum参数4.8 Autovacuum更勤快仍不能替代长事务治理4.9 KingbaseES的snapshotcsn能力4.10 第七层使用VACUUM VERBOSE做诊断4.11 第八层严重膨胀再决定重建5. 结果对比5.1 E02小时长事务5.2 E1长事务仍在时VACUUM5.3 E2结束老事务后再VACUUM5.4 E3批处理每5万行提交5.5 E4再优化表级Autovacuum5.6 E5VACUUM FULL5.7 汇总表5.8 为什么E3/E4表大小略增长却不代表失败5.9 查询执行计划也要一起保存5.10 VACUUM频率不能只看“次数”6. 风险与复盘6.1 风险一直接kill所有长事务6.2 风险二批处理拆批后语义变化6.3 风险三idle timeout误杀合法应用6.4 风险四Autovacuum过于激进6.5 风险五只看n_dead_tup6.6 风险六普通VACUUM后抱怨“磁盘没降”6.7 风险七把VACUUM FULL当日常Cron6.8 风险八只调snapshotcsn不改应用6.9 风险九批次太小带来提交/WAL放大6.10 风险十统计信息和VACUUM治理脱节推荐治理规则回退方案最终结论附录最低验收门禁每日一句正能量“我孤独但不为寂寞所苦。”我选择/享受孤独的清明而不会陷入寂寞的泥潭。愿你带着这些话里的温暖与力量不慌不忙走自己的路。主题长事务治理 / VACUUM / 批处理任务重点MVCC、旧快照、backend_xmin、n_dead_tup、Autovacuum、VACUUM VERBOSE、空间膨胀、idle in transaction、批次提交、P95/P99适用场景KingbaseES 中批量更新、批量删除、ETL、数据修复、报表游标、长时间人工事务以及“磁盘越来越大、VACUUM 明明在跑但效果不好”的生产问题。1. 背景与问题生产库里有一类容量问题特别容易被误判表一直涨 n_dead_tup 很高 Autovacuum 也在运行 VACUUM 手工执行过 磁盘却还是降不下来很多团队第一反应是autovacuum参数太保守于是开始提高worker 调低scale factor 频繁VACUUM 甚至直接VACUUM FULL但真正的根因有时根本不在 VACUUM 本身而在一个几小时前开启、一直没有结束的事务。KingbaseES 使用 MVCC。官方事务文档说明每个 SQL/事务看到的是某个时间点的数据快照而不是底层行的唯一“当前版本”。这意味着一条记录被 UPDATE 后旧版本并不会在提交瞬间直接从数据页消失因为仍可能存在一个更早启动的事务需要按照自己的快照规则判断旧版本是否可见。例如10:00 T1 BEGIN 建立旧快照 10:05 T2 UPDATE 1000万行 提交 10:20 T3 又 UPDATE/DELETE 1000万行 提交 11:00 T1 仍然没有结束从业务角度看T2/T3都已提交但从 MVCC 世界看系统里仍存在一个非常老的活动事务/快照VACUUM 在判断哪些旧版本可以安全回收时必须考虑全局可见性边界。结果就是dead tuple持续累积长期会带来更多表页 更多索引页 更多Buffer访问 更多VACUUM扫描量 更长备份时间 更长恢复时间 更高存储成本甚至查询 SQL 本身完全没改执行计划也没变P95/P99 却因为表和索引膨胀、缓存命中率下降而持续变差。KingbaseES 官方sys_stat_all_tables提供n_live_tup、n_dead_tup、last_vacuum、last_autovacuum等字段官方表/索引膨胀治理文档也明确把长事务列为自动清理无法达到预期的重要原因之一。所以本文第一个核心结论是VACUUM 的清理能力并不是只由 VACUUM 自己决定系统里最老的活动事务和旧快照会直接影响哪些旧行版本已经“足够老到可以安全清理”。2. 环境与数据实验表CREATETABLEbatch_order(order_idBIGINTPRIMARYKEY,tenant_idBIGINTNOTNULL,statusINTNOTNULL,amountNUMERIC(18,2)NOTNULL,updated_atTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMP);索引CREATEINDEXidx_batch_order_tenant_statusONbatch_order(tenant_id,status);规模初始数据 2亿行 表大小 约280GB 索引 约90GB 日更新 2000万~5000万 日删除 100万~500万批处理任务每天凌晨更新历史订单状态原设计BEGIN 一次UPDATE几千万行 处理中还要写进度、查询辅助数据 最后统一COMMIT最坏事务持续1~3小时2.1 为什么长事务不一定是“正在执行很久的SQL”这是治理里最重要的区分。一种长事务activeSQL 本身持续执行 2 小时。另一种更危险idle in transaction事务已经 BEGIN。上一条 SQL 早就执行完。客户端却没有COMMIT 也没有ROLLBACKKingbaseES 官方sys_stat_activity把这种状态明确标成idle in transaction官方参考手册还提供idle_in_transaction_session_timeout用于终止在打开事务中空闲超过指定时间的会话并明确说明这样可以释放锁、连接槽也允许只对该事务可见的元组被清理。因此排查长事务不能只找“query_start最早”还必须找“xact_start最早”。2.2 关键监控字段SELECTpid,usename,application_name,client_addr,state,xact_start,now()-xact_startASxact_age,query_start,backend_xid,backend_xmin,wait_event_type,wait_event,queryFROMsys_stat_activityWHERExact_startISNOTNULLORDERBYxact_start;重点xact_start state backend_xid backend_xmin application_name client_addr querybackend_xmin可以帮助理解该会话保留的 xmin 边界配合事务年龄判断旧快照问题。2.3 表侧指标SELECTschemaname,relname,n_tup_upd,n_tup_del,n_live_tup,n_dead_tup,last_vacuum,last_autovacuum,last_analyze,last_autoanalyzeFROMsys_stat_all_tablesORDERBYn_dead_tupDESC;官方sys_stat_all_tables明确提供这些指标。但要注意n_dead_tup是估计值不要把它当成逐行精确计数。治理时应该结合变化趋势 表大小 VACUUM时间 真实查询性能一起判断。3. 复现过程3.1 Session A故意建立旧事务测试环境BEGIN;SELECTCOUNT(*)FROMbatch_orderWHEREtenant_id1001;不要提交。另一个会话检查xact_start backend_xmin state确认它长期存在。这一步只用于实验。生产环境绝对不要人为制造长事务。3.2 Session B制造大量旧版本连续执行UPDATEbatch_orderSETstatusCASEWHENstatus1THEN2ELSE1ENDWHEREtenant_idBETWEEN1000AND1999;COMMIT;然后DELETEFROMbatch_orderWHEREstatus9ANDtenant_idBETWEEN1000AND1999;COMMIT;这些 UPDATE/DELETE 会产生大量旧行版本。随着 churn 增加n_dead_tup上升。3.3 观察清理前状态例如n_live_tup: 2.0亿 n_dead_tup: 4200万 dead ratio: 约17% 表大小: 380GB同时业务查询SELECT...FROMbatch_orderWHEREtenant_id:tenantANDstatus:status;执行计划仍然可能Index Scan但Buffers Read比健康状态高很多。P953.8s这说明执行计划没有变化不代表物理访问成本没有变化。3.4 Session A不结束执行VACUUMVACUUM(VERBOSE,ANALYZE)batch_order;官方膨胀治理文档建议在清理效果异常的诊断场景中使用VACUUM VERBOSE观察结果。清理完成后last_vacuum更新。但是n_dead_tup可能仍保持较高水平。示例4200万 → 3100万表文件380GB → 378GB几乎不变。这不是 VACUUM “完全没执行”。而是需要区分两件事1. 哪些旧版本已经安全可清理 2. 表文件是否能直接缩小并把空间归还文件系统3.5 Session A结束再VACUUMSession ACOMMIT;或者ROLLBACK;旧快照边界释放。再运行VACUUM(VERBOSE,ANALYZE)batch_order;示例n_dead_tup: 3100万 → 480万查询Buffers Read: 710万 → 320万 P95: 3.2s → 1.4s这一组实验直接证明老事务才是维护效果受限的重要原因。3.6 为什么普通VACUUM后表文件还是没明显变小这也是最容易误解的地方。普通 VACUUM 的主要价值之一是让可回收空间重新进入可复用状态它并不保证每次都把数据文件立即缩回操作系统所以可能出现dead tuples明显下降但relation size几乎没下降后续 UPDATE/INSERT 可以复用这些空闲位置从而阻止文件继续膨胀。3.7 VACUUM FULL为什么看起来更“有效”如果执行VACUUMFULLbatch_order;可能看到380GB → 255GB因为它属于重写/重建目标对象KingbaseES 官方文档明确说明VACUUM FULL 能收回更多磁盘空间但需要更长时间、额外空间并获取更重的排他锁。官方锁文档也指出 VACUUM FULL 会使用 ACCESS EXCLUSIVE 级别的锁。因此VACUUM FULL 是严重物理膨胀后的重建手段不是用来掩盖日常长事务治理失败的常规任务。4. 方案实施4.1 第一层给“事务年龄”建立SLA不同业务定义不同阈值。例如在线API 事务 5s告警 普通后台任务 60s告警 批处理 5min告警 超过30min 必须有明确审批/运行手册不要全库用事务超过1分钟全部kill因为备份 DDL 维护 批任务需求不同。真正需要的是workload class分类治理。4.2 第二层把批处理改成可恢复小事务原来1000万行 一次事务改成每5万行 一个事务例如BEGIN;UPDATEbatch_orderSETstatus2,updated_atCURRENT_TIMESTAMPWHEREorder_id:start_idANDorder_id:end_idANDstatus1;COMMIT;每个批次记录job_id start_id end_id rows checksum commit_time这样失败后从最后成功批次继续而不是整个1000万重跑4.3 为什么不是固定“5万行最好”批次大小应该和上一篇 ETL 事务治理一样实测。关注单批耗时 WAL 锁 业务P99 VACUUM跟进能力 失败重做量目标不是越小越好而是让事务持续时间进入治理预算4.4 第三层事务里禁止远程IO禁止BEGIN UPDATE HTTP RPC sleep 等待消息 上传文件 人工确认 COMMIT任何外部依赖都可能把事务时间无限放大应该事务前准备 事务内只做必要DB工作 事务提交 事务后通知4.5 第四层治理idle in transaction官方sys_stat_activity明确区分idle idle in transaction后者才是在事务中空闲。对长期忘记提交的应用可以评估idle_in_transaction_session_timeout官方参考手册说明它会终止超过指定空闲时间的打开事务释放锁和连接资源并允许该事务可见性相关的元组被清理。但使用时必须按角色/应用范围验证。不能盲目实例级统一设置一个极短值。4.6 第五层持续监控dead tuple ratio官方膨胀治理文档给出了基于n_live_tup n_dead_tup计算旧版本比例的示例。例如SELECTrelname,n_live_tup,n_dead_tup,ROUND(n_dead_tup::numeric*100/NULLIF(n_live_tupn_dead_tup,0),2)dead_ratioFROMsys_stat_all_tables;不要只看dead tuple绝对数一张10亿行表1000万 dead tuple 是 1%。一张2000万表1000万 dead tuple 是 33%。风险不同。4.7 第六层优化表级Autovacuum参数KingbaseES 官方自动清理文档给出触发关系n_dead_tup autovacuum_vacuum_threshold autovacuum_vacuum_scale_factor × reltuples对于5亿行高更新表默认比例可能需要非常多变化才触发。可以根据增长速度 更新速度 dead ratio目标 IO预算设置表级参数。示例ALTERTABLEbatch_orderSET(autovacuum_vacuum_threshold50000,autovacuum_vacuum_scale_factor0.02);数字只是方法示例。不是生产固定答案。4.8 Autovacuum更勤快仍不能替代长事务治理这是关键。如果最老快照一直存在你即使让autovacuum每5分钟启动一次仍可能反复扫描却无法达到理想清理目标。所以治理顺序应该先解决长事务 再优化vacuum频率而不是反过来。4.9 KingbaseES的snapshotcsn能力KingbaseES 官方自动清理文档还提供autovacuum_vacuum_by_snapshotcsn用于 VACUUM/AUTOVACUUM 在特定条件下越过长事务阻碍执行分段清理。该参数默认 -1表示功能不启用。官方还给出了基于日常最大事务并发量或autovacuum threshold scale factor × reltuples估算触发值的建议。这个能力很有价值。但必须强调它是 KingbaseES 针对长事务清理问题提供的增强能力不代表应用就可以无限制造长事务。版本、表类型、业务可见性和资源开销都必须验证。4.10 第七层使用VACUUM VERBOSE做诊断当Autovacuum运行 但膨胀不降不要只凭last_autovacuum判断“清理正常”。对关键表在维护窗口执行VACUUM(VERBOSE,ANALYZE)batch_order;保存清理输出 执行时间 IO dead tuple前后 relation size形成证据。4.11 第八层严重膨胀再决定重建当普通 VACUUM 已能持续跟上dead tuple稳定但物理表文件因为历史膨胀仍非常大。再评估sys_squeeze REINDEX VACUUM FULLKingbaseES 官方膨胀治理文档指出sys_squeeze可用于部分在线重建场景。而VACUUM FULL会重建目标对象并需要排他锁。应该优先考虑业务窗口 额外磁盘 锁 复制影响 恢复风险不是只看能省多少GB5. 结果对比本节数字全部是方法演示数据并非生产实测。5.1 E02小时长事务最老事务 2h n_dead_tup 4200万 表大小 380GB 查询P95 3.8s Buffer Read 890万5.2 E1长事务仍在时VACUUM执行VACUUM VERBOSE结果n_dead_tup 3100万 表大小 378GB P95 3.2s有改善。但大量旧版本仍残留。5.3 E2结束老事务后再VACUUMn_dead_tup 480万 表大小 378GB P95 1.4s Buffer Read 320万注意表文件没有明显缩小但可复用空间恢复 查询物理工作量下降这才是普通 VACUUM 的主要治理价值。5.4 E3批处理每5万行提交长事务改造成50k/chunk最老批处理事务通常 30s一周后n_dead_tup 320万 Autovacuum 可以持续追上 查询P95 620ms5.5 E4再优化表级Autovacuum例如根据表规模把vacuum scale factor调到适合这张高 churn 大表的值。结果dead ratio 长期8% P95 510ms P99 明显收敛5.6 E5VACUUM FULL维护窗口执行表大小 378GB →255GB查询P95 480ms物理空间明显下降。但代价重建 额外磁盘 重锁 更高变更风险所以它解决的是历史物理膨胀不是长事务根因。5.7 汇总表实验最老事务n_dead_tup表大小P95结论E02h4200万380GB3.8s长事务高膨胀E12h3100万378GB3.2sVACUUM只能部分改善E20480万378GB1.4s旧快照释放空间可复用E330s320万382GB620ms批次治理有效E430s210万384GB510msAutovacuum持续追上E5080万255GB480ms物理重建成本最高5.8 为什么E3/E4表大小略增长却不代表失败因为业务仍然在持续写入新数据健康目标不是表大小永远不变而是增长与真实业务数据增长匹配不再出现业务数据10% 文件却100%这种异常膨胀。5.9 查询执行计划也要一起保存治理前后Plan Shape可能都还是Index Scan但需要记录Buffers Heap Fetch Execution Time例如治理前 Buffers Read 890万 治理后 110万说明计划名字没变 物理访问成本却显著下降这是空间治理对性能的真实价值。5.10 VACUUM频率不能只看“次数”一张表一天autovacuum 20次并不代表清理好。还要看n_dead_tup趋势 dead ratio趋势 每次清理后是否下降 表大小斜率 autovacuum耗时如果一直跑 一直清不掉优先查最老事务6. 风险与复盘6.1 风险一直接kill所有长事务线上发现xact_age10min直接批量终止。风险很大。这些会话可能是数据迁移 备份 人工维护 核心批处理正确流程先识别owner 确认业务 评估回滚成本 再终止6.2 风险二批处理拆批后语义变化原来1000万行一个原子事务改成200个5万行事务原子性边界已经变化。必须确认业务是否允许部分成功 断点续跑并设计job checkpoint 状态机 补偿 幂等不能只为了 VACUUM 好看就拆。6.3 风险三idle timeout误杀合法应用idle_in_transaction_session_timeout很实用。但交互式管理 某些框架事务 合法长批次可能需要更长窗口。所以优先应用/角色级而不是全实例极短值。6.4 风险四Autovacuum过于激进scale factor 太低频繁VACUUM可能增加CPU IO 缓存污染对在线查询产生干扰。所以自动维护参数必须经过负载回归6.5 风险五只看n_dead_tup这是估计统计。数据库重启等场景后统计可能暂时不准确官方膨胀文档也提醒这类统计需要结合 ANALYZE 或实际读写后判断。严重容量问题还应结合relation size kbstattuple_approx kbstatindex等更深检查。6.6 风险六普通VACUUM后抱怨“磁盘没降”这是概念错误。普通 VACUUM 的首要目标清理并复用内部空间物理文件收缩不是每次必然结果。如果运营指标只看df -h就会误判维护无效。6.7 风险七把VACUUM FULL当日常CronVACUUM FULL重写对象 需要重锁 需要额外空间高并发生产系统不能每天随便跑。应该作为严重历史膨胀 维护窗口 容量规划下的专项动作。6.8 风险八只调snapshotcsn不改应用KingbaseES 的autovacuum_vacuum_by_snapshotcsn对特定长事务场景很有价值。但如果应用每天制造几十个3小时事务依然需要治理。数据库增强能力应该缓解不可避免的长事务而不是容忍所有不合理事务设计6.9 风险九批次太小带来提交/WAL放大把1000万行拆成每10行提交事务是短了。但COMMIT次数 网络开销 WAL提交成本会大幅上升。所以必须结合上一章事务大小甜点共同选择。6.10 风险十统计信息和VACUUM治理脱节大规模 UPDATE/DELETE 后分布也会变化清理完成还需要检查ANALYZE 统计新鲜度 执行计划否则空间健康了 计划仍旧估错推荐治理规则可以形成一套生产规则规则1 所有批任务必须有application_name/job_id 规则2 所有批任务必须有最大事务年龄预算 规则3 所有批任务必须可断点续跑 规则4 数据库事务内禁止远程IO和人工等待 规则5 idle in transaction必须告警 规则6 核心高churn表监控n_dead_tup/dead ratio/last_autovacuum 规则7 VACUUM效果不佳先查最老事务 规则8 表级autovacuum参数按表治理 规则9 VACUUM FULL必须走变更窗口 规则10 任何kill长事务动作必须有owner和回滚评估回退方案如果批次治理或自动维护调整上线后出现吞吐下降 批任务无法恢复 Autovacuum IO过高 合法事务被timeout回退1. 停止扩大新策略 2. 恢复上一个安全batch size 3. 恢复表级autovacuum原参数 4. 恢复原idle timeout作用范围/值 5. 保存前后sys_stat_activity/sys_stat_all_tables/VACUUM VERBOSE 6. 不盲目回滚已经完成的VACUUM/ANALYZE 7. 重测批处理正确性与断点恢复最终结论长事务治理的核心逻辑是MVCC需要保留历史版本 ↓ 最老活动事务决定可见性边界之一 ↓ 事务越老越多旧版本可能暂时无法彻底清理 ↓ dead tuples和膨胀累积 ↓ 表/索引/Buffer/维护成本上升真正有效的治理顺序应该是找最老事务 → 区分active和idle in transaction → 缩短/拆分批处理事务 → 建立幂等断点 → 让Autovacuum持续追上 → 再处理历史物理膨胀如果只记住一句话VACUUM 不是“垃圾回收器想跑就能把所有旧版本都删掉”它必须尊重 MVCC 可见性边界。一个长期不结束的事务可能让整个数据库为它保留大量历史版本而这个成本最终会由磁盘、缓存、查询延迟和维护窗口共同买单。生产上真正应该追求的也不是n_dead_tup永远为0而是长事务受控 Autovacuum能持续追上写入速度 膨胀率稳定 查询Buffers和P95/P99不随时间失控 物理空间增长和真实业务增长匹配这才是一套可持续的长事务与空间治理体系。附录最低验收门禁[ ] Top长事务按xact_start可查询 [ ] idle in transaction可识别 [ ] application_name/job_id可定位owner [ ] backend_xmin已纳入诊断 [ ] n_dead_tup趋势已监控 [ ] dead tuple ratio已监控 [ ] last_autovacuum已监控 [ ] VACUUM VERBOSE诊断脚本已准备 [ ] 批任务支持分批提交 [ ] 批任务支持断点续跑 [ ] 事务内无远程RPC/人工等待 [ ] idle timeout策略已评估 [ ] 表级autovacuum参数有基线 [ ] 查询Buffers/P95/P99已回归 [ ] VACUUM FULL不作为日常任务 [ ] 回退参数和旧批次配置已保存转载自https://blog.csdn.net/u014727709/article/details/164031237欢迎 点赞✍评论⭐收藏欢迎指正