1. MySQL并行复制性能异常案例分析为何多线程比单线程慢最近在排查一个MySQL从库复制延迟的案例时遇到了一个反直觉的现象在特定场景下启用并行复制配置了8个工作线程的从库其复制速度竟然比单线程复制还要慢4倍多。这个发现颠覆了我们多线程一定比单线程快的常规认知值得深入分析。1.1 问题现象描述环境配置MySQL版本8.0.40事务隔离级别RCRead Committed并行复制配置replica_parallel_workers8问题表现 从某个时间点开始从库的复制延迟持续增加且没有下降趋势。通过监控发现Relay_Master_Log_File和Exec_Master_Log_Pos都在变化但变化速度很慢主库写入量大的时间段没有出现延迟反而在写入量较小时出现延迟错误日志中出现大量锁等待超时报错1.2 初步排查思路首先使用mysql-binlog-time-extractor工具分析binlog排除了主库写入压力大的可能性。然后通过binlog_summary.py分析延迟时段的binlog文件发现操作模式高度一致操作频次最高的两张表都是biz_schema.tbl_product_service_mapping01主要操作类型为DELETE和INSERT且INSERT总是出现在对应DELETE之后这表明业务是通过先删除后插入的方式实现数据更新这种模式在特定条件下可能引发锁竞争问题。2. 问题复现与深入分析2.1 实验环境搭建为了准确复现问题我们搭建了测试环境创建测试表CREATE TABLE biz_schema.tbl_product_service_mapping01 ( id BIGINT AUTO_INCREMENT PRIMARY KEY, c1 INT, c2 INT, UNIQUE KEY uk_c1_c2 (c1, c2) );模拟业务操作模式-- 先删除 DELETE FROM biz_schema.tbl_product_service_mapping01 WHERE c1 ?; -- 后插入 INSERT IGNORE INTO biz_schema.tbl_product_service_mapping01 (c1, c2) VALUES (?, ?);配置从库并行复制SET GLOBAL replica_parallel_workers 8; SET GLOBAL replica_preserve_commit_order ON;2.2 锁等待问题定位通过performance_schema.data_locks和sys.innodb_lock_waits视图我们观察到以下锁等待链事务APID 10等待事务BPID 14持有的S,GAP锁事务BPID 14等待事务CPID 12持有的S,GAP锁事务CPID 12因提交顺序限制必须等待事务A提交这种循环等待导致三个线程相互阻塞最终触发锁等待超时1205错误。2.3 RC隔离级别下的GAP锁异常在Read Committed隔离级别下通常只会出现记录锁。但本案例中出现了GAP锁原因在于DELETE操作不会立即物理删除记录而是标记为delete-marked当INSERT操作遇到delete-marked记录时会对该索引项加S锁同时对该索引项的间隙加S,GAP锁这种加锁行为在row_ins_scan_sec_index_for_duplicate()函数中实现通过以下实验可以验证-- 会话1创建表并插入数据 CREATE TABLE test.t1( id BIGINT AUTO_INCREMENT PRIMARY KEY, c1 INT, c2 INT, UNIQUE KEY(c1,c2) ); INSERT INTO test.t1(c1,c2) VALUES(10512476,1),(10512476,2); -- 会话2暂停purge线程 FLUSH TABLES test.t2 FOR EXPORT; -- 会话1删除数据 DELETE FROM test.t1; -- 会话3插入数据 BEGIN; INSERT INTO test.t1(c1,c2,id) VALUES(10512476,1,18158557178); -- 查看锁信息 SELECT * FROM performance_schema.data_locks;3. 根本原因分析3.1 并行复制工作机制MySQL并行复制基于以下机制事务分组通过slave_parallel_workers指定工作线程数冲突检测WRITESET并行复制根据事务修改的主键/唯一索引判断是否冲突顺序保证replica_preserve_commit_orderON时保持主库提交顺序3.2 问题产生的四个关键因素业务模式采用DELETEINSERT方式更新数据索引设计存在唯一索引(uk_c1_c2)隔离级别RC下对delete-marked记录的特殊加锁并行配置replica_preserve_commit_orderON这四个因素共同作用导致了并行工作线程间产生锁等待提交顺序限制形成循环等待最终触发锁超时和事务重试4. 解决方案与优化建议4.1 应用层优化方案索引设计优化方案1将唯一索引改为普通二级索引ALTER TABLE biz_schema.tbl_product_service_mapping01 DROP INDEX uk_c1_c2, ADD INDEX idx_c1_c2(c1,c2);方案2使用联合主键替代自增主键唯一索引ALTER TABLE biz_schema.tbl_product_service_mapping01 DROP PRIMARY KEY, DROP INDEX uk_c1_c2, ADD PRIMARY KEY(c1,c2);更新逻辑优化用UPDATE替代DELETEINSERTUPDATE biz_schema.tbl_product_service_mapping01 SET c2 ? WHERE c1 ?;使用INSERT ON DUPLICATE KEY UPDATEINSERT INTO biz_schema.tbl_product_service_mapping01 (c1,c2) VALUES (?,?) ON DUPLICATE KEY UPDATE c2 VALUES(c2);4.2 数据库层优化方案调整并行复制配置SET GLOBAL replica_preserve_commit_order OFF;优点打破事务提交顺序限制避免循环等待限制不适用于Group Replication环境监控与告警-- 监控锁等待 SELECT * FROM sys.innodb_lock_waits; -- 监控复制延迟 SHOW SLAVE STATUS\G4.3 各方案性能对比我们在测试环境对以下方案进行了基准测试方案平均执行时间(s)锁等待次数适用场景原方案(唯一索引并行8线程)359.06高频不推荐单线程模式83.07无临时解决方案普通索引并行8线程33.50少量可接受修改索引关闭提交顺序保证21.11无非Group Replication环境改用UPDATE语句18.23无可修改业务逻辑5. 经验总结与最佳实践5.1 关键发现在RC隔离级别下对唯一索引的INSERT操作遇到delete-marked记录时仍会产生GAP锁并行复制性能不一定总优于单线程在特定场景下可能更差replica_preserve_commit_orderON可能加剧锁竞争问题5.2 MySQL并行复制使用建议评估业务模式避免高频的DELETEINSERT操作大批量更新操作考虑分批次执行合理配置参数[mysqld] replica_parallel_workers8 replica_preserve_commit_orderOFF # 非Group Replication环境 slave_transaction_retries10 # 适当增加重试次数监控指标Seconds_Behind_MasterSlave_SQL_Running_State锁等待统计5.3 故障排查checklist当遇到复制延迟时可以按以下步骤排查检查主从状态SHOW SLAVE STATUS\G分析binlog模式使用binlog_summary.py等工具检查锁等待SELECT * FROM sys.innodb_lock_waits查看错误日志定位超时和死锁信息简化测试场景单表复现问题6. 深度原理剖析6.1 InnoDB锁机制详解在RC隔离级别下对唯一索引的特殊加锁逻辑查找阶段在row_ins_scan_sec_index_for_duplicate()中检查唯一性约束如果发现delete-marked记录加S锁和S,GAP锁插入阶段在lock_rec_add_to_queue()中处理锁继承新插入记录会继承下一条记录的GAP锁purge阶段后台线程清理delete-marked记录被清理记录的锁会被释放6.2 并行复制实现原理MySQL 8.0的WRITESET并行复制事务分组算法基于事务修改的行的主键/唯一键计算hash相同hash值的事务分配到同一组冲突检测机制通过transaction_write_set_extraction配置支持XXHASH64和MURMUR32算法工作线程调度协调线程分配事务给工作线程工作线程执行非冲突事务6.3 性能影响因素矩阵影响并行复制性能的关键因素因素影响程度优化方向事务冲突率高减少热点数据操作工作线程数中根据CPU核心数配置提交顺序保证高非必要场景关闭网络延迟低优化主从网络从库硬件中提升IOPS和CPU7. 扩展思考与进阶优化7.1 其他可能引发类似问题的场景批量导入数据LOAD DATA INFILE在大表上的并发执行mydumper/myloader的并行导入Online DDL操作添加唯一索引时的校验阶段主键变更操作级联删除/更新外键约束引发的锁升级大事务中的多表操作7.2 高级调优技巧动态调整并行度-- 高峰期增加工作线程 SET GLOBAL replica_parallel_workers 16; -- 低峰期减少工作线程 SET GLOBAL replica_parallel_workers 4;工作线程绑定CPU核心[mysqld] slave_parallel_workers 8 slave_parallel_threads_pinned 4 # 绑定前4个核心监控工作线程负载SELECT THREAD_ID, PROCESSLIST_TIME, PROCESSLIST_STATE FROM performance_schema.threads WHERE NAME LIKE %worker%;7.3 MySQL 8.4的改进在最新MySQL 8.4版本中优化了WRITESET并行复制的冲突检测算法增加了slave_parallel_modeOPTIMISTIC模式改进了锁等待超时的处理机制升级建议-- 检查兼容性 SELECT version; -- 测试新特性 SET GLOBAL slave_parallel_mode OPTIMISTIC;
1. MySQL并行复制性能异常案例分析为何多线程比单线程慢最近在排查一个MySQL从库复制延迟的案例时遇到了一个反直觉的现象在特定场景下启用并行复制配置了8个工作线程的从库其复制速度竟然比单线程复制还要慢4倍多。这个发现颠覆了我们多线程一定比单线程快的常规认知值得深入分析。1.1 问题现象描述环境配置MySQL版本8.0.40事务隔离级别RCRead Committed并行复制配置replica_parallel_workers8问题表现 从某个时间点开始从库的复制延迟持续增加且没有下降趋势。通过监控发现Relay_Master_Log_File和Exec_Master_Log_Pos都在变化但变化速度很慢主库写入量大的时间段没有出现延迟反而在写入量较小时出现延迟错误日志中出现大量锁等待超时报错1.2 初步排查思路首先使用mysql-binlog-time-extractor工具分析binlog排除了主库写入压力大的可能性。然后通过binlog_summary.py分析延迟时段的binlog文件发现操作模式高度一致操作频次最高的两张表都是biz_schema.tbl_product_service_mapping01主要操作类型为DELETE和INSERT且INSERT总是出现在对应DELETE之后这表明业务是通过先删除后插入的方式实现数据更新这种模式在特定条件下可能引发锁竞争问题。2. 问题复现与深入分析2.1 实验环境搭建为了准确复现问题我们搭建了测试环境创建测试表CREATE TABLE biz_schema.tbl_product_service_mapping01 ( id BIGINT AUTO_INCREMENT PRIMARY KEY, c1 INT, c2 INT, UNIQUE KEY uk_c1_c2 (c1, c2) );模拟业务操作模式-- 先删除 DELETE FROM biz_schema.tbl_product_service_mapping01 WHERE c1 ?; -- 后插入 INSERT IGNORE INTO biz_schema.tbl_product_service_mapping01 (c1, c2) VALUES (?, ?);配置从库并行复制SET GLOBAL replica_parallel_workers 8; SET GLOBAL replica_preserve_commit_order ON;2.2 锁等待问题定位通过performance_schema.data_locks和sys.innodb_lock_waits视图我们观察到以下锁等待链事务APID 10等待事务BPID 14持有的S,GAP锁事务BPID 14等待事务CPID 12持有的S,GAP锁事务CPID 12因提交顺序限制必须等待事务A提交这种循环等待导致三个线程相互阻塞最终触发锁等待超时1205错误。2.3 RC隔离级别下的GAP锁异常在Read Committed隔离级别下通常只会出现记录锁。但本案例中出现了GAP锁原因在于DELETE操作不会立即物理删除记录而是标记为delete-marked当INSERT操作遇到delete-marked记录时会对该索引项加S锁同时对该索引项的间隙加S,GAP锁这种加锁行为在row_ins_scan_sec_index_for_duplicate()函数中实现通过以下实验可以验证-- 会话1创建表并插入数据 CREATE TABLE test.t1( id BIGINT AUTO_INCREMENT PRIMARY KEY, c1 INT, c2 INT, UNIQUE KEY(c1,c2) ); INSERT INTO test.t1(c1,c2) VALUES(10512476,1),(10512476,2); -- 会话2暂停purge线程 FLUSH TABLES test.t2 FOR EXPORT; -- 会话1删除数据 DELETE FROM test.t1; -- 会话3插入数据 BEGIN; INSERT INTO test.t1(c1,c2,id) VALUES(10512476,1,18158557178); -- 查看锁信息 SELECT * FROM performance_schema.data_locks;3. 根本原因分析3.1 并行复制工作机制MySQL并行复制基于以下机制事务分组通过slave_parallel_workers指定工作线程数冲突检测WRITESET并行复制根据事务修改的主键/唯一索引判断是否冲突顺序保证replica_preserve_commit_orderON时保持主库提交顺序3.2 问题产生的四个关键因素业务模式采用DELETEINSERT方式更新数据索引设计存在唯一索引(uk_c1_c2)隔离级别RC下对delete-marked记录的特殊加锁并行配置replica_preserve_commit_orderON这四个因素共同作用导致了并行工作线程间产生锁等待提交顺序限制形成循环等待最终触发锁超时和事务重试4. 解决方案与优化建议4.1 应用层优化方案索引设计优化方案1将唯一索引改为普通二级索引ALTER TABLE biz_schema.tbl_product_service_mapping01 DROP INDEX uk_c1_c2, ADD INDEX idx_c1_c2(c1,c2);方案2使用联合主键替代自增主键唯一索引ALTER TABLE biz_schema.tbl_product_service_mapping01 DROP PRIMARY KEY, DROP INDEX uk_c1_c2, ADD PRIMARY KEY(c1,c2);更新逻辑优化用UPDATE替代DELETEINSERTUPDATE biz_schema.tbl_product_service_mapping01 SET c2 ? WHERE c1 ?;使用INSERT ON DUPLICATE KEY UPDATEINSERT INTO biz_schema.tbl_product_service_mapping01 (c1,c2) VALUES (?,?) ON DUPLICATE KEY UPDATE c2 VALUES(c2);4.2 数据库层优化方案调整并行复制配置SET GLOBAL replica_preserve_commit_order OFF;优点打破事务提交顺序限制避免循环等待限制不适用于Group Replication环境监控与告警-- 监控锁等待 SELECT * FROM sys.innodb_lock_waits; -- 监控复制延迟 SHOW SLAVE STATUS\G4.3 各方案性能对比我们在测试环境对以下方案进行了基准测试方案平均执行时间(s)锁等待次数适用场景原方案(唯一索引并行8线程)359.06高频不推荐单线程模式83.07无临时解决方案普通索引并行8线程33.50少量可接受修改索引关闭提交顺序保证21.11无非Group Replication环境改用UPDATE语句18.23无可修改业务逻辑5. 经验总结与最佳实践5.1 关键发现在RC隔离级别下对唯一索引的INSERT操作遇到delete-marked记录时仍会产生GAP锁并行复制性能不一定总优于单线程在特定场景下可能更差replica_preserve_commit_orderON可能加剧锁竞争问题5.2 MySQL并行复制使用建议评估业务模式避免高频的DELETEINSERT操作大批量更新操作考虑分批次执行合理配置参数[mysqld] replica_parallel_workers8 replica_preserve_commit_orderOFF # 非Group Replication环境 slave_transaction_retries10 # 适当增加重试次数监控指标Seconds_Behind_MasterSlave_SQL_Running_State锁等待统计5.3 故障排查checklist当遇到复制延迟时可以按以下步骤排查检查主从状态SHOW SLAVE STATUS\G分析binlog模式使用binlog_summary.py等工具检查锁等待SELECT * FROM sys.innodb_lock_waits查看错误日志定位超时和死锁信息简化测试场景单表复现问题6. 深度原理剖析6.1 InnoDB锁机制详解在RC隔离级别下对唯一索引的特殊加锁逻辑查找阶段在row_ins_scan_sec_index_for_duplicate()中检查唯一性约束如果发现delete-marked记录加S锁和S,GAP锁插入阶段在lock_rec_add_to_queue()中处理锁继承新插入记录会继承下一条记录的GAP锁purge阶段后台线程清理delete-marked记录被清理记录的锁会被释放6.2 并行复制实现原理MySQL 8.0的WRITESET并行复制事务分组算法基于事务修改的行的主键/唯一键计算hash相同hash值的事务分配到同一组冲突检测机制通过transaction_write_set_extraction配置支持XXHASH64和MURMUR32算法工作线程调度协调线程分配事务给工作线程工作线程执行非冲突事务6.3 性能影响因素矩阵影响并行复制性能的关键因素因素影响程度优化方向事务冲突率高减少热点数据操作工作线程数中根据CPU核心数配置提交顺序保证高非必要场景关闭网络延迟低优化主从网络从库硬件中提升IOPS和CPU7. 扩展思考与进阶优化7.1 其他可能引发类似问题的场景批量导入数据LOAD DATA INFILE在大表上的并发执行mydumper/myloader的并行导入Online DDL操作添加唯一索引时的校验阶段主键变更操作级联删除/更新外键约束引发的锁升级大事务中的多表操作7.2 高级调优技巧动态调整并行度-- 高峰期增加工作线程 SET GLOBAL replica_parallel_workers 16; -- 低峰期减少工作线程 SET GLOBAL replica_parallel_workers 4;工作线程绑定CPU核心[mysqld] slave_parallel_workers 8 slave_parallel_threads_pinned 4 # 绑定前4个核心监控工作线程负载SELECT THREAD_ID, PROCESSLIST_TIME, PROCESSLIST_STATE FROM performance_schema.threads WHERE NAME LIKE %worker%;7.3 MySQL 8.4的改进在最新MySQL 8.4版本中优化了WRITESET并行复制的冲突检测算法增加了slave_parallel_modeOPTIMISTIC模式改进了锁等待超时的处理机制升级建议-- 检查兼容性 SELECT version; -- 测试新特性 SET GLOBAL slave_parallel_mode OPTIMISTIC;
