文章目录每日一句正能量1. 背景与问题1.1 小事务为什么慢1.2 大事务为什么危险2. 环境与数据2.1 为什么总行数必须固定2.2 为什么必须保留生产索引与约束2.3 INSERT与COPY分开测3. 复现过程3.1 B100固定成本占主导3.2 B1K低风险收益区3.3 B10K甜点候选区3.4 B50K吞吐继续升但风险开始更快增长3.5 B100K吞吐接近平台3.6 B500K超大事务开始反噬3.7 失败注入必须是压测的一部分4. 方案实施4.1 第一原则找吞吐拐点而不是最大批次4.2 第二原则用LSN量化WAL4.3 WAL总量和峰值不是一回事4.4 第三原则观察checkpoint请求4.5 第四原则max_wal_size是系统容量参数不是魔法加速器4.6 第五原则主备lag必须进入ETL SLA4.7 为什么最终追平也不代表没有问题4.8 第六原则批次设计必须包含幂等4.9 第七原则批次必须可断点重启4.10 第八原则比较COPY不要无限扩大INSERT4.11 COPY不等于没有WAL4.12 第九原则谨慎测试synchronous_commitoff4.13 synchronous_commitoff和fsyncoff不是一个风险级别5. 结果对比5.1 为什么不是直接选100k5.2 在线业务一起压测5.3 每行WAL也是有价值的指标5.4 检查点数据用于判断写入是否开始冲击后台维护5.5 失败场景也要有结果表6. 风险与复盘6.1 风险一超大事务6.2 风险二事务过小6.3 风险三只看rows/s6.4 风险四为了装载随意扩大max_wal_size6.5 风险五同步复制放大提交延迟6.6 风险六为了快关闭synchronous_commit6.7 风险七重试不幂等6.8 风险八长事务影响维护6.9 风险九ETL完成后不刷新统计6.10 风险十一次改太多变量推荐批次选择算法推荐生产门禁示例回退方案最终结论附录最低验收门禁每日一句正能量真正的热爱能让你在规则中创造在限制中舞蹈将“不可能”转化为“我试试”。真正的热爱不是无视规则而是深刻理解规则后在其中寻找创新的缝隙不是抱怨限制而是将限制视为节奏与框架优雅地与之共舞。主题写入吞吐 / ETL入库 / 批量事务重点批次大小、INSERT、COPY、WAL、COMMIT、检查点、主备复制、synchronous_commit、失败重试、P95/P99适用场景KingbaseES 中离线 ETL、数据同步落库、历史数据导入、批处理写入、数据仓库前置入库以及大批量接口写入。1. 背景与问题ETL 写入最常见的调优动作之一是把batch size调大。100 行一批慢就改 10001000 还不够就改成 10000、50000、100000最后甚至一次事务写几百万行。单看离线测试很容易形成一个经验“事务越大提交越少吞吐越高。”这个结论只在某一个区间成立。生产数据库真正承受的并不只是 INSERT 行数而是同时承受 WAL 生成与刷盘、索引维护、锁持有、脏页写回、checkpoint、主备复制、归档、失败回滚和失败重试。于是一个 ETL 作业可能自己从 11 万 rows/s 提升到 13 万 rows/s但在线接口 P99 翻倍、备库延迟增长到数 GB、检查点请求增加而且单批失败后必须重做几十万行。这样的“吞吐优化”并不完整。KingbaseES 官方 WAL 文档说明数据库通过预写式日志支持崩溃恢复和复制当synchronous_commiton时事务成功返回前需要等待相应 WAL 的持久化在同步复制环境下还可能等待同步备库达到规定阶段。max_wal_size则控制自动检查点之间允许 WAL 增长的大致规模WAL 增长过快会增加检查点压力。因此批量插入真正需要寻找的不是“最大事务”而是吞吐已经接近平台 提交延迟仍可接受 WAL峰值仍可控 主备复制不产生不可接受积压 失败时重做量可接受这个交点才是生产批次甜点。1.1 小事务为什么慢假设总共导入 100 万行。100 行/事务需要10000 次 COMMIT除了每行数据和索引维护还要频繁承担事务开始/提交、客户端往返、语句执行、WAL 提交同步等固定成本。这里不能简单说“10000 次 COMMIT 就一定等于 10000 次独立物理 fsync”因为数据库存在 group commit 等机制。但对于低并发、单线程 ETL小事务的固定成本占比通常仍然明显。1.2 大事务为什么危险事务从 10000 行扩大到 500000 行固定成本确实被摊薄但新的风险逐步增加单事务WAL量变大 提交等待变长 锁持有更久 MVCC事务生命周期更长 备库回放积压可能增大 失败回滚范围变大 客户端超时后的重试代价变大所以本篇重点不是给出一个通用“最佳 batch10000”而是给出一套可以在自己的 KingbaseES 环境里找拐点的方法。2. 环境与数据测试表CREATETABLEetl_order_fact(idBIGINTPRIMARYKEY,batch_idBIGINTNOTNULL,tenant_idBIGINTNOTNULL,biz_timeTIMESTAMPNOTNULL,amountNUMERIC(18,2)NOTNULL,statusINTNOTNULL,source_keyVARCHAR(128)NOTNULL);二级索引CREATEINDEXidx_etl_order_fact_batchONetl_order_fact(batch_id);CREATEINDEXidx_etl_order_fact_tenant_timeONetl_order_fact(tenant_id,biz_time);每轮测试固定总写入量100万行 平均行宽约180B 索引主键 2个二级索引 客户端并发1 synchronous_commiton 复制拓扑1主2备 数据分布完全相同唯一核心变量rows per transaction压测矩阵B100 100 B1K 1,000 B10K 10,000 B50K 50,000 B100K 100,000 B500K 500,000另外再单独比较COPY不能把“事务大小”和“加载协议”混为一个实验。2.1 为什么总行数必须固定如果 100 行批次只写 1 万行而 10 万行批次写 100 万行结果没有可比性。必须固定总数据量再比较总耗时 rows/s COMMIT次数 WAL总量 WAL MB/s 提交P95/P99 主备lag 失败重试量2.2 为什么必须保留生产索引与约束很多性能测试使用无索引空表导入数字非常漂亮。但真实生产表往往有主键、唯一索引、二级索引、外键或触发器。每插一行都可能产生额外 WAL 和随机写。因此事务大小必须在“接近生产结构”的目标表上验证。2.3 INSERT与COPY分开测KingbaseES 的INSERT可以使用多行VALUES对于大规模 ETL官方还提供 COPY 和sys_bulkload等批量加载能力。COPY 的优势之一是减少逐条语句解析和协议开销因此当 INSERT 的 batch 已经进入吞吐平台时下一步优化方向不一定是继续扩大事务而可能是换用更适合大数据流的加载协议。3. 复现过程3.1 B100固定成本占主导100 万行、每批 100 行COMMIT次数10000 吞吐18,000 rows/s WAL42 MB/s Commit P958ms 复制lag峰值25MB单次提交延迟很低但次数极多。这个阶段的核心瓶颈通常不是“单批太重”而是每行分摊的事务固定成本过高3.2 B1K低风险收益区每批 1000 行COMMIT次数1000 吞吐62,000 rows/s WAL78 MB/s Commit P9518ms 复制lag峰值65MB吞吐相对 B100 提升巨大而失败时最多重做约 1000 行因此通常属于低风险收益。3.3 B10K甜点候选区每批 10000 行COMMIT次数100 吞吐118,000 rows/s WAL132 MB/s Commit P9595ms 复制lag峰值180MB吞吐已经接近最高区间但事务仍不至于大到很难回滚或重试。对于很多在线系统旁路 ETL这类批次常是值得重点评估的候选。3.4 B50K吞吐继续升但风险开始更快增长吞吐132,000 rows/s WAL168 MB/s Commit P95410ms 复制lag峰值620MB从 10k 到 50k示例吞吐约提升 12%仍有收益但系统侧指标上升明显。3.5 B100K吞吐接近平台吞吐135,000 rows/s WAL194 MB/s Commit P95980ms 复制lag峰值1.4GB从 50k 增加到 100k吞吐只提升约 2.3%但提交延迟翻倍以上复制积压也明显扩大。这就是需要寻找的拐点批次继续增大收益已接近停止但风险还在增长。3.6 B500K超大事务开始反噬吞吐128,000 rows/s WAL峰值260 MB/s Commit P955.4s 复制lag峰值6.3GB 失败最大重做50万行吞吐已经下降。可能原因包括WAL写入峰值过高 更多脏页集中产生 后台写盘竞争 检查点压力 索引维护峰值 复制发送/回放积压 长事务带来的额外资源占用3.7 失败注入必须是压测的一部分只测试成功路径不够。至少主动制造重复主键 约束异常 客户端连接中断 应用超时记录回滚耗时 重新执行行数 是否出现重复 主备恢复时间1000 行批次失败和 50 万行批次失败恢复成本完全不同。4. 方案实施4.1 第一原则找吞吐拐点而不是最大批次示例BatchRows/s10018k1k62k10k118k50k132k100k135k500k128k10k 到 50k 仍有明显收益50k 到 100k 只剩很小增益。这时就应该把关注点从“还能不能更快”切换到Commit P95/P99 WAL MB/s checkpoint replica lag 失败重做 在线业务P994.2 第二原则用LSN量化WALKingbaseES 官方运维资料使用 LSN 表示 WAL 位置并提供sys_current_wal_lsn()、sys_current_wal_flush_lsn()以及在相应版本中使用sys_wal_lsn_diff()计算 WAL 位置差。实验前SELECTsys_current_wal_lsn();运行一轮固定数据量 ETL。实验后SELECTsys_current_wal_lsn();若目标版本支持SELECTsys_wal_lsn_diff(:after_lsn,:before_lsn);得到 WAL 字节数。需要记录WAL总量 WAL MB/s 每行WAL bytes4.3 WAL总量和峰值不是一回事同样插入 100 万行总 WAL 可能相近但 100 行批次输出更平滑50 万行事务更容易形成集中峰值。因此生产门禁里应该同时有total WAL peak WAL MB/s4.4 第三原则观察checkpoint请求KingbaseES 官方sys_stat_bgwriter可以观察checkpoints_timed checkpoints_req checkpoint_write_time checkpoint_sync_time buffers_checkpoint buffers_backend buffers_backend_fsync每轮实验测试前快照 测试后快照如果批次增大后checkpoints_req明显增加需要检查 WAL 增长和 checkpoint 配置而不是只看 rows/s。4.5 第四原则max_wal_size是系统容量参数不是魔法加速器官方文档指出max_wal_size是自动检查点间允许 WAL 增长的软限制。对于专门的大数据迁移窗口官方配置建议会评估更大的max_wal_size、更长checkpoint_timeout等以降低频繁检查点带来的装载干扰。但压测方法必须坚持单变量先固定WAL参数找batch拐点之后再单独固定batch测试WAL/checkpoint参数否则无法归因。4.6 第五原则主备lag必须进入ETL SLA主库跑到135k rows/s如果备库落后6GB整体系统不能算健康。可以使用SELECTclient_addr,state,sent_lsn,write_lsn,flush_lsn,replay_lsn,sync_state,sys_wal_lsn_diff(sys_current_wal_flush_lsn(),replay_lsn)ASreplay_lag_bytesFROMsys_stat_replication;观察峰值lag P95 lag ETL结束后的catch-up时间4.7 为什么最终追平也不代表没有问题如果 ETL 两小时里备库始终落后 30 分钟而业务需要从备库做报表或随时切换那么即使凌晨最终追平也依然违反业务 SLA。所以复制指标至少两个运行中最大lag 停止写入后的追平时间4.8 第六原则批次设计必须包含幂等ETL 最典型的故障是客户端超时应用无法确定 COMMIT 是否已经成功。如果直接重试整个批次而目标表没有幂等设计就可能重复。建议至少设计batch_id source_key source_offset 唯一约束 去重/UPSERT规则让“重试”成为安全操作。4.9 第七原则批次必须可断点重启一个 1 亿行文件不应设计成整个文件一个事务可以按 10k/50k 等批次保存batch_id source_start source_end expected_rows loaded_rows checksum status失败从最后一个成功批次继续而不是从第一行重来。4.10 第八原则比较COPY不要无限扩大INSERT如果 10k INSERT 已经达到 118k rows/s100k INSERT 也只有 135k那么继续增加事务到 500k 意义很小。可以把下一组实验改成 COPY。KingbaseES 官方客户端资料把 COPY 定义为高效的批量数据传输机制。sys_bulkloadBUFFERED 模式也以减少普通 INSERT 解析时间为优势之一。所以优化路径可以是逐条INSERT → JDBC batch / 多Values → 合适事务大小 → COPY / bulkload而不是只会不断放大事务4.11 COPY不等于没有WAL正常wal_levelreplica或logical的生产系统仍需考虑 WAL 与复制。官方 WAL 文档只在特定minimal模式及符合条件的批量操作中说明可以省略部分 WAL开启归档、流复制的系统通常不能照搬这种模式。4.12 第九原则谨慎测试synchronous_commitoffKingbaseES 官方文档说明synchronous_commitoff可以让客户端在 WAL 真正持久化前收到成功从而减少等待数据库一致性不会因此损坏但系统崩溃时可能丢失最近已经报告成功的事务。因此它只适合源数据100%可重放 业务明确接受最近一小段已确认数据丢失后重放的 ETL。可以在受控事务中测试BEGIN;SETLOCALsynchronous_commitoff;-- ETLCOMMIT;资金主链路、无法重放数据不要为了吞吐这样做。4.13 synchronous_commitoff和fsyncoff不是一个风险级别synchronous_commitoff仍保证数据库处于一致状态只可能丢最近已确认事务。fsyncoff则是更高风险的持久性设置。不能因为 ETL 是批量任务就随意关闭fsync。5. 结果对比示例汇总批次吞吐 rows/sCOMMIT次数WAL MB/s提交P95复制Lag峰值10018k10000428ms25MB1k62k10007818ms65MB10k118k10013295ms180MB50k132k20168410ms620MB100k135k10194980ms1.4GB500k128k22605.4s6.3GB以上均为方法演示数据并非生产实测。5.1 为什么不是直接选100k100k 的吞吐135k rows/s确实最高。但相较 50k吞吐只提升约2.3%与此同时Commit P95从410ms升到980ms 复制lag从620MB升到1.4GB 失败重做从5万行升到10万行如果业务的门禁是吞吐 100k rows/s Commit P95 500ms 复制lag峰值 1GB 失败重做 5万行那么50k比 100k 更合理。5.2 在线业务一起压测离线环境的最大吞吐不能直接上线。必须在 ETL 运行时同步压订单查询 核心接口 在线写入 报表查询例如batch50k ETL吞吐12% API P998% batch100k ETL吞吐再2% API P9945%显然后者不值得。5.3 每行WAL也是有价值的指标可以计算wal_bytes / inserted_rows帮助比较多Values INSERT COPY 索引数量变化这可以解释为什么某个“更快”的方案对日志与复制系统更重。5.4 检查点数据用于判断写入是否开始冲击后台维护批次扩大后如果出现checkpoints_req快速增加 checkpoint_write_time增加 buffers_backend增加说明前台批处理已经开始把压力传递到后台写盘路径。这时继续调大 batch往往是错误方向。5.5 失败场景也要有结果表建议增加批次故障点回滚/失败处理最大重做1k第900行重复键很短1k10k第9000行错误可接受10k50k第49000行错误明显增加50k500k第49万行错误很高500k吞吐只反映成功路径失败表才反映真正的运维风险。6. 风险与复盘6.1 风险一超大事务一次事务数百万行会放大失败回滚 复制积压 锁持有 MVCC生命周期 业务可见性延迟只有在专门停机装载、数据可完全重做、目标表隔离的场景才值得单独评估。6.2 风险二事务过小事务太小会导致COMMIT数量过多 协议往返多 事务固定成本高 ETL窗口变长ETL 运行越久对生产影响的持续时间也越长。6.3 风险三只看rows/s完整门禁应该同时包括Rows/s Commit P95/P99 WAL MB/s checkpoints_req replica lag 在线API P95/P99 失败重做行数6.4 风险四为了装载随意扩大max_wal_size官方文档提醒更大的max_wal_size可能延长崩溃恢复时间。大数据迁移时可以做专门容量规划但要和WAL磁盘空间 恢复RTO checkpoint节奏一起评估。6.5 风险五同步复制放大提交延迟在同步复制下synchronous_commiton的提交还可能等待同步备库的 WAL 接收/刷盘阶段。因此单机测试得到的 batch sweet spot 不能直接复制到 HA 集群。6.6 风险六为了快关闭synchronous_commit是否允许异步提交是数据耐久性决策不是单纯数据库性能参数。只有可重放 ETL 才适合评估。6.7 风险七重试不幂等需要确保batch_id source_key能够识别已经成功的批次或记录。否则网络故障一次就可能把性能问题演变成数据质量问题。6.8 风险八长事务影响维护长时间开放的大事务会延长 MVCC 可见性边界对清理和长期运行稳定性不利。不能因为“最终一次提交更快”就忽略数据库全局生命周期。6.9 风险九ETL完成后不刷新统计大批量写入改变表规模、热点值和时间分布。装载完成后、查询流量放开前应根据数据变化量进入 ANALYZE/统计信息治理流程否则可能出现写入很快 查询计划变差6.10 风险十一次改太多变量不能同时batch 10k→100k INSERT→COPY 并发1→8 max_wal_size翻4倍 synchronous_commit关闭最后即使快了也无法证明原因。每轮只改一个变量。推荐批次选择算法1. 从1k开始 2. 逐步测试5k/10k/50k/100k 3. 若吞吐提升小于10%停止无条件放大 4. 检查Commit P95、WAL、复制lag、checkpoint和失败成本 5. 选择所有风险门禁内吞吐最高的批次这里的关键不是最大吞吐而是风险约束下的最大吞吐推荐生产门禁示例Rows/s 100k Commit P95 500ms Replica lag peak 1GB Replica catch-up 60s checkpoints_req无异常增长 Online API P99增加 10% 单次失败重做 5万行这组门禁下本示例会选择50k而不是 100k。回退方案如果批次放大上线后出现在线P99升高 WAL盘压力 主备lag 检查点增加 单批回滚过慢立即1. 停止继续增加batch 2. 恢复上一个已验证批次 3. 恢复原synchronous_commit策略 4. 必要时暂停/限速ETL等待备库追平 5. 保存WAL、checkpoint和复制监控 6. 用同一批源数据重测安全批次 7. 通过batch_id/source_key防止重试重复不要在回退时同时改batch max_wal_size checkpoint_timeout否则再次丢失因果证据。最终结论批量插入事务大小本质上是在四类成本之间找平衡小事务 网络 提交 事务管理成本高 中等事务 固定成本被有效摊薄吞吐快速提升 大事务 吞吐进入平台但WAL、复制、失败和锁风险继续增长 超大事务 系统争用加剧吞吐甚至开始下降如果只记住一句话批量插入最优事务大小不是数据库一次最多能吞多少行而是在在线业务、WAL、检查点、复制和失败重试都能承受的前提下一次事务应该吞多少行。真正成熟的 ETL 基线必须同时具备可解释 可监控 可重试 可限流 可回退而不只是一个看起来很漂亮的rows/s。附录最低验收门禁[ ] 总写入行数固定 [ ] 目标表索引/约束固定 [ ] 100/1k/10k/50k/100k批次已测试 [ ] Rows/s已记录 [ ] Commit P95/P99已记录 [ ] WAL MB/s已记录 [ ] checkpoints_req已记录 [ ] 主备lag已记录 [ ] catch-up时间已记录 [ ] 失败批次重试已测试 [ ] batch_id/source_key幂等已验证 [ ] 在线接口P95/P99已回归 [ ] COPY已独立对比 [ ] 回退批次已保存转载自https://blog.csdn.net/u014727709/article/details/163950613欢迎 点赞✍评论⭐收藏欢迎指正