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

This article mainly introduces the reasons for mysql master-slave delay, the article is very detailed, has a certain reference value, interested friends must read it!

1. The machine performance of the slave library is worse than that of the master library.

For example, put 20 master libraries in four machines and slave libraries in one machine. At this time, the update operation will trigger a large number of reading operations, resulting in multiple libraries on the library machine competing for resources, resulting in master-slave delay.

2. The pressure from the storehouse is great.

According to the normal strategy, the reading and writing are separated, and the writing ability is provided by the master library and the reading and writing ability from the slave library. Placing a large number of queries from the library results in the consumption of a large number of CPU resources from the library, which in turn affects the synchronization speed and leads to master-slave delay.

3. The execution of big affairs.

Once a large transaction is executed, the main library must wait until the transaction is completed before it can write to the binlog. For example, the main library performs a very large insert..select insert operation, resulting in nearly 100 GB binlog files transferred to read-only nodes, causing read-only nodes to apply binlog delays.

4. Main library DDL (alter.drop.create)

The read-only node is synchronously serial with the main library DDL. If DDL runs in the main library for a long time, the slave library will consume the same amount of time. For example, it takes 10 minutes to add a 500W table to the main library, which also takes 10 minutes.

5. Lock conflict. May cause node SQL threads to execute slowly.

For example, there are some SELect...forupdateSQL on board.

Example

A) optimize database performance to reduce load b) improve database configuration. In a production environment, it is recommended that the read-only instance configuration should be at least the same as the primary instance configuration to avoid replication delays caused by large configuration differences. C) add slave instances to horizontally expand business read requests and reduce the load of read-only instances d) optimize business slow queries. These are all the contents of the article "what are the reasons for mysql master-slave delays". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.