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

In this issue, the editor will bring you about the reasons for the master-slave synchronization delay of MySQL. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

MySQL master-slave delay reasons and solutions: when it comes to the principle of MySQL database master-slave synchronization delay, we should start with mysql's database master-slave replication principle. Mysql master-slave replication is a single-threaded operation (before the mysql5.6 version), and the master database generates binlog,binlog for all DDL and DML sequentially, so it is very efficient.

The Slave_IO_Running thread of slave will fetch logs from the main database, which will be more efficient. The Slave_SQL_ running thread of slave implements both DDL and DML operations of the main database in slave. The IO operations of DML and DDL are random, not sequential, so the cost will be very high, and it is also possible that other queries on slave cause lock contention. Because Slave_SQL_Running is also single-threaded, a DDL card owner needs to execute for 10 minutes, so all subsequent DDL will wait for the DDL to finish execution before continuing to execute, which leads to the delay. A friend will say, "the same DDL on the main library needs to be executed by 10 points, so why does slave delay?" the answer is that master can be concurrent, but Slave_SQL_ running threads cannot.

How the master-slave synchronization delay of 2.MySQL database is generated.

When the TPS concurrency of the main library is high, the number of DDL generated is more than a sql thread of slave can bear, then the delay occurs, and of course, lock waiting may occur with the large query statements of slave.

Master-Slave synchronization delay solution for 3.MySQL Database

(1) the simplest way to reduce slave synchronization latency is to optimize the architecture so that the DDL of the main library can be executed as quickly as possible. In addition, the main library is write, which is highly secure for data, such as sync_binlog=1,innodb_flush_log_at_trx_commit = 1, while slave does not need such high data security. You can set sync_binlog to 0 or close binlog,innodb_flushlog to 0 to improve the efficiency of sql execution.

(2) the other thing is to use a better hardware device than the main library as the slave.

That is, a slave server is used as a backup instead of providing a query, where his load comes down, and the efficiency of executing SQL in relay log is naturally high.

(3) increase the slave server, this purpose is to disperse the pressure of reading, so as to reduce the server load.

The factors caused by the master-slave synchronization delay of 4.MySQL database.

1. Network delay 2. Master load 3. The general practice of slave load is to use multiple slave to share read requests, and then take a dedicated server from these slave for backup only, without any other operation, you can achieve the 'real-time' requirements relatively.

In addition, introduce two parameters that can reduce latency-slave-net-timeout=seconds parameter meaning: when slave failed to read log data from the master database, how long to wait to re-establish the connection and obtain data slave_net_timeout unit is set to 3600 seconds by default slave_net_timeout 3600-master-connect-retry=seconds parameter meaning: when re-establishing a master-slave connection, if the connection establishment fails, how long will the interval be before retry. The master-connect-retry unit is set to 60 seconds by default. Generally, configuring the above two parameters can reduce the master-slave data synchronization delay caused by network problems.

These are the reasons for the MySQL master-slave synchronization delay shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.