Bug #96400 MTS STOP SLAVE takes over a minute when master crashed during event logging

Submitted: 1 Aug 2019 13:30 Modified: 2 Aug 2019 10:49
Reporter: Przemyslaw Malkowski Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.7.25, 5.7.27 OS:Any
Assigned to: CPU Architecture:Any

[1 Aug 2019 13:30] Przemyslaw Malkowski
Description:
After a master crashes during binlogging replication event, where the slave already started to copy it to it's relay log, but didn't finish, a STOP SLAVE on such multi-threaded slave takes longer then expected.
In the test case below, it takes over one minute. That long delay may be a problem for failover automation scripts.
The issue does not happen when slave_parallel_workers=0.

How to repeat:
Create simple master-slave 5.7 pair (I used dbdeployer here), and set the parallel replication on slave:

slave_parallel_workers=2
slave_parallel_type=LOGICAL_CLOCK

Created one big table with sysbench and an empty table copy:

$ sysbench /usr/share/sysbench/oltp_read_write.lua --mysql_storage_engine=innodb  --table-size=100000 --mysql-db=test --mysql-user=msandbox --mysql-password=msandbox --db-driver=mysql --mysql-socket=/tmp/mysql_sandbox19226.sock prepare
sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)

Creating table 'sbtest1'...
Inserting 100000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...

master [localhost:19226] {msandbox} (test) > create table sbtest10 like sbtest1;
Query OK, 0 rows affected (0.75 sec)

Then, execute INSERT INTO ... SELECT to populate the second copy, and sent kill signal to the master immediately after:

$ rsandbox_ps5_7_25/m test -e "insert into sbtest10 select * from sbtest1"; kill -9 $(pgrep -f mysql_sandbox19226);

The slave is trying to reconnect:
slave1 [localhost:19227] {msandbox} ((none)) > show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Reconnecting after a failed master event read
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 19226
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000011
          Read_Master_Log_Pos: 11476121
               Relay_Log_File: mysql-relay.000030
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000011
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
(...)

and we can see the Relay_Log_Space increased, which proves that some of that transaction was delivered. An attempt to stop the slave is greatly delayed:

slave1 [localhost:19227] {msandbox} ((none)) > stop slave;
Query OK, 0 rows affected (1 min 2.66 sec)

From another session, it looks like this:

slave1 [localhost:19227] {msandbox} ((none)) > show processlist;
+-----+-------------+-----------+------+---------+------+-----------------------------------------------+------------------+-----------+---------------+
| Id  | User        | Host      | db   | Command | Time | State                                         | Info             | Rows_sent | Rows_examined |
+-----+-------------+-----------+------+---------+------+-----------------------------------------------+------------------+-----------+---------------+
|  39 | msandbox    | localhost | NULL | Query   |   63 | Killing slave                                 | stop slave       |         0 |             0 |
| 109 | system user |           | NULL | Connect |   82 | Reconnecting after a failed master event read | NULL             |         0 |             0 |
| 110 | system user |           | NULL | Connect |   72 | Waiting for workers to exit                   | NULL             |         0 |             0 |
| 111 | system user |           | NULL | Connect |   77 | Executing event                               | NULL             |         0 |             0 |
| 113 | msandbox    | localhost | NULL | Query   |    0 | starting                                      | show processlist |         0 |             0 |
+-----+-------------+-----------+------+---------+------+-----------------------------------------------+------------------+-----------+---------------+
5 rows in set (0.00 sec)

The problem is fully reproducible this way.

Suggested fix:
STOP SLAVE command should return as fast as for single-threaded slave. Especially that in the test above, there was only one transaction being executed. Or at least there should be some variable we could adjust this timeout (if I am not missing any existing one here).
[2 Aug 2019 10:49] MySQL Verification Team
Hello Przemyslaw,

Thank you for the report.

regards,
Umesh
[2 Aug 2019 10:50] MySQL Verification Team
Test results - 5.7.27

Attachment: 96400_5.7.27.results (application/octet-stream, text), 10.56 KiB.

[4 Sep 2019 13:09] Przemysław Skibiński
5.7.27 patch for the bug based on Facebook's implementation with improvements from Percona

Attachment: bug96400_5.7.27.patch (text/x-patch), 15.32 KiB.

[15 Jun 2022 19:30] Jean-François Gagné
Careful about using the patch mentioned above, there is a bug in it.  The fix can be found in [1].
[1]: https://jira.percona.com/browse/PS-7147

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.060 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.