MySQL master-slave database synchronization delay problem resolution

Recently in the MySQL master-slave database synchronization test, found some problems, the master-slave synchronization delay problem is one of them, the following content is found from the Internet some explanations, recorded so that their own learning;

MySQL master-slave synchronization is a very mature architecture, the advantage is: ① in the slave server can perform query work (that is, we often say that the read function), reduce the primary server pressure; ② in the backup from the primary server, to avoid affecting the primary server service during backup, ③ when the primary server is having problems, you can switch to the slave server.

MySQL master-Slave synchronization failure-slave_sql_running:no http://www.linuxidc.com/Linux/2014-02/96945.htm

MySQL master-slave synchronization build http://www.linuxidc.com/Linux/2013-12/93934.htm

MySQL master-slave replication configuration details http://www.linuxidc.com/Linux/2014-02/97136.htm

MySQL Replication (master server) configuration instance http://www.linuxidc.com/Linux/2013-12/94485.htm

Make MySQL database master server http://www.linuxidc.com/Linux/2013-12/93986.htm in Linux system

MySQL installation and master-slave configuration http://www.linuxidc.com/Linux/2013-12/93378.htm

It is believed that these benefits are well understood and are used in the deployment of the project. But MySQL's master-slave synchronization has been a problem from the library delay, then why this problem. How is this problem solved?

1. mysql Database master-Slave synchronization delay principle.

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

3. mysql Database master-Slave synchronization delay solution.

1. mysql Database master-Slave synchronization delay principle.

A: When it comes to MySQL database master-Slave synchronization delay principle, from the MySQL database master-slave replication principle, MySQL master-slave replication is a single-threaded operation, the main library for all DDL and DML production Binlog,binlog is sequential write, so high efficiency, slave slave _io_running thread to the main library to fetch logs, the efficiency is very high, next, the problem comes, slave slave_sql_running thread will be the main library of DDL and DML operations in the slave implementation. The IO operations of DML and DDL are immediately, not sequential, expensive, and may also generate lock contention on other queries on slave, since slave_sql_running is also single-threaded, so a DDL card master has to be executed for 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. How the MySQL database master-slave synchronization delay is generated.

A: When the TPS for the main library is high, the number of DDL generated exceeds the range that slave a SQL thread can withstand, and the delay is generated, and of course there is the possibility of a lock wait with a large query statement from slave.

3. mysql Database master-Slave synchronization delay solution

A: The simplest solution to reduce the slave synchronization delay is to optimize the architecture and try to get the DDL of the main library to execute quickly. There is the main library is written, the data security is high, such as Sync_binlog=1,innodb_flush_log_at_trx_commit = 1 and other settings, and slave do not need this high data security, fully can speak sync_ Binlog is set to 0 or off Binlog,innodb_flushlog can also be set to zero to increase the efficiency of SQL execution. The other 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. Similar to Dinkey, the Dinkey is to do a long thread on the table, Oracle uses a database (schema) for long threads, and 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, Binlog is not synchronized with the hard disk each 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 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 Sync_binlog is set to 1, there is a possibility of inconsistency between the table content and the Binlog content when a crash occurs. If you use the InnoDB table, the MySQL server processes the commit statement, which writes the entire transaction to Binlog and commits the transaction to InnoDB. If a crash occurs between two operations, the transaction is InnoDB rolled back, but still exists in Binlog. You can use the--innodb-safe-binlog option to increase the consistency between InnoDB table content and Binlog. (Note:--innodb-safe-binlog is not required in MySQL 5.1; This option is obsolete because of the introduction of XA transaction support), which provides greater security for Binlog (Sync_binlog =1) per transaction and (default is True) InnoDB logs are synchronized with the hard disk, the effect of this option is that after the crash restarts, after rolling back the transaction, the MySQL server cuts the rollback from the Binlog innodb transaction. This ensures that the Binlog feedback innodb the exact data of the table, etc., and keeps the slave server in sync with the primary server (without taking the rollback statement).

Innodb_flush_log_at_trx_commit (This works well)

Complaining that InnoDB is 100 times times slower than MyISAM? Then you probably forgot to adjust the value. The default value of 1 means that every single transaction commit or out-of-transaction instruction requires the log to be written to (flush) the hard disk, which is time consuming. Especially when using the battery-powered cache (Battery backed up cache). Set to 2 for many applications, especially from the MyISAM table is possible, it means to write to the system cache instead of writing to the hard disk. The log will still flush to the hard drive every second, so you will generally not lose more than 1-2 seconds of updates. Set to 0 will be faster, but the security side is poor, even if MySQL hangs may also lose the transaction data. A value of 2 will only lose data if the entire operating system is hung.

MySQL master-slave database synchronization delay problem Resolution (GO)