Replication latency due to MySQL 5.7 concurrent replication and mysqldump mutual blocking

Originally the MySQL binlog and show processlist commands belonged to the two transactions of unrelated, but in the most recent troubleshooting, there was a significant replication delay found in the main library and from the library, but the Slave_behind_master value from the library was shown as 0, The replication SQL thread is blocking each other from the backup thread, but the deadlock is not reported.

The SQL thread waits for a lock while executing show processlist from the library while waiting for the Where condition of SQL to be similar to where c1= ' ABC ' and c2> ' 2018-03-01 ' and c2< ' 2018-03-26 ' Such a range of queries, the first time to think of is how to be a copy based on statement, unscientific Ah, our production environment unified use of row-based replication, is it developed to modify the replication format of the reply level?

To export a log using MySQL Binlog, look at:

Found the wrong research colleague, Rbr_only=yes. For replication based on row format, "SET TRANSACTION isolation Level READ COMMITTED" is also one of the typical features based on row format replication, last_ Committed and Sequence_number are used for concurrent replication in MySQL version 5.7, and Row_query is followed by the original SQL executed on the main library, which is the SQL we see from the Library show Processlist. But in fact, from the library or the Binlog part, the Binlog can directly directly on the library directly from the execution, you can also parse a row of data DML operations, the Binlog part is as follows:

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

Another interesting question is that if you are running mysqldump from the library and you are using parallel replication from the library, backup and replication will be blocked from each other:

In the above blocking, multiple SQL threads and backup threads are blocking each other, and MySQL cannot effectively detect the deadlock loop and trigger a deadlock mechanism, which causes the replication thread and backup jobs to hang with each other, requiring DBA intervention (canceling the backup or stopping replication), While the replication SQL thread is stuck, the copied IO thread still works to accept the Binlog information to the main library, but Slave_behind_master does not increase, and the master-slave replication delay is determined only by monitoring the Slave_behind_master value , there is a serious vulnerability to latency monitoring, so in addition to monitoring slave_behind_master values, you need to monitor the Binlog location point of the main library and the Binlog location point from the library when monitoring replication latency.

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

After finishing the call, Sister town stickers:

Replication latency due to MySQL 5.7 concurrent replication and mysqldump mutual blocking