ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > 数据库开发技术 > MySQL 5.7中如何定位DDL被阻塞的问题

MySQL 5.7中如何定位DDL被阻塞的问题

原创 数据库开发技术 作者：MySQL实战 时间：2018-08-14 20:03:02 0 删除 编辑

在上篇文章《MySQL表结构变更，不可不知的Metadata Lock》中，我们介绍了MDL引入的背景，及基本概念，从“道”的层面知道了什么是MDL。下面就从“术”的层面看看如何定位MDL的相关问题。

在MySQL 5.7中，针对MDL，引入了一张新表performance_schema.metadata_locks，该表可对外展示MDL的相关信息，包括其作用对象，类型及持有等待情况。

开启MDL的instrument

但是相关instrument并没有开启（MySQL 8.0是默认开启的），其可通过如下两种方式开启，

临时生效

修改performance_schema.setup_instrume nts表，但实例重启后，又会恢复为默认值。

    

  1. UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
    2. 

  2. WHERE NAME = 'wait/lock/metadata/sql/mdl';
    3.

永久生效

在配置文件中设置

    

  1. [mysqld]
    2. 

  2. performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
    3.

测试场景

下面结合一个简单的Demo，来看看在MySQL 5.7中如何定位DDL操作的阻塞问题。

    

  1. session1> begin;
    2. 

  2. Query OK, 0 rows affected (0.00 sec)
    3. 

    4. 

  4. session1> delete from slowtech.t1 where id=2;
    5. 

  5. Query OK, 1 row affected (0.00 sec)
    6. 

    7. 

  7. session1> select * from slowtech.t1;
    8. 

  8. +------+------+
    9. 

  9. | id   | name |
    10. 

  10. +------+------+
    11. 

  11. |    1 | a    |
    12. 

  12. +------+------+
    13. 

  13. 1 row in set (0.00 sec)
    14. 

    15. 

  15. session1> update slowtech.t1 set name='c' where id=1;
    16. 

  16. Query OK, 1 row affected (0.00 sec)
    17. 

  17. Rows matched: 1  Changed: 1  Warnings: 0
    18. 

    19. 

  19. session2> alter table slowtech.t1 add c1 int; ##被阻塞
    20. 

    21. 

  21. session3> show processlist;
    22. 

  22. +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
    23. 

  23. | Id | User | Host      | db   | Command | Time | State                           | Info                               |
    24. 

  24. +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
    25. 

  25. |  2 | root | localhost | NULL | Sleep   |   51 |                                 | NULL                               |
    26. 

  26. |  3 | root | localhost | NULL | Query   |    0 | starting                        | show processlist                   |
    27. 

  27. |  4 | root | localhost | NULL | Query   |    9 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |
    28. 

  28. +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
    29. 

  29. 3 rows in set (0.00 sec)
    30. 

    31. 

  31. session3> select object_type,object_schema,object_name,lock_type,lock_duration,lock_status,owner_thread_id from performance_schema.metadata_locks;
    32. 

  32. +-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+
    33. 

  33. | object_type | object_schema      | object_name    | lock_type           | lock_duration | lock_status | owner_thread_id |
    34. 

  34. +-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+
    35. 

  35. | TABLE       | slowtech           | t1             | SHARED_WRITE        | TRANSACTION   | GRANTED     |              27 |
    36. 

  36. | GLOBAL      | NULL               | NULL           | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     |              29 |
    37. 

  37. | SCHEMA      | slowtech           | NULL           | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     |              29 |
    38. 

  38. | TABLE       | slowtech           | t1             | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     |              29 |
    39. 

  39. | TABLE       | slowtech           | t1             | EXCLUSIVE           | TRANSACTION   | PENDING     |              29 |
    40. 

  40. | TABLE       | performance_schema | metadata_locks | SHARED_READ         | TRANSACTION   | GRANTED     |              28 |
    41. 

  41. +-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+
    42. 

  42. 6 rows in set (0.00 sec)
    43.

                提示，类似代码可左右滑动

这里，重点关注lock_status，"PENDING"代表线程在等待MDL，而"GRANTED"则代表线程持有MDL。

如何找出引起阻塞的会话

结合owner_thread_id，可以可到，是29号线程在等待27号线程的MDL，此时，可kill掉52号线程。

