Slave delay optimization method for mysql synchronization problem

In general, slave has a higher latency than master, and the underlying reason is that the replicated threads on slave cannot be truly concurrent. In short, on master the transaction commits are done in concurrent mode (predominating on the InnoDB engine), whereas on slave there is only one sql replicating thread, thread for binlog's apply, so it is no wonder that slave is far behind master at high concurrency.

ORACLE 5.6 supports multi-threaded replication. Concurrent multi-threaded replication on slave can be achieved with slave_parallel_workers. However, it can only support concurrent replication among multiple database under one instance, and cannot really achieve concurrent replication of multiple tables. Therefore, slave is still unable to catch up with master in time when the concurrent load is large, and we need to find ways to optimize it.

Another important reason is that traditional MySQL replication is asynchronous (asynchronous), which means that after master commits, it is applied again on slave and is not really synchronous. Even the later Semi-sync Repication(semi-synchronous replication) was not true synchronization because it only guaranteed that the transaction was delivered to slave, but it did not require waiting for confirmation that the transaction was committed. Since it's asynchronous, there must be some delay. Therefore, MySQL replication is not technically MySQL synchronization (Virgo interviewers will probably brush off the first rule of MySQL synchronization during an interview).

In addition, many people think that slave is less important and therefore will not provide the same level of configuration as master. Some are running multiple instances on even worse servers.

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

1, the use of MariaDB distribution, it achieved a relatively true sense of parallel replication, its effect is far 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, just wait for the official 5.7 release.

About MariaDB Parallel Replication specific refer to: Replication and Binary Log Server System Variables # slave_parallel_threads � MariaDB Knowledge Base

If you do not specify a primary key, you will need to scan the entire table for each change in row mode. Especially for large tables, the delay will be more serious, and even cause the entire slave library to be suspended.

3, the application side to do more things, let MySQL end to do less, especially IO related activities, such as: front-end memory CACHE or local write queue, merge multiple read and write to 1, or even eliminate some write requests;

4. Carry out appropriate database and table splitting strategies to reduce the duplication pressure of single database and single table and avoid the replication delay of the whole instance caused by the pressure of single database and single table;

Other methods to improve ES88en performance, according to the quality of the effect, I made a simple sort:

1, replaced with SSD, or PCIe SSD and other IO equipment, the improvement of IOPS capacity is hundreds of times, ten thousand times, or even several hundred thousand times of the ordinary 15K SAS plate;

2. Increase the physical memory, increase the size of InnoDB Buffer Pool accordingly, let more hot data in memory, reduce the frequency of physical IO;

3, Adjust the file system to XFS or ReiserFS, compared with ext3 can greatly improve the IOPS capability. More robust IOPS performance than ext4 at high IOPS pressures (it is thought that the XFS will have major problems in particular scenarios, but we have not encountered any other cases except for data loss when less than 10% of the disk space is left);

4, Adjust RAID level to raid 1+0, which can improve IOPS performance compared to raid1, raid5, etc. If all SSD devices are available, 2 RAID disks can be converted into RAID 1, or multiple fast disks can be converted into RAID 5(and global hot standby disks can be set to improve the fault tolerance of the array), or even some local rich users can directly form multiple SSD disks into RAID 50;

5, Adjust RAID to WB or FORCE WB, details please refer to: common PC server array card, hard disk health monitoring and PC server array card management simple manual;

6. Adjust the kernel io to scheduler in favor of deadline. If SSD is used, then noop policy can be used.

Other more methods, welcome to help add :)