MySQL master-slave database synchronization latency problem solved _ MySQL

MySQL master-slave database synchronization latency problem solved recently I was conducting a MySQL master-slave database synchronization test and found some problems, among which the master-slave synchronization latency problem is one of them. the following content is some explanations found on the Internet, record it for your own learning;

The master-slave synchronization of MySQL is a mature architecture with the following advantages: ① the query can be performed on the slave server (that is, the read function we often call) to reduce the pressure on the master server; ② back up data from the master server to avoid affecting the services of the master server during the backup process; ③ When the master server encounters problems, you can switch to the slave server.

I believe that you have a good understanding of these benefits, and this solution is also used in the project deployment. However, the master-slave synchronization of MySQL has always had the slave database latency problem, so why. How can this problem be solved?

1. principle of master-slave synchronization latency in MySQL databases.

2. how is the master-slave synchronization latency of the MySQL database generated.

3. MySQL database master-slave synchronization latency solution.

1. principle of master-slave synchronization latency in MySQL databases.

A: Speaking of the master-slave synchronization delay principle of MySQL databases, we must start with the master-slave replication principle of mysql databases. mysql master-slave replication is a single-threaded operation, and the master database generates binlogs for all DDL and DML data, binlog is sequential write, so the efficiency is very high. slave's Slave_IO_Running thread is very efficient in log retrieval from the master database. Next, the problem arises, the Slave_ SQL _Running thread of slave implements DDL and DML operations on the master database in slave. I/O operations of DML and DDL are immediate, not sequential, and the cost is much higher. other queries on slave may generate lock contention. because Slave_ SQL _Running is also single-thread, therefore, it takes 10 minutes for a DDL card to be executed. all subsequent DDL statements will continue to be executed only after the DDL is executed, resulting in latency. A friend may ask, "the same DDL statement in the master database also needs to be executed for 10 points. why is slave Delayed ?", The answer is that the master node can be concurrent, but the Slave_ SQL _Running thread cannot.

2. how is the master-slave synchronization latency of the MySQL database generated.

A: When the TPS concurrency of the master database is high, the number of DDL statements generated exceeds the limit of one SQL thread of slave. Therefore, the latency is generated, of course, there may be a lock wait with the large query statement of slave.

3. MySQL database master-slave synchronization latency solution

A: The simplest solution to reduce slave synchronization latency is to optimize the architecture and try to make the DDL execution of the master database fast. In addition, the master database is written, which provides high data security, such as sync_binlog = 1, innodb_flush_log_at_trx_commit = 1, and slave does not require such high data security, you can set sync_binlog to 0 or disable binlog. innodb_flushlog can also be set to 0 to improve SQL execution efficiency. In addition, hardware devices that are better than the master database are used as slave.

The mysql-5.6.3 already supports multi-threaded master-slave replication. The principle is similar to that of Dingqi. Dingqi uses multiple threads for tables, while Oracle uses multiple threads for databases (schema). different databases 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, the binlog is not synchronized with the hard disk each time it is written. Therefore, if the operating system or machine (not just the MySQL server) crashes, the last statement in binlog may be lost. To prevent this situation, you can use the sync_binlog global variable (1 is the safest value, but also the slowest) to synchronize the binlog with the hard disk after each N binlog writes. Even if sync_binlog is set to 1, the table content and binlog content may be inconsistent when a crash occurs. If the InnoDB table is used, the MySQL server processes the COMMIT statement, which writes the entire transaction to the binlog and submits the transaction to InnoDB. If a crash occurs between two operations, the transaction is rolled back by InnoDB at the time of restart, but it still exists in binlog. You can use the -- innodb-safe-binlog option to increase the consistency between InnoDB table content and binlog. (Note: In MySQL 5.1, -- innodb-safe-binlog is not required. because XA transaction support is introduced, this option is voided.) This option provides greater security, synchronize the binlog (sync_binlog = 1) and (true by default) InnoDB logs of each transaction with the hard disk. this option is effective after the crash and restart, after the transaction is rolled back, the MySQL server splits the InnoDB transaction for rollback from the binlog. This ensures that the binlog reports the exact data of the InnoDB table and keeps the slave server synchronized with the master server (the Rollback statement is not received ).

Innodb_flush_log_at_trx_commit (this is useful)

Why is Innodb 100 times slower than MyISAM? You probably forgot to adjust this value. The default value 1 indicates that logs need to be written to the hard disk (flush) for each transaction commit or non-transactional command. this is time-consuming. Especially when Battery backed up cache is used. Set to 2 is applicable to many applications, especially the conversion from the MyISAM table. it means writing data to the system cache instead of writing data to the hard disk. Logs are flushed to hard disks per second, so you will not lose updates that exceed 1-2 seconds. Setting 0 is faster, but the security is poor. even if MySQL fails, the transaction data may be lost. Value 2 can only lose data when the entire operating system is down.