但需要注意的是，owner_thread_id给出的只是线程ID，并不是show processlist中的ID。如果要查找线程对应的processlist id，需查询performance_schema.threads表。

    

  1. session3> select * from performance_schema.threads where thread_id in (27,29)\G
    2. 

  2. *************************** 1. row ***************************
    3. 

  3.           THREAD_ID: 27
    4. 

  4.                NAME: thread/sql/one_connection
    5. 

  5.                TYPE: FOREGROUND
    6. 

  6.      PROCESSLIST_ID: 2
    7. 

  7.    PROCESSLIST_USER: root
    8. 

  8.    PROCESSLIST_HOST: localhost
    9. 

  9.      PROCESSLIST_DB: NULL
    10. 

  10. PROCESSLIST_COMMAND: Sleep
    11. 

  11.    PROCESSLIST_TIME: 214
    12. 

  12.   PROCESSLIST_STATE: NULL
    13. 

  13.    PROCESSLIST_INFO: NULL
    14. 

  14.    PARENT_THREAD_ID: 1
    15. 

  15.                ROLE: NULL
    16. 

  16.        INSTRUMENTED: YES
    17. 

  17.             HISTORY: YES
    18. 

  18.     CONNECTION_TYPE: Socket
    19. 

  19.        THREAD_OS_ID: 9800
    20. 

  20. *************************** 2. row ***************************
    21. 

  21.           THREAD_ID: 29
    22. 

  22.                NAME: thread/sql/one_connection
    23. 

  23.                TYPE: FOREGROUND
    24. 

  24.      PROCESSLIST_ID: 4
    25. 

  25.    PROCESSLIST_USER: root
    26. 

  26.    PROCESSLIST_HOST: localhost
    27. 

  27.      PROCESSLIST_DB: NULL
    28. 

  28. PROCESSLIST_COMMAND: Query
    29. 

  29.    PROCESSLIST_TIME: 172
    30. 

  30.   PROCESSLIST_STATE: Waiting for table metadata lock
    31. 

  31.    PROCESSLIST_INFO: alter table slowtech.t1 add c1 int
    32. 

  32.    PARENT_THREAD_ID: 1
    33. 

  33.                ROLE: NULL
    34. 

  34.        INSTRUMENTED: YES
    35. 

  35.             HISTORY: YES
    36. 

  36.     CONNECTION_TYPE: Socket
    37. 

  37.        THREAD_OS_ID: 9907
    38. 

  38. 2 rows in set (0.00 sec)
    39.

将这两张表结合，借鉴sys.innodb_lock _waits的输出，实际上我们也可以直观地呈现MDL的等待关系。

    

  1. SELECT
    2. 

  2.     a.OBJECT_SCHEMA AS locked_schema,
    3. 

  3.     a.OBJECT_NAME AS locked_table,
    4. 

  4.     "Metadata Lock" AS locked_type,
    5. 

  5.     c.PROCESSLIST_ID AS waiting_processlist_id,
    6. 

  6.     c.PROCESSLIST_TIME AS waiting_age,
    7. 

  7.     c.PROCESSLIST_INFO AS waiting_query,
    8. 

  8.     c.PROCESSLIST_STATE AS waiting_state,
    9. 

  9.     d.PROCESSLIST_ID AS blocking_processlist_id,
    10. 

  10.     d.PROCESSLIST_TIME AS blocking_age,
    11. 

  11.     d.PROCESSLIST_INFO AS blocking_query,
    12. 

  12.     concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
    13. 

  13. FROM
    14. 

  14.     performance_schema.metadata_locks a
    15. 

  15. JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
    16. 

  16. AND a.OBJECT_NAME = b.OBJECT_NAME
    17. 

  17. AND a.lock_status = 'PENDING'
    18. 

  18. AND b.lock_status = 'GRANTED'
    19. 

  19. AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
    20. 

  20. AND a.lock_type = 'EXCLUSIVE'
    21. 

  21. JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
    22. 

  22. JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID\G
    23. 

    24. 

  24. *************************** 1. row ***************************
    25. 

  25.                locked_schema: slowtech
    26. 

  26.                 locked_table: t1
    27. 

  27.                  locked_type: Metadata Lock
    28. 

  28.       waiting_processlist_id: 4
    29. 

  29.                  waiting_age: 259
    30. 

  30.                waiting_query: alter table slowtech.t1 add c1 int
    31. 

  31.                waiting_state: Waiting for table metadata lock
    32. 

  32.      blocking_processlist_id: 2
    33. 

  33.                 blocking_age: 301
    34. 

  34.               blocking_query: NULL
    35. 

  35. sql_kill_blocking_connection: KILL 2
    36. 

  36. 1 row in set (0.00 sec)
    37.

