Case Study of MSQL slave optimization method with high latency

ORACLE MySQL version 5.6 begins to support multi-threaded replication, and configuration options slave_parallel_workers enables multi-threaded concurrent replication on slave. However, it can only support concurrent replication between multiple database instances under one instance, and can not really do multiple tables concurrent replication. Therefore, in a larger concurrent load, slave still have no way to catch up with 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 submitted, before the slave on the application again, is not a true sense of synchronization. Even the later Semi-sync repication (semi-synchronous replication) is not a true synchronization, because it only guarantees that the transaction is routed to the slave, but does not require that the transaction commit succeeds until it is confirmed. Since it's asynchronous, that's sure to be a little late. Therefore, strictly speaking, MySQL replication can not be called MySQL synchronization (Virgo interviewers may be in the interview will be said to the MySQL synchronization of all the brush off OH).

In addition, many people in the concept of slave is relatively less important, so will not provide the same level of server with master configuration. Some even not only use worse servers, but also run multiple instances on top.

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

With the MARIADB release, it achieves a relatively real parallel replication, which is much better than Oracle MySQL. In my scene, 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 to wait for the official 5.7 major release;
Please refer to the parallel Replication of mariadb for details: Replication and Binary Log Server System variables#slave_parallel_threads? MARIADB Knowledge Base
Each table must explicitly specify a primary key, if you do not specify a primary key, will result in row mode, each modification should be full table scan, especially the large table is very terrible, the delay will be more serious, and even lead to the entire slave library is suspended, can refer to the case: the lack of MySQL primary key lead to the reserve hang;
The application side to do more things, so that the MySQL side less work, especially with IO-related activities, such as: the front-end through memory cache or local write queue, etc., merge multiple read and write for once, or even eliminate some write requests;
The appropriate sub-library, the table strategy, reduces the library Tanku duplication pressure, avoids because the library Tanku the pressure to cause the entire instance the replication delay;
Other ways to improve IOPS performance, based on the results, I made a simple sort of:
Change to SSD, or PCIe SSD, etc. io equipment, its IOPS ability is promoted is the ordinary 15K SAS disk number to multiply, million times, even hundreds of thousands of times times the meter;
Increase the physical memory, the corresponding increase InnoDB Buffer pool size, so that more hot data in memory, reduce the occurrence of physical IO frequency;
Adjust the file system to XFS or ReiserFS, compared to ext3 can greatly improve IOPS capabilities. Under high ioPS pressure, compared to EXT4 has a more robust IOPS performance (some people think that XFS in a special scenario will be a big problem, but we have less than 10% of the remaining disk space when throwing data, other not encountered);
Adjust the raid level to raid 1+0, which improves IOPS performance compared to RAID1, RAID5, and more. If you have all SSD devices, you can make RAID 1 on 2 disks, or as many as RAID 5 (and you can set up a global hot spare, improve array fault tolerance), and even some tyrants users to make a RAID 50 directly from multiple SSD disks;
Adjust raid write cache policy for WB or Force WB, please refer to: Common PC Server array card, hard disk health monitoring and PC server array card management simple handbook;
Adjust the IO Scheduler of the kernel, prioritize the use of deadline, and if it is SSD, you can use the NoOp policy, and the performance of the IOPS is increased by at least several times in individual cases compared to the default CFQ.