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的重要参数

重要组件

1. IO thread并没有改变
2. SQL thread 会变成Coordinator线程
3. 会新增很多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