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

The principle of master-slave synchronization delay in 1.MySQL database.

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 (before the mysql5.6 version). The master database generates binlog,binlog sequentially for all DDL and DML, so it is very efficient; the Slave_IO_Running thread of slave will go to the master database to get logs, which is more efficient. The Slave_SQL_ running thread of slave implements the DDL and DML operations of the master 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 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.

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

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.

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

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.

In addition, two more parameters that can reduce delay are introduced.

-slave-net-timeout=seconds

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?

Slave_net_timeout units are set to 3600 seconds by default.

| | slave_net_timeout | 3600 |

-master-connect-retry=seconds

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

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

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

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.