MySQL Master-Slave latency reasons and solutions

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

talked aboutMySQLthe principle of master-Slave synchronization delay of the databaseMySQLprinciple of database Master-slave replication,MySQLthe master-slave copy is a single-threaded operation(mysql5.6before version), the main library is for allDDLand theDMLProduceBinlog,Binlogis sequential write, so the efficiency is very high;slaveof theslave_io_runningthreads will fetch logs from the main library, which will be more efficient,slaveof theslave_sql_runningthread will be the primary library'sDDLand theDMLoperations are inslaveimplementation. DMLand theDDLof theIOoperations are random, not sequential, so costs can be high and may beslavegenerated by other queries on theLockcontention, becauseslave_sql_runningis also single-threaded, so aDDLthe card master, need to executeTenminutes, then all after theDDLwill wait for thisDDLexecution will continue, which results in a delay. A friend will ask:"The same one on the main library.DDLalso need to executeTenpoints, whyslavewill it be delayed? ", the answer isMastercan be concurrent,slave_sql_runningthreads are not allowed.

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

   when the main libraryTPSwhen concurrency is high, the resultingDDLQuantity exceedsslaveaSQLThe range that the thread can withstand, then the delay arises, and of course there is the possibility ofslaveof LargeQueryThe statement generates a lock wait.

3.MySQL Database master-Slave synchronization delay Solution

The simplest reductionslavethe scheme of synchronization delay is to optimize the architecture so as to make the main libraryDDLfast execution. There is also the main library is written, the data security is high, such assync_binlog=1,innodb_flush_log_at_trx_commit= 1such as the settings, andslaveit does not require such a high level of data security, it is entirely possible to speakSync_binlogset to0or CloseBinlog,Innodb_flushlogcan also be set to0to improveSQLefficiency of implementation. The other is to use a better hardware device than the main library asslave.

The factors of the 4.MySQL database master-Slave synchronization delay.
1.Network Latency
2. MasterLoad
3. SlaveLoad
It is common practice to use more than oneslaveto share the read request, and then from theseslaveTo take a dedicated server, only as a backup, without any other operations, can be relatively maximum to achieve'Real Time''s requirements.

In addition, we introduce2a parameter that can reduce the delay
–slave-net-timeout=seconds
parameter meaning: whenslaveread from the primary databaseLoghow long to wait to reestablish the connection and get the data after the data fails
Slave_net_timeoutUnit is Seconds The default setting is3600seconds
| 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-retryUnit is Seconds The default setting is -seconds
usually configured above2parameters to reduce master-slave data synchronization delays caused by network problems

This article is from the "Liu Xiao Rabbit" blog, please be sure to keep this source http://lyanhong.blog.51cto.com/12788695/1914288

MySQL Master-Slave latency reasons and solutions