由于MDL锁造成的主从复制延迟

• 症状：由于磁盘空间不足，dba 对 free_size 较大的表（示例表 region_backup 行数 350293，total_size 0.066 GB，free_size 0.658 GB）进行了 optimize 操作，随后收到了从库延迟的告警，整个分析过程如下（以下操作均在从库执行）：

#.查看版本
mysql> select @@version;
+---------------+
| @@version     |
+---------------+
| 5.7.32-35-log |
+---------------+

#.查看延迟，Seconds_Behind_Master: 4037
mysql> show slave status\G;
*************************** 1. row ***************************
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 4037

#.通过 performance_schema.metadata_locks 找出引起阻塞的会话，示例 lock_status 为 PENDING 的 87号线程在等待（88号线程的）MDL
mysql> select object_type,object_schema,object_name,lock_type,lock_duration,lock_status,owner_thread_id from performance_schema.metadata_locks;
+-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+
| object_type | object_schema      | object_name    | lock_type           | lock_duration | lock_status | owner_thread_id |
+-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+
| TABLE       | iot                | region_backup  | SHARED_WRITE        | TRANSACTION   | GRANTED     |              88 |
| GLOBAL      | NULL               | NULL           | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     |              87 |
| SCHEMA      | iot                | NULL           | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     |              87 |
| TABLE       | iot                | region_backup  | SHARED_NO_WRITE     | TRANSACTION   | PENDING     |              87 |
| COMMIT      | NULL               | NULL           | INTENTION_EXCLUSIVE | EXPLICIT      | GRANTED     |              88 |
| TABLE       | performance_schema | metadata_locks | SHARED_READ         | TRANSACTION   | GRANTED     |          576370 |
+-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+

#.结合 performance_schema.threads 查找线程对应的 processlist id，示例 87、88号线程对应的进程id分别为 17、18
mysql> select THREAD_ID,NAME,PROCESSLIST_ID,PROCESSLIST_TIME,PROCESSLIST_STATE from performance_schema.threads where thread_id in (87,88);
+-----------+-------------------------+----------------+------------------+---------------------------------------------+
| THREAD_ID | NAME                    | PROCESSLIST_ID | PROCESSLIST_TIME | PROCESSLIST_STATE                           |
+-----------+-------------------------+----------------+------------------+---------------------------------------------+
|        87 | thread/sql/slave_worker |             17 |             3000 | Waiting for table metadata lock             |
|        88 | thread/sql/slave_worker |             18 |             2997 | Waiting for preceding transaction to commit |
+-----------+-------------------------+----------------+------------------+---------------------------------------------+

#.通过 processlist 也能看到状态为 Waiting for table metadata lock 的进程ID为 17
mysql> show full processlist;
+----------+-------------+--------------+------+---------+------+---------------------------------------------+------------------------------+-----------+---------------+
| Id       | User        | Host         | db   | Command | Time | State                                       | Info                         | Rows_sent | Rows_examined |
+----------+-------------+--------------+------+---------+------+---------------------------------------------+------------------------------+-----------+---------------+
| 259878   | root        | localhost    | NULL | Query   |    0 | starting                                    | show full processlist        |         0 |             0 |
| 17       | system user |              | iot  | Connect | 3180 | Waiting for table metadata lock             | optimize table region_backup |         0 |             0 |
| 18       | system user |              | NULL | Connect | 3177 | Waiting for preceding transaction to commit | NULL                         |         0 |             0 |
+----------+-------------+--------------+------+---------+------+---------------------------------------------+------------------------------+-----------+---------------+

• 缓解：调整 lock_wait_timeout 参数，比如从 600 改为 1800

mysql> set global lock_wait_timeout=1800;
mysql> show global variables like 'lock_wait_timeout';

• 解决：杀掉状态为 Waiting for table metadata lock 的进程ID（本次延迟是由于主库执行 optimize 造成的，杀掉此进程不会丢失数据，可以安心杀掉），本次示例 KILL 17;

mysql> KILL 17;