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保持一致，重新开始同步，这样在从库中事务不会丢失。这个参数建议开启。