MySQL master-slave data synchronization delay analysis

first, MySQL database master-slave synchronization delay

To understand the principle of master-slave synchronization delay in MySQL database, we start with the principle of the master-slave replication of M y SQL:

The master-slave replication of M ySQL is a single-threaded operation, and the main library writes into B inlog for all DDL and DML-generated logs, and is highly efficient because binlog is sequential.

the IO thread thread of S Lave reads the log from the bin log in the main library .
the SQL thread thread of S lave replays the DDL and DML operation events of the main library in slave. The IO operations of DML and DDL are immediate, not sequential, and much more expensive.

Because SQL Thread is also single-threaded, if other queries on slave produce lock contention, or if a DML statement (large transaction, large query) executes for a few minutes, all subsequent DML waits for the DML to execute before it continues, which results in a delay.

second, the MySQL database master-Slave synchronization delay causes

1,Master load

2,Slave load

3. Network Delay

4. Machine configuration (CPU, memory, hard disk)

In summary, when the main library is high in concurrency, the number of DML generated exceeds the speed that slave SQL Thread can handle, or the delay occurs when a large query statement in slave generates a lock wait.

third, MySQL database master-Slave synchronization delay Solution     

1,Salve higher machine configuration

2,Slave adjustment parameters

we know from "Mysql data loss analysis" that in order to ensure high data security, configure settings such as Sync_binlog=1,innodb_flush_log_at_trx_commit= 1. While Slave can be turned off binlog,innodb_flush_log_at_trx_commit can also be set to zero to improve SQL execution efficiency

3. Parallel replication
Implementation and pros and cons of MySQL parallel replication in each version

MySQL 5.7 Parallel replication implementation principle and tuning

MySQL master-slave data synchronization delay analysis