MySQL master-slave synchronization-How long is the master-slave latency?

This time, we will separately investigate the time of Master/Slave latency. The master-slave latency mentioned here is not ldquo; slave database update performance cannot keep up with the master database rdquo; it is ldquo; a command slave

This time, we will separately investigate the time of Master/Slave latency. The master-slave latency mentioned here is not ldquo; slave database update performance cannot keep up with the master database rdquo; it is ldquo; a command slave

This time, we will separately investigate the time of Master/Slave latency. The master-slave latency mentioned here does not refer to the "Slave database update performance cannot keep up with the master database", but to the "Delay Time for a command to complete updates from the master database to the slave Database.

Basic Process:

For each connected slave database, the master database has a client thread corresponding to it.

First look at the basic data stream of the master and slave

1. Client SQL update command

2. master database execution

3. binlog is written to the master database.

4. The client thread of the master database reads the binlog and sends it to the io thread of the slave database.

5. relay-log)

6. read relay-log from the Database SQL thread

7. Execute updates.

Two write disks are involved, the binlog of the master database and the relaylog (3 and 5) of the slave database ). However, you don't have to worry about the latency caused by constantly scanning files, because the threads that read files are in the same process and will be broadcast each time they are written, so although it looks asynchronous, the latency is not large.

We mainly examine the latency between Step 2 and 7.

Experiment method-Level 1 Master/Slave

In actual application, the master and slave databases should be separated. This situation is also discussed here (in the same data center, different machines)

As you can imagine, the latency is very small, so the output time on different machines also needs to consider the time synchronization between machines. The design process is as follows:

1. Set MySQL S on machine A to the slave database of MySQL M on machine B.

2. There is A simple client C on A that initiates an insert operation to M, which will be synchronized to S.

3. output the current system microsecond time t1 when mysql_real_query is returned in C.

4. output the current system microsecond time t2 at the engine callback interface write_row entry on S

Then the t2-t1 value is the result we need.

Experiment Method-Level 2 Master/Slave

The project worried that multiple slave databases would connect to one master database, which would affect the performance of the master database. Therefore, the latency of the second-level Cascade master-slave databases in the experiment was also required.

In this structure, a write disk Forwarding is added to the first level slave database (write local binlog after SQL update ),

Lab results

Level 1 Master/Slave 50 ~ 100 us

Level 2 Master/Slave 1.1 ~ 1.2 MS