The replication latency caused by the mutual blocking of MySQL 5.7 concurrent replication and mysqldump.

The replication latency caused by the mutual blocking of MySQL 5.7 concurrent replication and mysqldump.

The MySQL BINLOG and show processlist commands were originally two transactions that could not be reached by the client. However, in the recent troubleshooting, it was found that there was a serious replication delay between the master database and slave database, however, the slave_behind_master value is 0 in the database, and the replication SQL thread and backup thread are blocked, but no deadlock is reported.

Execute show processlist on the slave database to find the SQL thread waiting lock for replication, the WHERE condition for waiting for SQL is similar to WHERE C1 = 'abc' AND C2> '2017-03-01 'AND C2 <'2017-03-26, the first thought was how STATEMENT-based replication was not scientific. We used ROW-based replication in our production environment. Did R & D Research and Development privately modify the session-level replication format?

Use MySQL Binlog to export logs:

Rbr_only = yes indicates ROW-based replication. "set transaction isolation level read committed" is also one of the typical features of ROW-based replication, last_committed and sequence_number are used for concurrent replication in MySQL 5.7. row_query is followed by the original SQL statement executed on the master database, that is, the SQL statement we see in the SHOW PROCESSLIST database, however, the BINLOG is actually executed from the database. The BINLOG can be directly executed on the slave database or parsed into a row of DML operations. The BINLOG part is as follows:

 

========================================================== ========================================================== ======================================

Another interesting problem is that running mysqldump on the slave database for backup and using parallel replication on the slave database will cause mutual blocking of backup and replication:

In the preceding blocking, multiple SQL threads and backup threads are blocked, and MySQL cannot effectively detect the deadlock loop and trigger the rollback mechanism of the deadlock, as a result, the replication thread and backup job are mutually hang, and DBA intervention is required (cancel the backup or stop the replication). During the period when the replication SQL thread is hang, the copied IO thread can still work normally and receive the Binlog information of the master database, but slave_behind_master does not increase. If the Master/Slave replication delay is determined only by monitoring the slave_behind_master value, this will cause a serious vulnerability in latency monitoring. Therefore, in addition to monitoring the slave_behind_master value, you also need to monitor the binlog location of the master database and the binlog location of the slave database.

========================================================== ========================================================== ======================================

After finishing the work, the sister town post: