Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to optimize Slave delay in MySQL, Xiaobian thinks it is quite practical, so share it with you for a reference, I hope you can gain something after reading this article.

In general, slave latency is higher than master latency, and the root cause is that replication threads on slave cannot be truly concurrent. Simply put, on master it is concurrency mode (mainly InnoDB engine) to complete transaction commit, while on slave, replication thread only has one sql thread for binlog apply, so it is no wonder slave lags far behind master when high concurrency.

ORACLE MySQL version 5.6 supports multithreaded replication. Configure slave_parallel_workers to achieve multithreaded concurrent replication on slave. However, it can only support concurrent replication between multiple databases under one instance, and cannot truly achieve concurrent replication of multiple tables. Therefore, when the concurrent load is large, the slave still has no way to catch up with the master in time, and needs to find ways to optimize it.

Another important reason is that traditional MySQL replication is asynchronous, that is to say, after the master commits, it is applied again on the slave, not in the true sense of synchronization. Even the later Semi-sync Repication is not true synchronization, because it only guarantees that the transaction will be delivered to the slave, but does not require waiting until the transaction is successfully committed. Since it is asynchronous, there must be some delay. Therefore, MySQL replication cannot be called MySQL synchronization in a strict sense (Virgo interviewers may brush out all MySQL synchronization during the interview).

In addition, in many people's minds, slaves are relatively unimportant, so they will not provide servers with the same configuration level as masters. Some even use worse servers and run multiple instances on them.

Combining these two main reasons, slave wants to keep up with master as much as possible, and can try the following methods:

With MariaDB distribution, it achieves parallel replication in a relatively real sense, and its effect is much better than ORACLE MySQL. In my scenario, using MariaDB as an instance of slave almost always keeps up with the master in time. If you don't want to use this version, then wait for the official version 5.7 to be released.

Parallel Replication of MariaDB: Replication and Binary Log Server System Variables#slave_parallel_threads - MariaDB Knowledge Base

Each table must explicitly specify the primary key. If the primary key is not specified, it will cause the whole table to be scanned every time it is modified in row mode, especially for large tables. The delay will be more serious, and even the entire slave library will be suspended. Please refer to the case: mysql lack of primary key leads to standby library hang;

Do more things on the application side to make MySQL do less, especially IO-related activities, such as: front-end through memory CACHE or local write queue, merge multiple reads and writes into one, and even eliminate some write requests;

Carry out appropriate sub-library and sub-table strategies to reduce the replication pressure of single library and single table, and avoid the replication delay of the whole instance due to the pressure of single library and single table;

There are several other ways to improve IOPS performance. According to the effect, I have made a simple ranking:

Replace it with SSD, PCIe SSD and other IO devices, and its IOPS capacity will be improved by hundreds, thousands, or even hundreds of thousands of times that of ordinary 15K SAS disks;

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

Adjusting the file system to XFS or ReiserFS can greatly improve IOPS capacity compared to ext3. Under high IOPS pressure, it has a more robust IOPS performance than ext4 (some people think that XFS will have great problems in special scenarios, but we have not encountered any other data except when the remaining disk space is less than 10%);

Adjust RAID level to raid 1+0, which improves IOPS performance better than raid 1, raid 5, etc. If they are all SSD devices, they can make RAID 1 with 2 disks, or RAID 5 with multiple disks (and global hot spare can be set to improve array fault tolerance), and even some local rich users directly make RAID 50 with multiple SSD disks;

Adjust RAID write cache strategy to WB or FORCE WB. For details, please refer to: Common PC Server Array Cards, Hard Disk Health Monitoring and PC Server Array Card Management Simple Manual;

Adjust the io scheduler of the kernel, give priority to using deadline. If it is SSD, you can use noop policy. Compared with the default cfq, in some cases, the performance of IOPS is improved by at least several times.

About "how to optimize Slave latency in MySQL" this article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.