Bug #71959 non blocking SHOW MASTER STATUS / SHOW BINARY LOGS

• Submitted: 6 Mar 2014 22:30
• Modified: 12 Mar 2014 9:55
• Reporter: Santosh Praneeth Banda
• Status: Verified
• Category: MySQL Server: Replication
• Severity: S2 (Serious)
• Version: 5.7.2
• OS: Any
• CPU Architecture: Any
• Tags: replication

[6 Mar 2014 22:30] Santosh Praneeth Banda

Description:
commands like SHOW MASTER STATUS / SHOW BINARY LOGS takes the hot mutex LOCK_log mutex to find out the binlog file name and binlog file pos.

This causes several issues

1. When binlog disk partition is starved for IO due to lots of binlog readers, the writes to binlog are stalled and the binlog group commit holds LOCK_log for a longer time. This is in turn worsened by the commands like "SHOW MASTER STATUS" trying to take LOCK_log mutex.

2. When there are no semi_sync tailers all the commits are blocked when loss less semi-sync is used. This causes the writes to binlog hold LOCK_log and get blocked to get the LOCK_sync mutex. This causes "SHOW MASTER STATUS" to hold indefinitely.

How to repeat:
In the description for part 1.

For part2

1. use test; create table t1(a int);
2. Turn on loss less semi-sync
3. set global rpl_semi_sync_master_timeout = 1000000000000 (very high value)
4. use test; insert into t1 values(1);
5. use test; insert into t1 values(2);
6. SHOW MASTER STATUS; blocked indefinitely.

Suggested fix:
This can be improved such that the commands SHOW MASTER STATUS/ SHOW BINARY LOGS can no longer need to take the hot mutex LOCK_log. This is how it can be done 

A variable binlog_file_name similar to binlog_end_pos should be added and can be updated inside update_binlog_end_pos() to match the latest contents of MYSQL_BIN_LOG::log_file_name.

1. hold LOCK_binlog_end_pos mutex.
2. read the binlog_file_name value
3. read the binlog_end_pos value which is added in 5.7.2
4. release LOCK_binlog_end_pos mutex

[12 Mar 2014 9:55] MySQL Verification Team

Hello Santosh,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh

[12 Mar 2014 9:57] MySQL Verification Team

//

Checked with 5.7.5, SHOW BINARY LOGS/SHOW MASTER STATUS took longer than 10min.

mysql> show binary logs;
+-------------------------+-----------+
| Log_name                | File_size |
+-------------------------+-----------+
| cluster-repo-bin.000013 | 647764471 |
| cluster-repo-bin.000014 |       120 |
+-------------------------+-----------+
2 rows in set (5 min 4.77 sec)

mysql> show binary logs;
+-------------------------+-----------+
| Log_name                | File_size |
+-------------------------+-----------+
| cluster-repo-bin.000013 | 647764471 |
| cluster-repo-bin.000014 |       120 |
+-------------------------+-----------+
2 rows in set (10 min 7.00 sec)