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

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

2. How is the master-slave synchronization delay in MySQL database 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 writes sequentially to all DDL (database definition language) and DML (database operation language), so it is very efficient. The Slave_IO_Running thread of slave fetches logs from the master database. The next step is the problem. The Slave_SQL_ running thread of slave implements the DDL and DML operations of the main library in slave. The IO operation of DML and DDL is random, not sequential, and the cost is much higher, and 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. MySQL database master-slave synchronization delay solution.

Problem 1: there are too many slaves in the master library, resulting in replication delay

The number of slave data should be 3-5. Too many slave nodes to be replicated will cause replication delay.

Problem 2: the hardware of the slave library is worse than that of the master library, resulting in replication delay

Viewing the system configuration of Master and Slave may cause replication delays due to improper machine configuration, including disk Imax O, CPU, memory and other factors. It usually occurs in the scenario of high concurrency and large amount of data writing.

Problem 3: too many slow SQL statements

If the execution time of a SQL statement is 20 seconds, it will take at least 20 seconds from the completion of the execution to the time the data can be found from the database, which is a delay of 20 seconds.

Generally speaking, the optimization of SQL statements should be continuously monitored and optimized as a routine work. If the writing time of a single SQL is long, it can be modified and written multiple times. Find out the query statements or large transactions that have been executed for a long time by viewing the slow query log or show full processlist command

Question 4: the design problem of master-slave replication

For example, master-slave replication single thread, if the write concurrency of the master library is too large to transfer to the slave library, it will cause delay. Later versions of Mysql can support multithreaded replication, while portals develop their own multithreaded synchronization capabilities.

Problem 5: network delay between master and slave libraries

Network devices such as network cards, network cables, switches and other network devices of the master-slave library may become the bottleneck of replication, resulting in replication delay. In addition, master-slave replication across public networks can easily lead to master-slave replication delays.

Problem 6: high read and write pressure on the main library, resulting in replication delay

Buffer and cache layer should be added to the front end of the architecture

Solutions for portals:

Youku's solution: database slicing technology, while abandoning the problem of replication delay due to the increasing amount of data. Slice according to user_id, so there must be a global table to manage the relationship between users and shard. Share_id can be obtained according to user_id, and then the specified data can be queried according to share_id.

Taobao's solution: modify the source code, and the corresponding mechanism is the Transfer mechanism. Here, multithreading is used to redo Binlog logs, so as to improve the QPS of slave.

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.