Skip to content

Commit fb445c0

Browse files
author
Venkatesh Duggirala
committed
Bug#22247668 SLAVE IS ~10X SLOWER TO EXECUTE SET OF STATEMENTS COMPARED TO MASTER RBR
Analysis: A new style of locking is implemented in Innodb. Starting from 5.6, Innodb uses this new style of locking for all the cases except for the case where for a simple (single/multi) row INSERTs, it fall back to old style locking if another transaction has already acquired the AUTOINC lock on behalf of a LOAD FILE or INSERT...SELECT etc. type of statement. Code snippet: 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) { The sql_command needs to be set properly(correctly) inorder to make the above optimization work. In Replication RBR mode, Slave is not setting sql_command for any of the DML events (WRITE_ROWS_EVENT/UPDATE_EVENT/DELETE_EVENT). Fix: Set the sql_command correctly to make any of these underlying optimization code to work.
1 parent 918aba3 commit fb445c0

5 files changed

Lines changed: 89 additions & 4 deletions

File tree

‎mysql-test/suite/rpl/r/rpl_autoinc_lock_style.result‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,15 @@
1+
include/master-slave.inc
2+
Warnings:
3+
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4+
Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
5+
[connection master]
6+
CREATE TABLE t (i INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
7+
include/sync_slave_sql_with_master.inc
8+
SET @save_debug=@@debug;
9+
SET GLOBAL DEBUG='+d,die_if_autoinc_old_lock_style_used';
10+
[connection master]
11+
INSERT INTO t VALUES (1);
12+
DROP TABLE t;
13+
include/sync_slave_sql_with_master.inc
14+
SET GLOBAL DEBUG=@save_debug;
15+
include/rpl_end.inc

‎mysql-test/suite/rpl/t/rpl_autoinc_lock_style.test‎

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,46 @@
1+
###############################################################################
2+
# Bug#22247668 SLAVE IS ~10X SLOWER TO EXECUTE SET OF STATEMENTS COMPARED TO
3+
# MASTER RBR
4+
# Problem: A new style of locking is implemented in Innodb. Starting from 5.6,
5+
# Innodb uses this new style of locking for all the cases except for the case
6+
# where for a simple (single/multi) row INSERTs, it fall back to old style
7+
# locking if another transaction has already acquired the AUTOINC lock on behalf of
8+
# a LOAD FILE or INSERT...SELECT etc. type of statement. But on
9+
# Slave, in RBR format, it is always using old style auto inc
10+
# algorithm.
11+
#
12+
# Steps to reproduce:
13+
# 1) Setup DEBUG simulation point on Slave to bring the server down
14+
# if the INSERT enters old style autoinc locking method.
15+
#
16+
# 2) Execute AUTOINC related work on Master.
17+
#
18+
# 3) Make sure that sync on slave happens without any issues.
19+
#
20+
###############################################################################
21+
--source include/have_debug.inc
22+
--source include/have_binlog_format_row.inc
23+
--source include/master-slave.inc
24+
25+
# Initial setup
26+
CREATE TABLE t (i INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
27+
--source include/sync_slave_sql_with_master.inc
28+
29+
# Step-1 : Setup DEBUG simulation point on Slave to bring the server down
30+
# if the INSERT enters old style autoinc locking method.
31+
SET @save_debug=@@debug;
32+
SET GLOBAL DEBUG='+d,die_if_autoinc_old_lock_style_used';
33+
34+
# Step-2 :Execute AUTOINC related work on Master.
35+
--source include/rpl_connection_master.inc
36+
INSERT INTO t VALUES (1);
37+
DROP TABLE t;
38+
39+
# Step-3: Due to above DEBUG simulation point, server will go down if it enters
40+
# old autoinc lock style. After fix, sync on Slave happens without any issues.
41+
--source include/sync_slave_sql_with_master.inc
42+
43+
# Cleanup
44+
# Reset the simulation point on Slave.
45+
SET GLOBAL DEBUG=@save_debug;
46+
--source include/rpl_end.inc

‎sql/binlog.cc‎

Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8454,11 +8454,9 @@ int THD::decide_logging_format(TABLE_LIST *tables)
84548454
DBUG_RETURN(-1);
84558455
}
84568456

8457-
if (is_write &&
8458-
lex->sql_command != SQLCOM_END /* rows-event applying by slave */)
8457+
if (is_write)
84598458
{
84608459
/*
8461-
Master side of DML in the STMT format events parallelization.
84628460
All involving table db:s are stored in a abc-ordered name list.
84638461
In case the number of databases exceeds MAX_DBS_IN_EVENT_MTS maximum
84648462
the list gathering breaks since it won't be sent to the slave.

‎sql/log_event.cc‎

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -11634,6 +11634,12 @@ static int rows_event_stmt_cleanup(Relay_log_info const *rli, THD * thd)
1163411634
thd->reset_current_stmt_binlog_format_row();
1163511635

1163611636
const_cast<Relay_log_info*>(rli)->cleanup_context(thd, 0);
11637+
11638+
/*
11639+
Clean sql_command value
11640+
*/
11641+
thd->lex->sql_command= SQLCOM_END;
11642+
1163711643
}
1163811644
return error;
1163911645
}
@@ -12469,6 +12475,11 @@ Write_rows_log_event::do_before_row_operations(const Slave_reporting_capability
1246912475
if (get_flags(STMT_END_F))
1247012476
status_var_increment(thd->status_var.com_stat[SQLCOM_INSERT]);
1247112477

12478+
/*
12479+
Let storage engines treat this event as an INSERT command
12480+
*/
12481+
thd->lex->sql_command= SQLCOM_INSERT;
12482+
1247212483
/**
1247312484
todo: to introduce a property for the event (handler?) which forces
1247412485
applying the event in the replace (idempotent) fashion.
@@ -12973,6 +12984,12 @@ Delete_rows_log_event::do_before_row_operations(const Slave_reporting_capability
1297312984
*/
1297412985
if (get_flags(STMT_END_F))
1297512986
status_var_increment(thd->status_var.com_stat[SQLCOM_DELETE]);
12987+
12988+
/*
12989+
Let storage engines treat this event as a DELETE command
12990+
*/
12991+
thd->lex->sql_command= SQLCOM_DELETE;
12992+
1297612993
error= row_operations_scan_and_key_setup();
1297712994
DBUG_RETURN(error);
1297812995

@@ -13082,6 +13099,12 @@ Update_rows_log_event::do_before_row_operations(const Slave_reporting_capability
1308213099
*/
1308313100
if (get_flags(STMT_END_F))
1308413101
status_var_increment(thd->status_var.com_stat[SQLCOM_UPDATE]);
13102+
13103+
/*
13104+
Let storage engines treat this event as an UPDATE command
13105+
*/
13106+
thd->lex->sql_command= SQLCOM_UPDATE;
13107+
1308513108
error= row_operations_scan_and_key_setup();
1308613109
DBUG_RETURN(error);
1308713110

‎storage/innobase/handler/ha_innodb.cc‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6529,6 +6529,7 @@ dberr_t
65296529
ha_innobase::innobase_lock_autoinc(void)
65306530
/*====================================*/
65316531
{
6532+
DBUG_ENTER("ha_innobase::innobase_lock_autoinc");
65326533
dberr_t error = DB_SUCCESS;
65336534

65346535
ut_ad(!srv_read_only_mode);
@@ -6563,6 +6564,8 @@ ha_innobase::innobase_lock_autoinc(void)
65636564
/* Fall through to old style locking. */
65646565

65656566
case AUTOINC_OLD_STYLE_LOCKING:
6567+
DBUG_EXECUTE_IF("die_if_autoinc_old_lock_style_used",
6568+
ut_ad(0););
65666569
error = row_lock_table_autoinc_for_mysql(prebuilt);
65676570

65686571
if (error == DB_SUCCESS) {
@@ -6576,7 +6579,7 @@ ha_innobase::innobase_lock_autoinc(void)
65766579
ut_error;
65776580
}
65786581

6579-
return(error);
6582+
DBUG_RETURN(error);
65806583
}
65816584

65826585
/********************************************************************//**

0 commit comments

Comments
 (0)