MySQL5.7 新特性： Multi-threaded Slave 多线程复制
https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html
什么是MTS
一句话概括：通过组提交的方式 master怎么并行，slave就怎么并行。
- 关键因素：组提交
# 组提交
## 哪些需要组提交
1. transaction prepare
2. binlog write
3. transaction commit
## MySQL 5.1
1. transaction prepare #fsync
2. binlog write #fsync
3. transaction commit #fsync
总结：每个事务都需要三次fsync
## MySQL 5.5
1. transaction prepare #fsync
2. binlog write #group fsync
3. transaction commit #fsync
总结：binlog 可以组提交了
## MySQL 5.6
1. transaction prepare # fsync
2. binlog write # group fsync
3. transaction commit # 不需要fsync了，因为1，2都fsync，就能保证整个事务提交
总结：少了最后一步的fsync，性能提升很多
## MySQL 5.7
1. transaction prepare # 不fsync
2. binlog write # 在写入binlog之前，去group fsync prepare log。 然后再group fsync binlog
3. transaction commit # 不需要fsync了，因为1，2都fsync，就能保证整个事务提交
总结：相当于1，2 合在一起组提交，性能提升更多
为什么要用MTS
一句话概括：解决单线程复制的延迟问题
note1：当master有多个线程在写数据，那么MTS效果会非常好
note2：如果master对大表进行DDL，这样的延迟是没办法避免的
开启MTS的重要参数
| 参数 | comment | 默认配置 | 推荐配置 | 调整方式 |
|---|---|---|---|---|
| slave_parallel_workers | applier threads数量 | 0 | 16 | dynamic |
| slave_parallel_type | 并行方式 | DATABASE | LOGICAL_CLOCK | dynamic |
| slave_preserve_commit_order | 并行排序提交 | 0 | 1 | dynamic |
| master_info_repository | master_info持久化方式 | FILE | DATABASE | static |
| relay_log_info_repository | relay_info持久化方式 | FILE | DATABASE | static |
| relay_log_recovery | 重新获取relay log | 0 | 1 | static |
重要组件
- IO thread并没有改变
- SQL thread 会变成Coordinator线程
- 会新增很多work线程来受Coordinator调度
+-----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 127 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 128 | system user | | NULL | Connect | 457 | Waiting for master to send event | NULL |
| 129 | system user | | NULL | Connect | 456 | Slave has read all relay log; waiting for more updates | NULL |
| 130 | system user | | NULL | Connect | 457 | Waiting for an event from Coordinator | NULL |
| 131 | system user | | NULL | Connect | 457 | Waiting for an event from Coordinator | NULL |
| 132 | system user | | NULL | Connect | 457 | Waiting for an event from Coordinator | NULL |
| 133 | system user | | NULL | Connect | 457 | Waiting for an event from Coordinator | NULL |
| 134 | system user | | NULL | Connect | 457 | Waiting for an event from Coordinator | NULL |
| 135 | system user | | NULL | Connect | 457 | Waiting for an event from Coordinator | NULL |
| 136 | system user | | NULL | Connect | 457 | Waiting for an event from Coordinator | NULL |
| 137 | system user | | NULL | Connect | 457 | Waiting for an event from Coordinator | NULL |
| 138 | system user | | NULL | Connect | 457 | Waiting for an event from Coordinator | NULL |
| 139 | system user | | NULL | Connect | 457 | Waiting for an event from Coordinator | NULL |
| 140 | system user | | NULL | Connect | 457 | Waiting for an event from Coordinator | NULL |
| 141 | system user | | NULL | Connect | 457 | Waiting for an event from Coordinator | NULL |
| 142 | system user | | NULL | Connect | 457 | Waiting for an event from Coordinator | NULL |
| 143 | system user | | NULL | Connect | 457 | Waiting for an event from Coordinator | NULL |
+-----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
17 rows in set (0.00 sec)Binlog 和 MTS
同一个last_committed 可以并行执行
同一个last_committed 中的sequence_number 默认是无序的
#160628 16:29:10 server id 12616406 end_log_pos 259 CRC32 0x9565260a GTID last_committed=0 sequence_number=1
#160628 16:29:25 server id 12616406 end_log_pos 427 CRC32 0xaa1d4add GTID last_committed=0 sequence_number=2
#160628 16:29:25 server id 12616406 end_log_pos 682 CRC32 0x0715f36a GTID last_committed=0 sequence_number=3
#160628 16:29:25 server id 12616406 end_log_pos 937 CRC32 0x2998c5ed GTID last_committed=0 sequence_number=4
#160628 16:29:25 server id 12616406 end_log_pos 1192 CRC32 0xd58951f3 GTID last_committed=4 sequence_number=5
#160628 16:29:25 server id 12616406 end_log_pos 1447 CRC32 0xbf77ba5f GTID last_committed=4 sequence_number=6
#160628 16:29:25 server id 12616406 end_log_pos 1702 CRC32 0x3e74905f GTID last_committed=4 sequence_number=7
#160628 16:29:25 server id 12616406 end_log_pos 1957 CRC32 0xc31cbd6d GTID last_committed=4 sequence_number=8顺序
- 当slave_preserve_commit_order=0时
没有办法保证顺序，在恢复的过程中会有问题,到时候你怎么start slave 呢？
start slave until SQL_AFTER_MTS_GAPS ; reset slave
Master执行顺序: last_committed=0,sequence_number=1,2,3,4
slave执行顺序： 有可能就是 last_committed=0,sequence_number=1,4,3,2- 当slave_preserve_commit_order=1时
后一个sequence_number提交的时候，会等待前一个sequence_number完成。
Waiting for preceding transaction to commit
Master执行顺序: last_committed=0,sequence_number=1,2,3,4
slave执行顺序： 有可能就是 last_committed=0,sequence_number=1,2,3,4时间： 2024-09-29 06:15:53