ORACLEMySQL5.6 supports multi-thread replication. you can configure the slave_parallel_workers option to achieve multi-thread concurrent replication on the slave. However, it can only support concurrent replication between multiple databases under an instance, and cannot truly achieve multi-table concurrent replication. Therefore, when there is a large concurrent load, slave still cannot catch up with the master in time, and it is necessary to find a way to optimize it.
Generally, slave has a large latency compared to the master. the root cause is that the replication thread on the slave cannot truly achieve concurrency. Simply put, on the master node, the transaction is committed in the concurrency mode (mainly based on the InnoDB engine), while on the slave, the replication thread has only one SQL thread for binlog apply, so it is no wonder that slave will lag far behind the master in high concurrency.
ORACLE MySQL 5.6 supports multi-thread replication. you can configure the slave_parallel_workers option to achieve multi-thread concurrent replication on the slave. However, it can only support concurrent replication between multiple databases under an instance, and cannot truly achieve multi-table concurrent replication. Therefore, when there is a large concurrent load, slave still cannot catch up with the master in time, and it is necessary to find a way to optimize it.
Another important reason is that the traditional MySQL replication is asynchronous (asynchronous). That is to say, after the master node is submitted, it is applied again on the slave, which is not a real synchronization. Even the subsequent Semi-sync Repication (Semi-synchronous replication) is not a real synchronization because it only guarantees that the transaction is transmitted to the slave, but does not need to wait until the transaction is committed successfully. Since it is asynchronous, there must be latency. Therefore, in a strict sense, MySQL replication cannot be called MySQL synchronization (the interviewer of Virgo may say that MySQL synchronization is always flushed during the interview ).
In addition, slave is not that important in many people's ideas, so it will not provide servers with the same configuration level as the master. Some even use even worse servers and run multiple instances on them.
Based on these two main reasons, slave can try the following methods to keep up with the progress of the master as soon as possible:
- The MariaDB release version is used to implement parallel replication in a relatively real sense, which is far better than ORACLE MySQL. In my scenario, MariaDB is used as a server load balancer instance, and it is almost always able to keep up with the master in time. If you do not want to use this version, please wait for the official version 5.7 to be released. for details about Parallel Replication of MariaDB, see: Replication and Binary Log Server System Variables # slave_parallel_threads-MariaDB Knowledge Base
- A primary key must be explicitly specified for each table. If no primary key is specified, a full table scan is required for each modification in row mode, especially for large tables, latency is more serious, and even the entire slave database is suspended. For more information, see the case: hang of the slave database due to the lack of the mysql primary key;
- The application end does more things to make MySQL end do less things, especially IO-related activities. for example, the front-end uses the memory CACHE or local write queue to merge multiple reads and writes for one time, even some write requests are eliminated;
- Appropriate database/table sharding policies are implemented to reduce the copy pressure on a single database/table, so as to avoid the replication delay of the entire instance due to the pressure on a single database/table;
- Based on the advantages and disadvantages of other methods to improve IOPS performance, I made a simple sorting:
- Switching to an SSD, or PCIe SSD or other I/O devices, the IOPS capability is improved by hundreds, ten thousand times, or even several 100,000 times of ordinary 15 K SAS disks;
- Increase the physical memory, increase the InnoDB Buffer Pool size, and put more hot data in the memory to reduce the frequency of physical IO;
- By adjusting the file system to XFS or ReiserFS, the IOPS capability is greatly improved compared with ext3. Under high IOPS pressure, there is more stable IOPS performance than ext4 (some people think that XFS will have a big problem in special scenarios, however, we have not encountered any loss of data except when the remaining disk space is less than 10% );
- Adjust the RAID level to raid 1 + 0, which improves IOPS performance compared with RAID 1 and raid 5. If you already have all the SSD devices, you can make two disks into RAID 1, or how fast the disk can be made into RAID 5 (and you can set a global hot spare disk to improve array fault tolerance ), even some local tyrants directly make multiple SSD disks into RAID 50;
- Adjust the RAID write cache policy to WB or force wb. for details, refer to: commonly used PC server array cards, hard disk health monitoring, and PC server array card management simple manual;
- Adjust the I/O schedline of the kernel and use deadline first. if SSD is used, the noop policy can be used. The performance improvement of IOPS is at least several times higher than the default cfq.
