Causes and solutions for MySQL master-slave synchronization delay

For historical reasons, MySQL replication is based on the logical binary log, not the redo log. I have been asked many times when MySQL will support physical-based replication. In fact, it depends on the ideas of the MySQL bosses. The last time I was brainstorming with Professor Lai, I suddenly asked: Will MySQL have a redo replication based on Paxos?

The real benefit of physical replication does not lie in correctness, because log replication based on ROW format can fully guarantee the correctness of replication. Because the physical log is written continuously during the transaction execution, and the binary log is written only when the transaction is committed. The advantages of physical logging are therefore as follows:

• Under the replication architecture, large transaction logs are committed quickly;
• Under the replication architecture, the master-slave data delay is small;

Assume that a large transaction is executed for one hour. At the final commit, only the redo log of the last committed part needs to be written (redo log can be regarded as a physical log). Although the total amount of redo log written for this large transaction may be 1G, at the time of submission, the data master-slave replication only needs to transfer the last part of the log to the remote slave, because the previous redo log has been continuously synchronized to the slave within 1 hour of execution.

For binary logs, since their writing time occurs when the transaction is committed, assuming that a 1G binary log is generated, the transaction commit time must include the writing time of this 1G log. There is a saying in Oracle that the transaction commit speed is flat, regardless of the size of the transaction. This is not true in the MySQL database. That is, the commit speed of MySQL depends on the size of the binary log generated by the transaction, and the transaction commit speed is not flat.

Even worse, MySQL master-slave replication is delayed under large transactions. Also, assuming that a large transaction is executed on the master server for 1 hour, it needs to be transmitted to the slave server at the last commit time. The master-slave delay is at least 1 hour. If the slave server takes another 1 hour to execute, the worst-case master-slave replication delay may be 2 hours. There is no such limitation for physical replication. The reason is as mentioned above. During the transaction submission process, the log is already being transmitted and played back.

Although physical replication is good, it also has its own shortcomings. From my own actual experience:

• In physical replication, a bad block on the host will cause both the master and slave servers to fail to start. I believe that many students have encountered this problem.
• In addition, doing ETL is difficult, for example, how to synchronize physical logs to the Hadoop big data platform?

In a nutshell, for the MySQL database, no large transactions are allowed to be executed at any time. If you want to execute it, split the large transaction into small sub-transactions for execution. This is the most basic mantra, but it is very different from Oracle. In short, there is no good or bad in Qi Sect and Sword Sect. Only by learning to understand the differences between them and integrating them can one reach the ultimate state like Feng Qingyang.

MySQL uses the master-slave synchronization method to separate reads and writes, which reduces the pressure on the master server and is now very common in the industry. Master-slave synchronization can basically achieve real-time synchronization. I borrowed the master-slave synchronization schematic from another website.

After the configuration is completed and the master-slave synchronization is complete, the master server will write the update statements into the binlog, and the slave server's IO thread (note that there is only one IO thread before 5.6.3, and there are multiple threads to read after 5.6.3, so the speed is naturally faster) will go back to read the master server's binlog and write it into the slave server's Relay log. Then the slave server's SQL thread will execute the SQL statements in the relay log one by one to recover the data.

Relay means to pass on, and relay race means relay race.

1. Causes of delay in master-slave synchronization

We know that a server opens N links for clients to connect to, so there will be large concurrent update operations, but there is only one thread reading binlog from the server. When a certain SQL is executed on the slave server for a longer time or a certain SQL needs to lock the table, a large amount of SQL on the master server will be accumulated and not synchronized to the slave server. This leads to master-slave inconsistency, that is, master-slave delay.

2. Solution to master-slave synchronization delay

In fact, there is no one-size-fits-all solution to the master-slave synchronization delay, because all SQL statements must be executed once in the slave server. However, if the master server is continuously updated and written, once a delay occurs, the possibility of the delay increasing will increase. Of course we can take some mitigating measures.

• a. We know that because the master server is responsible for update operations, it has higher security requirements than the slave server, so some settings can be modified, such as sync_binlog=1, innodb_flush_log_at_trx_commit = 1 and other settings. However, the slave does not need such high data security. You can set sync_binlog to 0 or turn off binlog, innodb_flushlog, innodb_flush_log_at_trx_commit can also be set to 0 to improve the execution efficiency of SQL, which can greatly improve efficiency. Another option is to use better hardware than the master database as the slave.
• b. That is, use a slave server as a backup instead of providing queries. The load there is reduced, and the efficiency of executing the SQL in the relay log is naturally high.
• c. Add slave servers. The purpose of this is to distribute the reading pressure and thus reduce the server load.

3. Methods for determining master-slave delay

MySQL provides a slave server status command, which can be viewed through show slave status. For example, you can check the value of the Seconds_Behind_Master parameter to determine whether there is a master-slave delay.

The values ​​are as follows:

NULL - Indicates that either io_thread or sql_thread has failed, that is, the Running status of the thread is No, not Yes.
0 - This value is zero, which is what we are most eager to see, indicating that the master-slave replication status is normal

I haven't tried other methods, so I won't comment on them for now.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links