Solving the problem of master-slave database synchronization delay in MySQL

Recently I was doing MySQL master-slave database synchronization testing and found some problems, among which the master-slave synchronization delay problem is one of them. The following content is some explanations found on the Internet, recorded for your own learning;

MySQL's master-slave synchronization is a very mature architecture. The advantages are: ① Query work can be performed on the slave server (that is, what we often call the read function), which reduces the pressure on the master server; ② Backup is performed on the slave master server to avoid the backup period. Affects the main server service; ③ When there is a problem with the main server, you can switch to the slave server.

I believe that everyone is already very familiar with these benefits, and this solution is also used in project deployment. However, MySQL's master-slave synchronization has always had the problem of slave database delay, so why does this problem occur? How to solve this problem?

1. MySQL database master-slave synchronization delay principle.

2. How does the MySQL database master-slave synchronization delay occur?

3. MySQL database master-slave synchronization delay solution.

1. MySQL database master-slave synchronization delay principle.

Answer: When talking about the principle of master-slave synchronization delay in MySQL database, we have to start with the principle of mysql database master-slave replication. Mysql’s master-slave replication is a single-threaded operation. The main database generates all DDL and DML. Binlog, binlog is written sequentially, so it is very efficient. The slave's Slave_IO_Running thread goes to the main library to get the log, which is very efficient. The next step is the problem. The slave's Slave_SQL_Running thread implements the DDL and DML operations of the main library on the slave. The IO operations of DML and DDL are random, not sequential, and the cost is much higher. Other queries on the slave may also cause lock contention. Since Slave_SQL_Running is also single-threaded, a DDL card master will take 10 minutes to execute. Then all subsequent DDL will wait for this DDL to be executed before continuing, which leads to delays. Some friends will ask: "The same DDL on the main library also needs to be executed for 10 minutes. Why is the slave delayed?" The answer is that the master can run concurrently, but the Slave_SQL_Running thread cannot.

2. How does the MySQL database master-slave synchronization delay occur?

Answer: When the TPS concurrency of the main library is high, the number of DDL generated exceeds the range that one SQL thread of the slave can bear, and then delay occurs. Of course, there may also be large query statements with the slave. A lock wait occurs.

3. MySQL database master-slave synchronization delay solution

Answer: The simplest solution to reduce slave synchronization delay is to optimize the architecture and try to make the DDL of the main database execute quickly. There is also the fact that the main library is written, which has high data security, such as sync_binlog=1, innodb_flush_log_at_trx_commit = 1 and other settings. However, the slave does not need such high data security. You can set sync_binlog to 0 or turn off binlog. innodb_flushlog can also be set to 0 to improve SQL execution efficiency. The other is to use a better hardware device than the main library as a slave.

mysql-5.6.3 already supports multi-threaded master-slave replication. The principle is similar to Ding Qi's. Ding Qi's uses tables as multi-threads, while Oracle uses database (schema) as the unit to do multi-threads. Different libraries can use different replication threads.

sync_binlog=1

This makes MySQL synchronize the binary log's contents to disk each time it commits a transaction

By default, not every time it is written Binlog is synchronized with the hard disk. Therefore, if the operating system or machine (not just the MySQL server) crashes, it is possible that the last statement in the binlog is lost. To prevent this, you can use the sync_binlog global variable (1 is the safest value, but also the slowest) to synchronize binlog with the hard disk after every N binlog writes. Even if sync_binlog is set to 1, when a crash occurs, there may be inconsistencies between the table content and the binlog content. If using an InnoDB table, the MySQL server handles the COMMIT statement, it writes the entire transaction to the binlog and commits the transaction to InnoDB. If a crash occurs between two operations, the transaction is rolled back by InnoDB when restarting, but it still exists in the binlog. You can use the --innodb-safe-binlog option to increase consistency between the InnoDB table contents and the binlog. (Note: --innodb-safe-binlog is not required in MySQL 5.1; this option is obsolete due to the introduction of ) and (true by default) the InnoDB log is synchronized with the hard disk. The effect of this option is that when restarting after a crash, after the transaction is rolled back, the MySQL server cuts the rolled back InnoDB transaction from the binlog. This ensures that the binlog feeds back the exact data of the InnoDB table, etc., and keeps the slave server in sync with the master server (without receiving rollback statements).

innodb_flush_log_at_trx_commit (This works very well)

Complaining that Innodb is 100 times slower than MyISAM? Then you probably forgot to adjust this value. The default value of 1 means that every transaction commit or instruction outside the transaction needs to write the log to the hard disk (flush), which is very time-consuming. Especially when using battery-backed cache (Battery backed up cache). Setting it to 2 is fine for many applications, especially those transferred from MyISAM tables. It means not writing to the hard disk but writing to the system cache. The logs are still flushed to disk every second, so you generally won't lose more than 1-2 seconds of updates. Setting it to 0 will be faster, but the security is poor. Even if MySQL hangs up, transaction data may be lost. The value 2 will only cause data loss when the entire operating system hangs.

The above is the detailed content of Solving the problem of master-slave database synchronization delay in MySQL. For more information, please follow other related articles on the PHP Chinese website!