Reasons and solutions of MySQL master-slave synchronization delay

MySQL Master-Slave Latency Reasons and solutions: talking about MySQL Database master-Slave synchronization delay principle, from MySQL the principle of master-slave replication of the database, MySQL master-slave replication is a single-threaded operation (before the mysql5.6 version), the main library for all DDL and DML production Binlog,binlog is sequential write, so the efficiency is very high.

The slave slave_io_running thread will fetch logs from the main library, and the slave slave_sql_running thread will implement the DDL and DML operations of the main library slave. The IO operations of DML and DDL are random, not sequential, so the cost can be high, and perhaps other queries on slave generate lock contention, because Slave_sql_running is also single-threaded, so a DDL card master, need to execute 10 minutes, Then all subsequent DDL will wait for the DDL to execute before continuing, which results in a delay. A friend would say, "The same DDL on the main library needs to perform 10 points, why is the slave delayed?" "The answer is that master can be concurrent and slave_sql_running threads are not."

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

When the TPS for the main library is high, the number of DDL generated exceeds the range that slave a SQL thread can withstand, and the delay is generated, and of course there is the possibility of a lock wait with slave's large query statement.

3.MySQL Database Master-Slave synchronous delay solution

(1) The simplest solution to reduce the slave synchronization delay is to optimize the architecture and try to get the DDL of the main library to execute quickly. There is the main library is written, the data security is high, such as Sync_binlog=1,innodb_flush_log_at_trx_commit = 1 and other settings, and slave do not need this high data security, fully can speak sync_ Binlog is set to 0 or off Binlog,innodb_flushlog can also be set to zero to increase the efficiency of SQL execution.

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

That is, one from the server when the degree of use as a backup, and do not provide a query, where his load down, execute relay log inside the SQL efficiency is naturally high.

(3) Increase from the server, the purpose is to disperse the read pressure, thereby reducing the server load.

the factors of the 4.MySQL database master-Slave synchronization delay.  

1. Network Latency 2. Master Load 3. Slave load the general practice is to use multiple slave to allocate read requests, and then from these slave to take a dedicated server, only as a backup, no other operation, can be relatively maximum to achieve ' real-time ' requirements

In addition, we introduce 2 parameters that can reduce the delay –slave-net-timeout=seconds parameter meaning: How long to wait to reestablish the connection and get the data slave_net_timeout units seconds when slave fails to read log data from the primary database The default setting is 3,600 seconds slave_net_timeout 3600–master-connect-retry=seconds parameter meaning: When the master-slave connection is re-established, if the connection establishment fails, how long after the interval is retried. Master-connect-retry unit is set to 60 seconds by default the above 2 parameters are typically configured to reduce master-slave data synchronization delays caused by network problems.

This article from the "North Bear" blog, reproduced please contact the author!

Reasons and solutions of MySQL master-slave synchronization delay