输出一目了然，DDL操作如果要获得MDL，执行kill 2即可。

官方的sys.schematablelock_waits

实际上，MySQL 5.7在sys库中也集成了类似功能，同样的场景，其输出如下，

    

  1. mysql> select * from sys.schema_table_lock_waits\G
    2. 

  2. *************************** 1. row ***************************
    3. 

  3.                object_schema: slowtech
    4. 

  4.                  object_name: t1
    5. 

  5.            waiting_thread_id: 29
    6. 

  6.                  waiting_pid: 4
    7. 

  7.              waiting_account: root@localhost
    8. 

  8.            waiting_lock_type: EXCLUSIVE
    9. 

  9.        waiting_lock_duration: TRANSACTION
    10. 

  10.                waiting_query: alter table slowtech.t1 add c1 int
    11. 

  11.           waiting_query_secs: 446
    12. 

  12.  waiting_query_rows_affected: 0
    13. 

  13.  waiting_query_rows_examined: 0
    14. 

  14.           blocking_thread_id: 27
    15. 

  15.                 blocking_pid: 2
    16. 

  16.             blocking_account: root@localhost
    17. 

  17.           blocking_lock_type: SHARED_READ
    18. 

  18.       blocking_lock_duration: TRANSACTION
    19. 

  19.      sql_kill_blocking_query: KILL QUERY 2
    20. 

  20. sql_kill_blocking_connection: KILL 2
    21. 

  21. *************************** 2. row ***************************
    22. 

  22.                object_schema: slowtech
    23. 

  23.                  object_name: t1
    24. 

  24.            waiting_thread_id: 29
    25. 

  25.                  waiting_pid: 4
    26. 

  26.              waiting_account: root@localhost
    27. 

  27.            waiting_lock_type: EXCLUSIVE
    28. 

  28.        waiting_lock_duration: TRANSACTION
    29. 

  29.                waiting_query: alter table slowtech.t1 add c1 int
    30. 

  30.           waiting_query_secs: 446
    31. 

  31.  waiting_query_rows_affected: 0
    32. 

  32.  waiting_query_rows_examined: 0
    33. 

  33.           blocking_thread_id: 29
    34. 

  34.                 blocking_pid: 4
    35. 

  35.             blocking_account: root@localhost
    36. 

  36.           blocking_lock_type: SHARED_UPGRADABLE
    37. 

  37.       blocking_lock_duration: TRANSACTION
    38. 

  38.      sql_kill_blocking_query: KILL QUERY 4
    39. 

  39. sql_kill_blocking_connection: KILL 4
    40. 

  40. 2 rows in set (0.00 sec)
    41.

具体分析下官方的输出，

只有一个alter table操作，却产生了两条记录，而且两条记录的kill对象竟然还不一样，对表结构不熟悉及不仔细看记录内容的话，难免会kill错对象。

不仅如此，如果有N个查询被DDL操作堵塞，则会产生N*2条记录。在阻塞操作较多的情况下，这N*2条记录完全是个噪音。

而之前的SQL，无论有多少操作被阻塞，一个alter table操作，就只会输出一条记录。

如何查看阻塞会话已经执行过的操作

但上面这个SQL也有遗憾，其blocking_query为NULL，而在会话1中，其明明已经执行了三个SQL。

这个与performance_schema.threads（类似于show processlist）有关，其只会输出当前正在运行的SQL，对于已经执行过的，实际上是没办法看到。

但在线上，kill是一个需要谨慎的操作，毕竟你很难知道kill的是不是业务关键操作？又或者，是个批量update操作？那么，有没有办法抓到该事务之前的操作呢？

答案，有。

即Performance Schema中记录Statement Event（操作事件）的表，具体包括events_statements_current,events_statements_history,events_statements_history_long,prepared_statements_instances。

常用的是前面三个。

三者的表结构完全一致，其中，events_statements_history又包含了events_statements_current的操作，所以我们这里会使用events_statements_history。

终极SQL如下，

    

  1. SELECT
    2. 

  2.     locked_schema,
    3. 

  3.     locked_table,
    4. 

  4.     locked_type,
    5. 

  5.     waiting_processlist_id,
    6. 

  6.     waiting_age,
    7. 

  7.     waiting_query,
    8. 

  8.     waiting_state,
    9. 

  9.     blocking_processlist_id,
    10. 

  10.     blocking_age,
    11. 

  11.     substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query,
    12. 

  12.     sql_kill_blocking_connection
    13. 

  13. FROM
    14. 

  14.     (
    15. 

  15.         SELECT
    16. 

  16.             b.OWNER_THREAD_ID AS granted_thread_id,
    17. 

  17.             a.OBJECT_SCHEMA AS locked_schema,
    18. 

  18.             a.OBJECT_NAME AS locked_table,
    19. 

  19.             "Metadata Lock" AS locked_type,
    20. 

  20.             c.PROCESSLIST_ID AS waiting_processlist_id,
    21. 

  21.             c.PROCESSLIST_TIME AS waiting_age,
    22. 

  22.             c.PROCESSLIST_INFO AS waiting_query,
    23. 

  23.             c.PROCESSLIST_STATE AS waiting_state,
    24. 

  24.             d.PROCESSLIST_ID AS blocking_processlist_id,
    25. 

  25.             d.PROCESSLIST_TIME AS blocking_age,
    26. 

  26.             d.PROCESSLIST_INFO AS blocking_query,
    27. 

  27.             concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
    28. 

  28.         FROM
    29. 

  29.             performance_schema.metadata_locks a
    30. 

  30.         JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
    31. 

  31.         AND a.OBJECT_NAME = b.OBJECT_NAME
    32. 

  32.         AND a.lock_status = 'PENDING'
    33. 

  33.         AND b.lock_status = 'GRANTED'
    34. 

  34.         AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
    35. 

  35.         AND a.lock_type = 'EXCLUSIVE'
    36. 

  36.         JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
    37. 

  37.         JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID
    38. 

  38.     ) t1,
    39. 

  39.     (
    40. 

  40.         SELECT
    41. 

  41.             thread_id,
    42. 

  42.             group_concat(    CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text
    43. 

  43.         FROM
    44. 

  44.             performance_schema.events_statements_history
    45. 

  45.         GROUP BY thread_id
    46. 

  46.     ) t2
    47. 

  47. WHERE
    48. 

  48.     t1.granted_thread_id = t2.thread_id \G
    49. 

    50. 

  50. *************************** 1. row ***************************
    51. 

  51.                locked_schema: slowtech
    52. 

  52.                 locked_table: t1
    53. 

  53.                  locked_type: Metadata Lock
    54. 

  54.       waiting_processlist_id: 4
    55. 

  55.                  waiting_age: 294
    56. 

  56.                waiting_query: alter table slowtech.t1 add c1 int
    57. 

  57.                waiting_state: Waiting for table metadata lock
    58. 

  58.      blocking_processlist_id: 2
    59. 

  59.                 blocking_age: 336
    60. 

  60.               blocking_query: delete from slowtech.t1 where id=2;select * from slowtech.t1;update slowtech.t1 set name='c' where id=1
    61. 

  61. sql_kill_blocking_connection: KILL 2
    62. 

  62. 1 row in set, 1 warning (0.00 sec)
    63.

              SQL可点击文末的“阅读原文”获取

从上面的输出可以看到，blocking_query中包含了会话1中当前事务的所有操作，按执行的先后顺序输出。

需要注意的是，默认情况下，events_statements_history只会保留每个线程最近的10个操作，如果事务中进行的操作较多，实际上也是没办法抓全的。

Anyway, it is better than nothing！

欢迎阅读公众号其它文章

    MySQL表结构变更，不可不知的Metadata Lock

    升级MySQL5.7，唯一需要开发Review代码地方

    MySQL root密码忘记，原来还有更优雅的解法

    MySQL 8 新特性之自增主键的持久化

    MySQL 8 新特性之降序索引

    MySQL 8 新特性之持久化全局变量的修改

    MySQL 8 新特性之Invisible Indexes


原文作者：MySQL实战；如有侵权，请联系管理员删除。

0
0
上一篇： MySQL 5.6中如何定位DDL被阻塞的问题
下一篇： MySQL表结构变更，不可不知的Metadata Lock
请登录后发表评论 登录
全部评论
MySQL实战
陈臣，《MySQL实战》作者

注册时间：2024-01-02

  • 博文量
    51
  • 访问量
    30416

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593