Principle of synchronization latency of a MYSQL master

Welcome to the Linux community forum and interact with 2 million technical staff. 1. MySQL database master-slave synchronization delay principle. To talk about the delay principle, we must start with the mysql database master-slave replication principle. mysql master-slave replication is a single-threaded operation. The master database generates binlog for all DDL and DML data, and the binlog is sequential write, so the efficiency is very high, slave Sla

Welcome to the Linux community forum, and interact with 2 million technical staff> go to 1. MySQL database master-slave synchronization delay principle. To talk about the delay principle, we must start with the mysql database master-slave replication principle. mysql master-slave replication is a single-threaded operation. The master database generates binlog for all DDL and DML data, and the binlog is sequential write, so the efficiency is very high, slave Sla

Welcome to the Linux community forum and interact with 2 million technicians>

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

To talk about the delay principle, we must start with the mysql database master-slave replication principle. mysql master-slave replication is a single-thread operation,

The main database generates binlogs for all DDL and DML data. The binlog is sequential write, so the efficiency is very high. The Slave_IO_Running thread of slave takes logs from the main database, which is very efficient. 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. Some 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 can be concurrent, but the Slave_ SQL _Running thread cannot.

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

When the TPS concurrency of the master database is high, the number of DDL statements generated exceeds the range that a SQL thread of slave can afford. 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.

Ding Qi's transefer is a good solution, but the general company is limited by the restrictions on the mysql code modification capability and the ability to control mysql, It is not suitable.

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 o

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.