MySQL 5.7 under Master-slave Replication Latency Solution

In MySQL under the master-slave replication delay problem has been in the industry is relatively large, master-slave delay due to the network disk and other related factors, but the main impact is that the master too busy write causes slave not effective from Relay_ Log to read the latest related records, so for the data real-time high-performance business slave data is not the latest has a certain delay, at this time using master-slave read and write separation is a bit of the chicken, can not do slave on the latest real-time data can be found, Most of the slave are doing some data queries that are not very high on real-time data requirements.

And to the MySQL version 5.6 began to introduce multi-threaded master-slave replication mechanism, it is well known that the work of MySQL is a single process multi-threaded way, then the size of the thread will greatly affect the efficiency of MySQL, and in early MySQL master and slave is a single-threaded, so that the master-slave replication in addition to the relevant objective factors are The impact of the body. But in MySQL 5.6, the multi-threaded master-slave replication is not quite perfect, because in MySQL 5.6, the master-slave replication multithreading is a thread processing a master-slave replication, and in most of the production environment is mostly in a database do a lot of operations, So this MySQL multi-threaded master-slave replication and the previous version is the same as the single-threaded master-slave replication, not much practical significance. To this end in MySQL 5.7 version of the multi-threaded master-slave replication to further improve, in MySQL 5.7 in accordance with the logical clock (CPU-like processing mechanism) to handle multi-threading, even in the semi-synchronous replication Semisync or use performance Schema table to monitor the replication thread, so the master-slave replication in MySQL 5.7 directly due to MySQL itself caused by the delay is greatly reduced, the configuration is very simple, as follows in Salve configuration:

mysql> show global variables like  ' slave_parallel_workers '; #默认是0, that is, single thread +------------ ------------+-------+| variable_name          |  value |+------------------------+-------+| slave_parallel_workers | 0      |+------------------------+-------+1 row in set  (0.01 sec) mysql>  set global slave_parallel_workers = 4; #根据实际情况决定开启多少个线程用于主从复制1  row in set   (0.01 sec) mysql> show global variables like  '%slave_parallel_type% '; The default is that the multithreading mechanism is a thread that handles a library +---------------------+----------+| variable_name        | value    |+---------------------+----------+| slave_parallel_type |  database |+---------------------+----------+1 row in set  (0.01 sec) MySQL > stop slave; #修改多线程机制工作类型需要停止slaveQuery  OK, 0 rows affected  (0.21 sec) mysql> set  global slave_parallel_type= ' Logical_clock ';1 row in set  (0.01 sec) MySQL > start slave;    Query OK, 0 rows affected  (0.08  SEC)

At this point in accordance with the actual situation to decide how many threads to use for master-slave replication, at this time look at the list of threads on slave:

mysql> show full processlist;+----+-------------+-----------+------+---------+------+------- -------------------------------------------------+-----------------------+| id | user         | Host      | db    | Command | Time | State                                                     | Info                   |+----+-------------+-----------+------+---------+------+----------- ---------------------------------------------+-----------------------+| 16 | root         | localhost | null | query   |    0  | starting                                                 | show  full processlist | |  29 | system user |            | null | connect |    3 | waiting for master  to send event                        | NULL                   | |  30 | system user  |           | null | connect |     3 | slave has read all relay log; waiting  for more updates | NULL                   | |  31 | system user |            | null | connect |    3 | waiting for an  event from coordinator                   | NULL                   | |  32 | system user |            | null | connect |    3 | waiting for an event from coordinator                   | null                   | |  33 | system user |            | null | connect |    3 | waiting for an  event from coordinator                   | NULL                   | |  34 | system user |            | null | connect |    3 | waiting for an event from coordinator                   | NULL                   |+----+-------------+-----------+------+---------+ ------+--------------------------------------------------------+-----------------------+7 rows in  set  (0.00 SEC)

This will successfully open the MySQL 5.7 version of multi-threaded master-slave replication, after configuration in the slave DataDir directory will have the same number of threads set according to the slave_parallel_workers number of threads files:

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M01/05/8B/wKiom1mm6I7DKyyPAAFL7bE5YRs161.png-wh_500x0-wm_ 3-wmp_4-s_3393508641.png "title=" Worker_relay_log "alt=" Wkiom1mm6i7dkyypaafl7be5yrs161.png-wh_50 "/>

Finally, the configured variable parameters are written to my.cnf, allowing the next reboot to take effect:

Slave_parallel_workers = 4slave_parallel_type= Logical_clock

This article from "Jim's Technical Essay" blog, declined to reprint!

MySQL 5.7 under Master-slave Replication Latency Solution