Slave latency causes and optimization methods, and slave latency Optimization

Slave latency causes and optimization methods, and slave latency Optimization

Reprinted: http://imysql.com/2015/04/12/mysql-optimization-case-howto-resolve-slave-delay.shtml

Generally, slave has a large latency compared to the master. The root cause is that the replication thread on the slave cannot truly achieve concurrency. Simply put, on the master node, the transaction is committed in the concurrency mode (mainly based on the InnoDB engine), while on the slave, the replication thread has only one SQL thread for binlog apply, so it is no wonder that slave will lag far behind the master in high concurrency.

ORACLE MySQL 5.6 supports multi-thread replication. You can configure the slave_parallel_workers option to achieve multi-thread concurrent replication on the slave. However, it can only support concurrent replication between multiple databases under an instance, and cannot truly achieve multi-Table concurrent replication. Therefore, when there is a large concurrent load, slave still cannot catch up with the master in time, and it is necessary to find a way to optimize it.

Another important reason is that the traditional MySQL replication is asynchronous (asynchronous). That is to say, after the master node is submitted, it is applied again on the slave, which is not a real synchronization. Even the subsequent Semi-sync Repication (Semi-synchronous replication) is not a real synchronization because it only guarantees that the transaction is transmitted to the slave, but does not need to wait until the transaction is committed successfully. Since it is asynchronous, there must be latency. Therefore, in a strict sense, MySQL replication cannot be called MySQL synchronization (the interviewer of Virgo may say that MySQL synchronization is always flushed during the interview ).

In addition, slave is not that important in many people's ideas, so it will not provide servers with the same configuration level as the master. Some even use even worse servers and run multiple instances on them.

Based on these two main reasons, slave can try the following methods to keep up with the progress of the master as soon as possible: