Summary of optimization methods for slave delay (MySQL optimization)

"Http://www.cstor.cn/textdetail_9146.html"
In general, the slave relative master delay is large, the root cause is that the replication thread on the slave is not able to really do concurrency. Simply put, in master is the concurrency mode (mainly InnoDB engine) to complete the transaction committed, and on slave, the replication thread has only one SQL thread for Binlog apply, so no wonder slave in high concurrency will lag behind master

ORACLE MySQL version 5.6 starts with multi-threaded replication, and configuration option Slave_parallel_workers enables multithreaded concurrent replication on slave. However, it can only support the concurrent replication of multiple database under one instance, and cannot really do multi-table concurrent replication. Therefore, in the large concurrent load, slave still have no way to catch up with the master in time, need to find ways to optimize.

Another important reason is that the traditional MySQL replication is asynchronous (asynchronous), that is, after the master is submitted, it is only applied again on the slave, not in the true sense of synchronization. Even the later Semi-sync repication (semi-synchronous replication) is not true, because it only guarantees that the transaction is routed to slave, but does not require waiting until the confirmation transaction commits successfully. Since it is asynchronous, there must be some delay. Therefore, in a strict sense, MySQL replication is not called MySQL synchronization (Virgo interviewers may be in the interview to say that MySQL sync all the same).

In addition, in many people's minds, slave is relatively less important, so it does not provide the same level of server as the master. Some even use worse servers, and run multiple instances on top of them.

Combined with these two main reasons, slave wants to keep up with Master's progress as soon as possible, and can try the following methods:

With the MARIADB release, it achieves parallel replication in a relatively real sense, with much better results than Oracle MySQL. In my scenario, using MARIADB as an example of slave almost always keeps up with master in time. If you do not want to use this version, then be honest waiting for the official version of the 5.7 release it; for mariadb parallel Replication for details, refer to: Replication and Binary Log Server System variables# SLAVE_PARALLEL_THREADS-MARIADB Knowledge Base

Each table to explicitly specify the primary key, if you do not specify a primary key, it will result in the row mode, each modification to a full table scan, especially the large table is very scary, the delay will be more serious, and even cause the entire slave library is suspended, can refer to the case: the lack of MySQL primary key causes the standby store hang;

The application side to do more things, so that the MySQL side less work, especially the IO-related activities, such as: The front end through the memory cache or local write queue, etc., merge multiple reads and writes as a single, or even eliminate some write requests;

The appropriate sub-database, sub-table strategy, reduce the copy pressure of the Library single table, to avoid the single table of the library due to the pressure of the entire instance of the replication delay;

other ways to improve IOPS performance , depending on the effect, I made a simple sort:

More than SSD, or PCIe SSD and other IO devices, the increase in IOPS capacity is the average 15K SAS disk number hundred times, multiples, or even hundreds of thousands of times times the meter;

Increase the physical memory, correspondingly increase the size of the InnoDB Buffer pool, so that more hot data in memory, reduce the frequency of physical IO;

Adjusting the file system to XFS or ReiserFS can greatly improve IOPS capabilities compared to ext3. More robust IOPS performance compared to EXT4 under high iops pressure (some people think that XFS has a lot of problems in a particular scenario, but we have not yet encountered the loss of data except when the remaining disk space is less than 10%);

The RAID level is tuned to raid 1+0, which improves IOPS performance compared to RAID1, RAID5, and more. If you have all the SSD device, you can make 2 disk RAID 1, or more fast disk into RAID 5 (and can set up a global hot spare, improve array fault tolerance), and even some local tyrants users directly to a number of SSD disks composed of RAID 50;

Adjust the raid's write cache policy to WB or Force WB, for details, please refer to: Common PC Server array card, hard disk health monitoring and simple manual for PC server array card management;

Adjust the kernel IO Scheduler, priority to use deadline, if the SSD, you can use the NoOp policy, compared to the default CFQ, the performance of the IOPS on the individual guest is at least a few times higher.

Summary of optimization methods for slave delay (MySQL optimization)