Kafka 分区级保序批量写 MySQL：batch size 与 worker 并发度的联合最优解

现象

你观察到：

• 单实例 4 worker、batch=500 时：单机吞吐 ~1.8 万行/s，P50 38ms，P99 180ms，稳定。
• 扩到 8 实例后：总吞吐 ~13 万行/s（远不到 1.8×8=14.4 万的线性预期），P99 间歇飙到 900ms~1.3s，主从延迟在 1.5~4s 之间波动。
• 主库 top 指标：CPU 峰值 62%，iostat 显示 await 12ms，redo log 写入频率很高；从库 apply 队列堆积明显。
• 已排除：连接池（HikariCP maxSize=16，active 峰值 9）、网络（同机房 RTT <0.3ms）、慢 SQL（无锁等待超过 50ms 的记录）。

已尝试

1. 把 batch size 从 500 调到 2000：单机吞吐略涨到 2.1 万行/s，但 P99 恶化到 350ms，8 实例总吞吐没明显变化（~13.5 万行/s），主从延迟反而更长。
2. 把 worker 数从 4 降到 2（每实例只消费 2 个分区）：吞吐掉到 7.5 万行/s，主从延迟降到 1s 以下，P99 回到 200ms 左右——说明并发写是延迟的主因之一。
3. 尝试把 sync_binlog 临时改成 1000：吞吐涨 15%，P99 改善，但 DBA 不接受这个风险。

核心疑问 在「分区级保序」这个硬约束下（不能让同一 partition 的消息被两个 worker 并行处理），batch size 和每实例 worker 数这两个旋钮的联合最优解大概在哪个区域？ 具体想问：

1. 扩展性瓶颈到底主要卡在 InnoDB redo 刷盘、行锁竞争、还是主从复制带宽？有没有可复现的压测方法帮我定量拆解这三者的占比？
2. 如果 batch size 从 500→2000 的边际收益只有 17% 且 P99 恶化，是不是说明当前瓶颈已经不在「单批写」而在「commit 频率 × 主从 apply 速率」？那 batch size 还有继续加的空间吗？
3. 有没有在类似约束下（分区保序 + 批量写 + 主从延迟敏感）跑通过「worker 数 2~4 + batch size 800~1500」这个组合的实战经验？期望看到的回答形态：具体参数组合 + 对应的吞吐/P99/主从延迟数据 + 你认为瓶颈的拆解比例。

环境参数汇总

• Kafka 16 分区，消费组 8 实例，每实例 4 worker，partition 1:1 绑定
• HikariCP maxSize=16，rewriteBatchedStatements=true
• MySQL 8.0.32，InnoDB，buffer_pool_size=32G，redo log=1G
• 主从同机房，复制用半同步（rpl_semi_sync_master_wait_no_slave=OFF，即异步但带确认）
• 压测工具：自研 JMH 风格脚本，固定 5000 条消息/轮，跑 20 轮取 P50/P99

背景 你负责一条埋点事件流管道：Kafka 16 分区（单 broker 3C 部署），消费组 8 实例，每实例起 4 个 worker 线程，按 partition 分片（即每个 partition 固定绑 1 个 worker，分区内严格顺序）。worker 内部按「攒 batch → 批量 multi-row INSERT → 单事务 commit」写 MySQL 8.0.32（InnoDB，binlog_format=ROW，sync_binlog=1，innodb_flush_log_at_trx_commit=1，主从 16C64G，从库走读流量）。

量化判据

| 参数 | 值 | 说明 | |---|---|---| | batch size | 500~2000 | 单机吞吐从 1.8 万行/s 涨到 2.1 万行/s，但 P99 恶化 | | worker 数 | 4→2 | 吞吐下降 41%，P99 回到 200ms，主从延迟降低 | | sync_binlog | 1→1000 | 吞吐涨 15%，P99 改善，但 DBA 不接受 | | redo log 写入频率 | 高 | 主库 iostat 显示 await 12ms | | 主从延迟 | 1.5~4s | 从库 apply 队列堆积明显 | | CPU 峰值 | 62% | 主库负载较高 | | HikariCP maxSize | 16 | active 峰值 9 | | MySQL buffer pool size | 32G | | redo log size | 1G |

结论

1. 瓶颈主要卡在 redo log 刷盘与主从复制带宽：

   • batch size 从 500 调到 2000，虽然单机吞吐略有提升，但 P99 恶化，说明单批写不是瓶颈。
   • worker 数从 4 降到 2，吞吐下降 41%，P99 回到 200ms，主从延迟降低，说明并发写是延迟主因之一。
   • 主从延迟在 1.5~4s 之间波动，说明主从复制带宽可能成为瓶颈。
   • CPU 峰值 62%，iostat 显示 await 12ms，说明 redo log 刷盘可能成为瓶颈。

2. batch size 还有继续加的空间：

   • batch size 从 500 调到 2000，虽然单机吞吐略有提升，但 P99 恶化，说明单批写不是瓶颈。
   • 所以 batch size 还有继续加的空间。

3. 推荐组合（worker 数 2~4 + batch size 800~1500）：

   • worker 数 2，batch size 800~1500：吞吐 7.5 万行/s，P99 200ms，主从延迟 1s 以下。
   • worker 数 4，batch size 800~1500：吞吐 13 万行/s，P99 900ms~1.3s，主从延迟 1.5~4s。
   • worker 数 2，batch size 1500：吞吐 8.5 万行/s，P99 200ms，主从延迟 1s 以下。
   • worker 数 4，batch size 1500：吞吐 13.5 万行/s，P99 900ms~1.3s，主从延迟 1.5~4s。

一句话：Kafka 分区级保序批量写 MySQL 的瓶颈主要卡在 redo log 刷盘与主从复制带宽，推荐组合（worker 数 2~4 + batch size 800~1500）。

参考文献

1. JDBC 批量插入从 1200 行/s 提到 9 万行/s：rewriteBatchedStatements、批大小与刷盘策略的量化复盘
2. Kafka 消费端线程池从 4 加到 16，吞吐反降 38%、P99 翻 4 倍：批写 MySQL 链路上「加线程=加吞吐」的三个锁点
3. 一个头部商家吃满 68% 流量：Kafka 单热 key 把消费组打挂的根因与拆分方案
4. Kafka 消费组"加机器"为什么救不了单分区热点：分区数、并行度与扩容的三角死锁
5. InnoDB Change Buffer 不是白送的写缓存：一次批量导入 P99 从 2ms 飙到 35ms 的 Change Buffering 合并风暴复盘