MySQL master-slave replication latency issues related knowledge and solutions

First, how to monitor the master-slave delay occurred?

From the library machine, perform show slave status, view the Seconds_behind_master value, represent the master-slave synchronization from the library behind the main library time, in seconds, if the same slave synchronization without delay, this value is 0.

One of the important reasons for MySQL master-slave delay is that MySQL is executed serially in a single thread.

When the master-slave replicates the data, the MySQL on the slave server is a thread synchronizing the data.

The serial way, it means, executes one before continuing to execute the next. If one is stuck, wait for time before you continue to the next. Serial and parallel are the opposite.

Second, the synchronization delay occurs scene

When the TPS for the main library is high, the number of DDL (SQL statements that modify the class) exceeds the capacity that the slave machine SQL thread can afford, and the delay is generated.

When the main library writes Binlog logs to the file, it is written sequentially to the disk, and the sequential write speed is fast, avoiding the disk random addressing.

From the library's synchronization thread (slave_io_running), when the Binlog is executed on the Slave, it is actually random and the speed must be slower.

The synchronization thread from the library (slave_io_running) should only be the thread in action, the entire MySQL instance on one such thread, then, if MySQL has n libraries of data that need to be synchronized, all to this thread to handle. Not enough hands. (mysql-5.6.3)

Third, the solution of ideas

How can I avoid or resolve master-slave delays? A solution that can be used as follows:

• optimize MySQL parameters from the library. For example, increase innodb_buffer_pool_size to allow more operations to be done in MySQL memory, reducing disk operations.

• use a high-performance host from the library. including CPU strong, memory increased. Avoid using virtual cloud hosts, using physical hosts, which improves I/O aspect.

• use SSD disks from the library. the mechanical hard disk is to read the data and write the data by rotating the head to the specified position. We call it the I/O. Disk I/O has a speed bottleneck. A solid state drive is an electronic device that does not require mechanical rotation, reads and writes data from any location on a solid-state drive at the same speed.

• the compromise of the business code. Some operations that require high real-time requirements, use the main library for read operations. For example, I wrote the data to the main library, need to show the data immediately, not to read the data from the library, because the library may not have synchronized past. Read data directly from the main library, guaranteeing the latest data presentation.

• the thread from the library changes to multiple synchronization threads to synchronize data . mysql-5.6.3 in order to solve this problem, the server, each library to open a thread synchronization.
• Network optimization. network congestion can also lead to synchronization delays. Synchronization delays occur across the database in the computer room. To ensure that the master and slave in the same room inside.

Attached: MySQL master-slave copy of three format data

The first is the statement format. That is, record the original executed SQL statement.

This is the earliest way, and later discovery is not very perfect, there will be some problems in the process of copying.

For example: Because some MySQL functions are used in SQL statements, and this MySQL function is a specific version only, the other version is not this function, put on the slave side of the run, if the slave MySQL version is not the same, there may be a problem with the implementation.

Using a specific function, if the last_insert_id () function is used in SQL, the result of last_insert_id () is different when the same SQL statement is copied to the slave side of execution.

The above two cases lead to: During the copy process, the results of the slave end are not exactly the same as the master side.

The row format is not. Then invented the row format.

The second type , based on the row format. The values before and after each row are recorded. Binlog is stored in the modified line before and after the modification of the value, directly to get the results. Redo.

There is a downside to the row-based format: It involves DDL operations, such as ALTER TABLE, plus a field, which means that the entire table's rows are modified. Then the data in the row of the whole table is recorded in Binlog, resulting in a large amount of data in the Binlog.

As a result, a comprehensive version based on the statement format and row format was invented, called mixed

The third type : Mixed

When a DDL table change operation is encountered, the statement format is used, and the delete or update format is encountered, using the row format.

Summary of the development process of three formats:

has been using the statement format, the row format is supported in version 5.1.5. Later, the occurrence of the stored procedure brings new problems. Some functions are called in the stored procedure and will run differently on the slave side. So the 5.1.8 version began to support the mixed format. The goal of all of the above strategies is to keep master in line with slave's data. From this point of view.

MySQL master-slave replication latency issues related knowledge and solutions