MySQL5.7 新特性： Multi-threaded Slave 多线程复制

Contents

1. 什么是MTS
2. 为什么要用MTS
3. 开启MTS的重要参数
4. 重要组件
5. Binlog 和 MTS
6. 顺序

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

1 2	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

1 2	Master执行顺序: last_committed=0,sequence_number=1,2,3,4 slave执行顺序：有可能就是 last_committed=0,sequence_number=1,2,3,4