MYSQL master slave asynchronous delay principle analysis and solution
- 2020-06-01 11:09:25
- OfStack
1. Principle of MySQL database master slave synchronization delay.
To talk about the principle of delay, we should start with the database master replication principle of mysql, which is a single-threaded operation.
The main library generates binlog for all DDL and DML, binlog is written sequentially, so it is very efficient. The Slave_IO_Running thread of slave goes to the main library to fetch the log, which is quite efficient. Next step, the problem comes out, Slave_SQL_Running thread of slave implements DDL and DML operations of the main library in slave. The IO operation of DML and DDL is random, not sequential, which is much more expensive. It may also generate lock contention for other queries on slave. Since Slave_SQL_Running is also single-threaded, one DDL card is owned and needs to be executed for 10 minutes. A friend would ask, "why is slave delayed when the same DDL on the main library also needs to execute 10 points?" , the answer is that master can be concurrent, while Slave_SQL_Running threads cannot.
2. How the MySQL master slave synchronization delay is generated.
When the TPS concurrency of the main library is high, the number of DDL generated exceeds the range of slave1 sql threads, then the delay occurs, and of course the lock wait may occur with slave's large query statements.
3. MySQL master slave synchronization delay solution.
Dinky's transefer is a good solution, but the company is limited by the ability to modify the code of mysql and the ability to control mysql, so it is still not suitable.
The simplest solution to reduce the synchronization delay of slave is to optimize the architecture so that DDL of the main library can execute as quickly as possible. For example, sync_binlog=1, innodb_flush_log_at_trx_commit =1, while 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 the execution efficiency of sql. Another is to use slave as a hardware device that is better than the main library.
mysql-5.6.3 already supports multi-threaded master-slave replication. The principle is similar to that of dinky. Dinky USES tables for multithreading, and oracle USES databases (schema) for multithreading. Different libraries can use different replication threads.
sync_binlog=1 o
This makes MySQL synchronize the binary log's contents to disk each time it commits a transaction
By default, binlog is not synchronized with the hard disk every time it is written. So if the operating system or machine (not just the MySQL server) crashes, it is possible that the last statement in 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 your hard drive after every N binlog write. Even if sync_binlog is set to 1, it is possible that when a crash occurs, there will be a nonuniformity between the contents of the table and the contents of binlog. If the InnoDB table is used and the MySQL server processes the COMMIT statement, it writes the entire transaction to binlog and commits the transaction to InnoDB. If a crash occurs between operations, the transaction is rolled back by InnoDB when restarted, but still exists in binlog. You can use the -- innodb-safe-binlog option to increase the 1 specificity between the contents of the InnoDB table and binlog. (note: not required in MySQL 5.1 -- innodb-safe-binlog; This option is deactivated due to the introduction of XA transaction support), which provides a greater degree of security by synchronizing the binlog(sync_binlog =1) and (true by default)InnoDB logs for each transaction with the hard disk. The effect of this option is that when restarted after a crash, the MySQL server cuts back the InnoDB transactions from binlog after the transaction is rolled back. This ensures that binlog feeds back the exact data of the InnoDB table, etc., and keeps the slave server in sync with the master server (no statements are received for rollback).
innodb_flush_log_at_trx_commit (this works)
Complaining that Innodb is 100 times slower than MyISAM? So you probably forgot to adjust this value. The default value of 1 means that the log needs to be written to the hard drive (flush) for every transaction commit or out-of-transaction instruction, which can be very time consuming. This is especially true when using the pool power cache (Battery backed up cache). Setting it to 2 is fine for many USES, especially those transferred from the MyISAM table, which means writing to the system cache rather than to the hard disk. The log will still log flush to the hard disk every second, so you probably won't lose more than 1-2 seconds of updates. Setting it to 0 is 1 point faster, but security is poor, and even if MySQL fails, transaction data may be lost. The value 2 is only lost when the entire operating system is down.
To talk about the principle of delay, we should start with the database master replication principle of mysql, which is a single-threaded operation.
The main library generates binlog for all DDL and DML, binlog is written sequentially, so it is very efficient. The Slave_IO_Running thread of slave goes to the main library to fetch the log, which is quite efficient. Next step, the problem comes out, Slave_SQL_Running thread of slave implements DDL and DML operations of the main library in slave. The IO operation of DML and DDL is random, not sequential, which is much more expensive. It may also generate lock contention for other queries on slave. Since Slave_SQL_Running is also single-threaded, one DDL card is owned and needs to be executed for 10 minutes. A friend would ask, "why is slave delayed when the same DDL on the main library also needs to execute 10 points?" , the answer is that master can be concurrent, while Slave_SQL_Running threads cannot.
2. How the MySQL master slave synchronization delay is generated.
When the TPS concurrency of the main library is high, the number of DDL generated exceeds the range of slave1 sql threads, then the delay occurs, and of course the lock wait may occur with slave's large query statements.
3. MySQL master slave synchronization delay solution.
Dinky's transefer is a good solution, but the company is limited by the ability to modify the code of mysql and the ability to control mysql, so it is still not suitable.
The simplest solution to reduce the synchronization delay of slave is to optimize the architecture so that DDL of the main library can execute as quickly as possible. For example, sync_binlog=1, innodb_flush_log_at_trx_commit =1, while 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 the execution efficiency of sql. Another is to use slave as a hardware device that is better than the main library.
mysql-5.6.3 already supports multi-threaded master-slave replication. The principle is similar to that of dinky. Dinky USES tables for multithreading, and oracle USES databases (schema) for multithreading. Different libraries can use different replication threads.
sync_binlog=1 o
This makes MySQL synchronize the binary log's contents to disk each time it commits a transaction
By default, binlog is not synchronized with the hard disk every time it is written. So if the operating system or machine (not just the MySQL server) crashes, it is possible that the last statement in 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 your hard drive after every N binlog write. Even if sync_binlog is set to 1, it is possible that when a crash occurs, there will be a nonuniformity between the contents of the table and the contents of binlog. If the InnoDB table is used and the MySQL server processes the COMMIT statement, it writes the entire transaction to binlog and commits the transaction to InnoDB. If a crash occurs between operations, the transaction is rolled back by InnoDB when restarted, but still exists in binlog. You can use the -- innodb-safe-binlog option to increase the 1 specificity between the contents of the InnoDB table and binlog. (note: not required in MySQL 5.1 -- innodb-safe-binlog; This option is deactivated due to the introduction of XA transaction support), which provides a greater degree of security by synchronizing the binlog(sync_binlog =1) and (true by default)InnoDB logs for each transaction with the hard disk. The effect of this option is that when restarted after a crash, the MySQL server cuts back the InnoDB transactions from binlog after the transaction is rolled back. This ensures that binlog feeds back the exact data of the InnoDB table, etc., and keeps the slave server in sync with the master server (no statements are received for rollback).
innodb_flush_log_at_trx_commit (this works)
Complaining that Innodb is 100 times slower than MyISAM? So you probably forgot to adjust this value. The default value of 1 means that the log needs to be written to the hard drive (flush) for every transaction commit or out-of-transaction instruction, which can be very time consuming. This is especially true when using the pool power cache (Battery backed up cache). Setting it to 2 is fine for many USES, especially those transferred from the MyISAM table, which means writing to the system cache rather than to the hard disk. The log will still log flush to the hard disk every second, so you probably won't lose more than 1-2 seconds of updates. Setting it to 0 is 1 point faster, but security is poor, and even if MySQL fails, transaction data may be lost. The value 2 is only lost when the entire operating system is down.