The principle analysis and solution _mysql of Mysql master never synchronous delay

1. mysql Database master-Slave synchronization delay principle.
To say the principle of delay, from the MySQL database master-slave copy principle, MySQL master-slave replication is a single thread of operation,
The main library for all DDL and DML generated binlog,binlog is sequential write, so efficient, slave slave_io_running thread to the main library log, the efficiency is very high, next, the problem, slave slave_sql_ The running thread implements the DDL and DML operations of the main library in slave. DML and DDL IO operations are immediate, not sequential, costly, and may also generate lock contention for other queries on slave, because slave_sql_running is also single-threaded, so a DDL card owner needs to perform 10 minutes, Then all subsequent DDL will wait for the DDL to execute before continuing, which results in a delay. A friend will ask: "The same DDL on the main library also needs to perform 10 points, why slave delay?" The answer is that master can be concurrent, and slave_sql_running threads are not.

2. mysql Database master-Slave synchronization delay is how to produce.
When the TPS concurrency of the main library is higher, the number of DDL generated exceeds the range slave an SQL thread can withstand, then latency arises and, of course, it is possible to have a lock wait with slave's large query statement.

3. mysql Database master-Slave synchronization delay solution.
Ding's Transefer is a good solution, but the general company is limited by the ability to modify MySQL's code and the ability to control MySQL, still not very appropriate.
The simplest solution to reduce the slave synchronization delay is to optimize the architecture and make the main repository's DDL execute quickly. There is the main library is written, the data security is higher, such as Sync_binlog=1,innodb_flush_log_at_trx_commit = 1, such as settings, and slave do not need such a high data security, completely can speak sync_ Binlog set to 0 or off Binlog,innodb_flushlog can also be set to 0来 improve SQL execution efficiency. Another is to use a better hardware device than the main library as a slave.
mysql-5.6.3 has supported multi-threaded master-slave replication. The principle is similar to that of Ding, Ding is a table-long thread, and Oracle uses a database (schema) for multiple threads, and different libraries can use different replication threads.

Sync_binlog=1 o
This is makes MySQL synchronize the binary log ' contents to disk ' It commits a transaction
By default, Binlog is not synchronized with the hard disk every time you write. So 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) so that Binlog synchronizes with the hard disk after every n binlog write. Even if the sync_binlog is set to 1, there may be inconsistencies between the table content and the Binlog content when there is a crash. If the InnoDB table is used, the MySQL server processes the commit statement, writes the entire transaction to the Binlog and commits the transaction to the InnoDB. If a crash occurs between two operations, the transaction is InnoDB rolled back, but still exists in the Binlog. You can use the--innodb-safe-binlog option to increase the consistency between InnoDB table content and Binlog. (Note: There is no need for--innodb-safe-binlog in MySQL 5.1; Due to the introduction of XA transaction support, this option is invalid), this option can provide a greater degree of security to make each transaction Binlog (Sync_binlog =1) and (by default, True) The InnoDB log synchronizes with the hard disk, which is the effect of a crash restart when the MySQL server cuts back the InnoDB transaction from Binlog after the transaction is rolled back. This ensures that the Binlog feedback innodb the exact data of the table, and keeps the server from being synchronized with the primary server (not the retracted statement).

Innodb_flush_log_at_trx_commit (This works well)
Complaining that InnoDB is 100 times times slower than MyISAM? So you probably forgot to adjust the value. The default value of 1 means that every instruction outside the transaction commit or transaction needs to write the log to the (flush) hard disk, which is time consuming. Especially when using battery-powered caching (Battery backed up cache). Set to 2 for many applications, especially from the MyISAM table, it means that the system cache is written without writing to the hard disk. The log still flush to the hard drive every second, so you won't normally lose more than 1-2 seconds of updates. Set to 0 will be faster, but the security aspect is poor, even if MySQL hangs may lose the transaction data. A value of 2 only loses data when the entire operating system hangs.