The slave delay of the Mysql synchronization problem is very great optimization method _mysql

Generally speaking, the slave relative master delay is large, the root cause is that the replication thread on slave is not able to really concurrency. Simply put, the concurrency mode (InnoDB engine) completes the transaction submission on master, while on slave, the replication thread has only one SQL thread for Binlog apply, so it is no wonder that slave is far behind master in high concurrency.

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:

1, the use of mariadb distribution, it achieved a relatively real sense of parallel replication, its effect 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;

For parallel Replication specific reference to MARIADB: Replication and Binary Log Server System variables#slave_parallel_threads–mariadb Knowledge Base

2, each table must explicitly specify the 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;

3, the application side to do more things, so that the MySQL side less work, especially and IO-related activities, such as: the front-end through memory cache or local write queue, and so on, merging multiple reads and writes for once, or even eliminate some write requests;

4, the appropriate sub-library, the table strategy to reduce the library Tanku replication pressure, to avoid the library Tanku caused by the pressure of the whole instance of the replication delay;

Other ways to improve IOPS performance, based on the results, I made a simple sort of:

1, the replacement of SSD, or PCIe SSD, such as IO equipment, its IOPS ability to upgrade the ordinary 15K SAS disk number to a hundredfold, million times, or even hundreds of thousands of times times the meter;

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

3, adjust the file system for XFS or ReiserFS, compared to ext3 can greatly improve the IOPS ability. 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);

4. Adjust RAID level to raid 1+0, it can improve IOPS performance compared with RAID1, RAID5 etc. 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;

5, adjust raid write cache strategy for WB or Force WB, please refer to: Commonly used PC server array card, hard disk health monitoring and PC server array card management summary manual;

6, adjust the core IO Scheduler, the priority to use deadline, if it is SSD, you can use NoOp policy, compared to the default CFQ, the IOPS performance in a number of cases to increase at least several times.

Other more methods, welcome to help you add:)