Shulou(Shulou.com)06/01 Report--

The following content mainly brings you the synchronous delay principle and solution of MySQL master-slave database, the knowledge mentioned is slightly different from books, it is summed up by professional and technical personnel in the process of contact with users, and has a certain value of experience sharing, hoping to bring help to the majority of readers.

Summary of MySQL master-slave database synchronization delay: MySQL master-slave synchronization is a very mature architecture, with the following advantages: ① can perform query work on the slave CVM (that is, the read function we often say), reducing the pressure on the master server; ② backs up the master server to avoid affecting the service of the master server during the backup; ③ can switch to the slave server when there is a problem with the master server. I believe you are well aware of these benefits, and this scheme is also used in the deployment of the project. But MySQL...

MySQL master-slave synchronization is a very mature architecture, the advantages are: ① in the slave server can perform query work (that is, we often say read function), reducing the pressure on the master server; ② in the backup from the master server to avoid affecting the master server service during the backup; ③ when there is a problem with the master server, you can switch to the slave server.

I believe you are well aware of these benefits, and this scheme is also used in the deployment of the project. But the master-slave synchronization of MySQL has always had the problem of slave library delay, so why is there such a problem? How to solve this kind of problem?

1. Principle of master-slave synchronization delay in MySQL database.

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

3. MySQL database master-slave synchronization delay solution.

1. Principle of master-slave synchronization delay in MySQL database.

A: when it comes to the principle of master-slave synchronization delay in MySQL database, we should start with the master-slave replication principle of mysql. The master-slave replication of mysql is a single-thread operation, 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 is very efficient to retrieve logs from the master database. The next step is that the Slave_SQL_ running thread of slave implements the DDL and DML operations of the master database in slave. The IO operation of DML and DDL is random, not sequential, and the cost is much higher. Other queries on slave may also generate lock contention. Because Slave_SQL_Running is also single-threaded, a DDL card owner needs to execute for 10 minutes. Then all subsequent DDL will wait for the DDL to finish execution before continuing to execute, which leads to the delay. A friend will ask: "the same DDL on the main library also needs to execute 10 points. Why is the slave delayed?" The answer is that master can be concurrent, but Slave_SQL_ running threads cannot.

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

A: 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.

3. Master-slave synchronization delay solution for MySQL database

A: 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. The other thing is to use a better hardware device than the main library as the slave.

Mysql-5.6.3 already supports multithreaded master-slave replication. The principle is similar to Ding Qi's, Ding Qi uses tables to do multithreading, Oracle uses database (schema) as a unit to do multithreading, and different libraries can use different replication threads.

The local area network-based master/slave mechanism can meet the requirements of 'real-time' backup in general. If the delay is large, identify the following factors first:

1. Network delay

2. Master load

3. Slave load

The general practice is to use multiple slave to apportion read requests, and then take a dedicated server from these slave, which can only be used as a backup, without any other operation, so that the requirement of 'real-time' can be achieved relatively maximum.

Slave_net_timeout units are set to 3600 seconds by default.

Parameter meaning: when slave fails to read log data from the master database, how long does it take to re-establish the connection and get the data?

Master-connect-retry units are set to 60 seconds by default.

Parameter meaning: when the master-slave connection is re-established, how long will the interval be before retrying if the connection establishment fails.

Usually, configuring the above two parameters can reduce the master-slave data synchronization delay caused by network problems.

There are usually two ways to determine the master-slave delay:

1. Seconds_Behind_Master vs 2. Mk-heartbeat, the following details the difference between the two in the implementation of the function.

You can determine whether there is a master-slave delay by monitoring the value of the Seconds_Behind_Master parameter output by the show slave status\ G command.

There are several kinds of value:

NULL-indicates that either io_thread or sql_thread has failed, that is, the thread's Running state is No, not Yes.

0-this value is zero, which is what we are very eager to see, indicating that the master-slave copy is good and it can be considered that lag does not exist.

Positive value-indicates that there is a delay between the master and slave, and the higher the number, the more the slave lags behind the master.

Negative value-rarely seen, only seen by some experienced DBA, in fact, this is a bug value, this parameter does not support negative values, that is, it should not appear.

Seconds_Behind_Master is such a difference by comparing the timestamp of event executed by sql_thread with the timestamp of io_thread copied event (abbreviated as ts). As we all know, relay-log is exactly the same as the bin-log of the master library. While recording the sql statement, it will be recorded with the ts at that time, so the comparative reference value comes from binlog. In fact, it is not necessary for master and slave to synchronize with NTP, that is to say, there is no need to ensure the consistency of the master-slave clock. You will also find that, in fact, it really happens between io_thread and sql_thread, and io_thread is really related to the main library, so the problem arises. When the load of the main library is heavy or the network is blocked, io_thread cannot copy binlog in time (without interruption, it is also copying), and sql_thread can always keep up with the script of io_thread, and the value of Seconds_Behind_Master is 0, which is what we think is no delay. But, actually, it's not, you know. This is why we criticize the use of this parameter to monitor whether the database delay is incorrect, but this value is not always inaccurate, and it is also valuable if the io_thread and master networks are good. For example: mother-son-wife relationship, mother and son relatives, wife and son are also relatives, wife and mother are not necessarily very close. Just kidding: -) previously, we mentioned that there will be a negative value for the parameter Seconds_Behind_Master. We already know that this value is the ts difference between io_thread and the new ts and sql_thread. The former is always greater than the latter, and the only possible thing is that there is an error in the ts of a certain event, which is smaller than before, so when this happens, negative values become possible.

Method 2. A tool in the mk-heartbeat,Maatkit universal toolkit that is considered to accurately determine the method of replication delay.

The implementation of mk-heartbeat is also realized with the help of timestmp comparison, which first needs to ensure that the master and slave servers must be consistent by synchronizing the clock with the same NTP server. It needs to create a heartbeat table on the master database, which has at least two fields: id and ts. Id is server_id,ts, which is the current timestamp now (). This structure will also be copied to the slave database. After the table is built, a row of update commands will be executed on the master database in the mode of background process, and the insert data in the table will be directed to the table regularly. This period defaults to 1 second. At the same time, the slave library will execute a monitoring command in the background. Compared with the period consistent with the main database, the copied ts value is compared with the same ts value on the main database. A difference of 0 means no delay, and a larger difference means more seconds of delay. We all know that replication is asynchronous and ts is not completely consistent, so the tool allows a gap of half a second, within which differences can be ignored as no latency. This tool is through real reproduction, ingenious use of timestamp to check delay, like a!

For the above synchronization delay principle and solution of MySQL master-slave database, if you have more information, you can continue to pay attention to the innovation of our industry. If you need professional solutions, you can contact the pre-sale and after-sale ones on the official website. I hope this article can bring you some knowledge updates.

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.