Bug #79324 Slave is ~10x slower to execute set of statements compared to master RBR
Submitted: 18 Nov 2015 12:36 Modified: 16 Jun 2016 6:01
Reporter: serge grachov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S1 (Critical)
Version:5.6.27, 5.6.29 OS:Linux
Assigned to: CPU Architecture:Any

[18 Nov 2015 12:36] serge grachov 
Description:
innoDB fallback to old style autoinc locks on Slave with RBR. both 5.6.29 and 5.7.9 use

	case AUTOINC_NEW_STYLE_LOCKING:
		/* For simple (single/multi) row INSERTs, we fallback to the
		old style only if another transaction has already acquired
		the AUTOINC lock on behalf of a LOAD FILE or INSERT ... SELECT
		etc. type of statement. */
		if (thd_sql_command(m_user_thd) == SQLCOM_INSERT
		    || thd_sql_command(m_user_thd) == SQLCOM_REPLACE) {

How to repeat:
please see https://mariadb.atlassian.net/browse/MDEV-7578 for details.

Suggested fix:
use new style for RBR, eg SQLCOM_END too ?
[20 Nov 2015 14:53] MySQL Verification Team 
Hello Serge Grachov,

Thank you for the report.

Thanks,
Umesh
[20 Nov 2015 14:55] MySQL Verification Team 
test results

Attachment: 79324_5_6_29.results (application/octet-stream, text), 277.20 KiB.
[16 Jun 2016 6:01] Erlend Dahl 
[2 Jun 2016 1:43] David Moss

This has been fixed in upcoming versions and the following was added to the
5.6.32 and 5.7.14 changelogs:

When using row-based replication and InnoDB, replication slaves were
reverting to using an older locking scheme when a transaction had already
acquired an AUTOINC lock related to a LOAD FILE or INSERT ... SELECT type of
statement, reducing replication slave performance. The fix ensures that
sql_command is set correctly for any of the DML events such as
WRITE_ROWS_EVENT,UPDATE_EVENT, and DELETE_EVENT.