mysql多线程优化并行复制
sql单线程优化，mts并行复制
MySQL 的主从复制延迟一直是受开发者最为关注的问题之一，MySQL 从 5.6 版本开始追加了并行复制功能，目的就是为了改善复制延迟问题，并行复制称为enhanced multi-threaded slave（简称MTS）。
master基于组提交(group commit)来实现的并发事务分组，再由slave通过SQL thread将一个组提交内的事务分发到各worker线程，实现并行应用。
- MySQL 的复制是基于 binlog 的。
- MySQL 复制包括两部分，从库中有两个线程：IO 线程和 SQL 线程。
- IO 线程主要是用于拉取接收 Master 传递过来的 binlog，并将其写入到 relay log.
- SQL 线程主要负责解析 relay log，并应用到 slave 中。
- IO 和 SQL 线程都是单线程的，然而master却是多线程的，所以难免会有延迟，为了解决这个问题，多线程应运而生了。
- IO 没必要多线程，因为 IO 线程并不是瓶颈。
- SQL 多线程，目前最新的5.6，5.7，8.0 都是在 SQL 线程上实现了多线程，来提升 slave 的并发度，减少复制延迟。
查看进程信息
mysql> show processlist;
+----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 483 | Waiting on empty queue | NULL |
| 8 | root | localhost | NULL | Query | 0 | init | show processlist |
| 9 | system user | connecting host | NULL | Connect | 444 | Waiting for source to send event | NULL |
| 10 | system user | | NULL | Query | 414 | Replica has read all relay log; waiting for more updates | NULL |
| 11 | system user | | NULL | Connect | 414 | Waiting for an event from Coordinator | NULL |
| 12 | system user | | NULL | Connect | 444 | Waiting for an event from Coordinator | NULL |
| 13 | system user | | NULL | Connect | 444 | Waiting for an event from Coordinator | NULL |
| 14 | system user | | NULL | Connect | 444 | Waiting for an event from Coordinator | NULL |
+----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+
8 rows in set, 1 warning (0.00 sec)
Replica has read all relay log; waiting for more updates
回放relay 日志，该线程是SQL 线程
Waiting for source to send event
等待主库发送更多的事件，该线程是IO 线程负责接收主库binlog 日志保存为本地relay 日志。
修改从库配置
vim /etc/my.cnf.d/mysql-server.cnf
server-id=2
slave-parallel-type=logical_clock #以组方式提交
slave-parallel-workers=8 #8个线程
master_info_repository=table #存放master信息的形式，默认是file
relay_log_info_repository=table #存放relay日志信息的形式设置，默认是file
relay_log_recovery=ON #开启relay日志恢复
查看进程信息，发现有更多的Waiting for
mysql> show processlist;
+----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+
| 5 | system user | connecting host | NULL | Connect | 38 | Waiting for source to send event | NULL |
| 6 | system user | | NULL | Query | 38 | Replica has read all relay log; waiting for more updates | NULL |
| 7 | system user | | NULL | Connect | 38 | Waiting for an event from Coordinator | NULL |
| 8 | event_scheduler | localhost | NULL | Daemon | 38 | Waiting on empty queue | NULL |
| 10 | system user | | NULL | Connect | 38 | Waiting for an event from Coordinator | NULL |
| 12 | system user | | NULL | Connect | 38 | Waiting for an event from Coordinator | NULL |
| 13 | system user | | NULL | Connect | 38 | Waiting for an event from Coordinator | NULL |
| 14 | system user | | NULL | Connect | 38 | Waiting for an event from Coordinator | NULL |
| 15 | system user | | NULL | Connect | 38 | Waiting for an event from Coordinator | NULL |
| 16 | system user | | NULL | Connect | 38 | Waiting for an event from Coordinator | NULL |
| 17 | system user | | NULL | Connect | 38 | Waiting for an event from Coordinator | NULL |
| 18 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------------+------+---------+------+----------------------------------------------------------+------------------+
12 rows in set, 1 warning (0.00 sec)
刚刚设置的master，elaylog，worker 信息，会以表形式存储于数据库
mysql> use mysql;mysql> show tables like '%slave%';
+---------------------------+
| Tables_in_mysql (%slave%) |
+---------------------------+
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
+---------------------------+
3 rows in set (0.01 sec)
官方文档中对relay_log_recovery参数的解释
Enables automatic relay log recovery immediately following server startup. The recovery process creates a new relay log file, initializes the SQL thread position to this new relay log, and initializes the I/O thread to the SQL thread position. Reading of the relay log from the master then continues.
在从库中将relay_log_recovery不设置或者设置为off，如果碰到上面的情形，从库会丢失那些没有应用的日志，主从会不一致。
在从库中将relay_log_recovery设置为on，假如果碰到上面的情形，从库会自动放弃所有未执行的relay log，重新生成一个relay log，并将从库的io线程的position重新指向新的relay log。并将sql线程的position退回到跟io线程的position保持一致，重新开始同步，这样在从库中事务不会丢失。这个参数建议开启。