Analyzing and Simulating MySQL show slave status Hang: Mutex Locks and Debugging

In MySQL daily operations, a typical high‑availability setup uses one master and multiple slaves. The show slave status command normally reveals replication status, but it can hang due to mutex lock contention.

Using pstack we captured thread stacks showing that the command blocks while waiting for a mutex. The relevant locks include channel_map read lock, global_sid_lock write lock, and several mi data locks.

We built a debug build of MySQL 5.7.41 and examined the source files sql_parse.cc , rpl_slave.cc and rpl_master.cc . The execution flow of show slave status passes through show_slave_status_cmd , which acquires channel_map.rdlock() and, if a Master_info is present, also locks global_sid_lock . The show_slave_status function further locks global_sid_lock and various mi mutexes before sending data.

Thread 6 (Thread 0xxxx (LWP xxx)):
#0  0x..... in __lll_lock_wait () from /lib64/libpthread.so.0
#1  0x..... in _L_lock_975 () from /lib64/libpthread.so.0
#2  0x..... in pthread_mutex_lock () from /lib64/libpthread.so.0
#3  0x..... in inline_mysql_mutex_lock (that=0x1358520 <LOCK_active_mi>, src_file=<optimized out>, src_line=<optimized out>) at .../mysql-5.6.40/include/mysql/psi/mysql_thread.h:688
#4  0x..... in mysql_execute_command (thd=thd@entry=0x5d0abb0) at .../mysql-5.6.40/sql/sql_parse.cc:2853
#5  0x..... in mysql_parse (thd=thd@entry=0x5d0abb0, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x7fe44c644690) at .../mysql-5.6.40/sql/sql_parse.cc:6453
#6  0x..... in dispatch_command (command=<optimized out>, thd=0x5d0abb0, packet=0x5d0def1 "show slave status", packet_length=17) at .../mysql-5.6.40/sql/sql_parse.cc:1374

case SQLCOM_SHOW_SLAVE_STAT:
{
  /* Accept one of two privileges */
  if (check_global_access(thd, SUPER_ACL | REPL_CLIENT_ACL))
    goto error;
  res= show_slave_status_cmd(thd);
  break;
}

bool show_slave_status_cmd(THD *thd)
{
  Master_info *mi= 0;
  LEX *lex= thd->lex;
  bool res;
  ......
  channel_map.rdlock();
  if (!lex->mi.for_channel)
    res= show_slave_status(thd);
  else
  {
    ......
    res= show_slave_status(thd, mi);
  }
  channel_map.unlock();
  ......
}

bool show_slave_status(THD* thd, Master_info* mi)
{
  ......
  if (mi != NULL)
  {
    global_sid_lock->wrlock();
    ......
    global_sid_lock->unlock();
  }
  ......
  show_slave_status_metadata(field_list, io_gtid_set_size, sql_gtid_set_size);
  ......
  if (mi != NULL && mi->host[0])
  {
    if (show_slave_status_send_data(thd, mi,io_gtid_set_buffer, sql_gtid_set_buffer))
      ......
  }
}

bool show_master_status(THD* thd)
{
  ......
  global_sid_lock->wrlock();
  ......
  global_sid_lock->unlock();
  ......
}

To reproduce the hang, we set breakpoints in show_master_status to hold global_sid_lock while a second session runs show slave status . GDB commands illustrate attaching to the MySQL process, listing threads, setting breakpoints, and observing the blocked and unblocked sessions.

# 查看 MySQL 进程
[root@localhost ~]# ps -ef|grep mysql
root     14949 14919  0 23:13 pts/3    00:00:00 grep --color=auto mysql
mysql    31658     1  0 06:52 ?        00:01:14 /root/mysql-5.7.41/build/sql/mysqld --defaults-file=/etc/my.cnf --user=mysql

# 会话 A
mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
|  4 | root | localhost | NULL | Query   |    0 | starting | show processlist |
|  5 | root | localhost | NULL | Sleep   |    9 |          | NULL             |
+----+------+-----------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

# 会话 B
mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
|  4 | root | localhost | NULL | Sleep   |  154 |          | NULL             |
|  5 | root | localhost | NULL | Query   |    0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

# 关联 MySQL 进程
(gdb) attach 31658
Attaching to process 31658

# 查看相关线程
(gdb) info threads
  Id   Target Id         Frame
* 1    Thread 0x2b0ca2618480 (LWP 31658) "mysqld" 0x00002b0ca4113ddd in poll () from /lib64/libc.so.6
  31   Thread 0x2b0caff6a700 (LWP 16055) "mysqld" (running)
  32   Thread 0x2b0caff28700 (LWP 16090) "mysqld" (running)

# 切换到指定线程，会话 A
(gdb) t 31
[Switching to thread 31 (Thread 0x2b0caff6a700 (LWP 16055))]

# 设置断点，持有锁而不释放
(gdb) b rpl_master.cc:647
Breakpoint 1 at 0x181a565: file /root/mysql-5.7.41/sql/rpl_master.cc, line 647.

# 设置断点，释放锁
(gdb) b rpl_master.cc:649
Breakpoint 2 at 0x181a577: file /root/mysql-5.7.41/sql/rpl_master.cc, line 649.

# 查看断点
(gdb) info breakpoints
Num     Type           Disp Enb Address            What
1       breakpoint     keep y   0x000000000181a565 in show_master_status(THD*) at /root/mysql-5.7.41/sql/rpl_master.cc:647
2       breakpoint     keep y   0x000000000181a577 in show_master_status(THD*) at /root/mysql-5.7.41/sql/rpl_master.cc:649

The experiment shows that if global_sid_lock is held by one session, another session’s show slave status will block; once the lock is released, the command returns immediately.

In practice, when show slave status hangs, checking the mutexes involved, using pstack , and querying performance_schema.threads can help locate the contention point.