Optimizing MySQL master-slave slave delay problem

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 application, so it is no wonder slave in high concurrency will lag far 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:

(1) with the MARIADB release, it achieves a relatively true parallel copy, which is much better than Oracle MySQL. In my scenario, using MARIADB as an example of slave almost always keeps up with master in time. If you don't want to use this version, be honest and wait for the official 5.7 version to be released.
Parallel Replication for MariaDB refer to: Replication and Binary Log Server System variables#slave_parallel_threads–mariadb Knowledge Base
(2) Each table to explicitly specify the primary key, if not specify the primary key, it will result in the row mode, each modification to the 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;
(3) 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;
(4) 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 whole instance of replication delay;
(5) Other ways to improve the performance of IOPS, based on the effect of the pros and cons, I made a simple sort:
1. Replace the 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;
2. 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;
3. Adjust the file system to XFS or ReiserFS, which can greatly improve IOPS capability 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%);
4. Adjust the raid level 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 raid (and can set up a global hot spare, improve array fault tolerance), even some local tyrants users directly to a number of SSD disk RAID 50;
5. 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;
6. Adjust the kernel IO Scheduler, priority to use the deadline, if the SSD, you can use the NoOp policy, compared to the default CFQ, the performance of IOPS in some cases is at least several times higher.

Optimizing MySQL master-slave slave delay problem