• 售前

  • 售后

热门帖子
入门百科

解决MySQL 5.7中定位DDL被壅闭的题目

[复制链接]
二只怜雪 显示全部楼层 发表于 2021-10-26 14:16:38 |阅读模式 打印 上一主题 下一主题
在上篇文章《MySQL表布局变动,不可不知的Metadata Lock》中,我们先容了MDL引入的配景,及基本概念,从“道”的层面知道了什么是MDL。下面就从“术”的层面看看如何定位MDL的相关标题。
在MySQL 5.7中,针对MDL,引入了一张新表performance_schema.metadata_locks,该表可对外展示MDL的相关信息,包罗其作用对象,类型及持有等待情况。
开启MDL的instrument
但是相关instrument并没有开启(MySQL 8.0是默认开启的),其可通过如下两种方式开启,
暂时生效
修改
  1. performance_schema.setup_instrume nts表
复制代码
,但实例重启后,又会规复为默认值。
  1. UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
  2. WHERE NAME = 'wait/lock/metadata/sql/mdl';
复制代码
永世生效
在设置文件中设置
  1. [mysqld]
  2. performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
复制代码
测试场景
下面联合一个简单的Demo,来看看在MySQL 5.7中如何定位DDL操作的阻塞标题。
  1. session1> begin;
  2. Query OK, 0 rows affected (0.00 sec)
  3. session1> delete from slowtech.t1 where id=2;
  4. Query OK, 1 row affected (0.00 sec)
  5. session1> select * from slowtech.t1;
  6. +------+------+
  7. | id | name |
  8. +------+------+
  9. | 1 | a |
  10. +------+------+
  11. 1 row in set (0.00 sec)
  12. session1> update slowtech.t1 set name='c' where id=1;
  13. Query OK, 1 row affected (0.00 sec)
  14. Rows matched: 1 Changed: 1 Warnings: 0
  15. session2> alter table slowtech.t1 add c1 int; ##被阻塞
  16. session3> show processlist;
  17. +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
  18. | Id | User | Host  | db | Command | Time | State       | Info        |
  19. +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
  20. | 2 | root | localhost | NULL | Sleep | 51 |         | NULL        |
  21. | 3 | root | localhost | NULL | Query | 0 | starting      | show processlist     |
  22. | 4 | root | localhost | NULL | Query | 9 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |
  23. +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
  24. 3 rows in set (0.00 sec)
  25. session3> select object_type,object_schema,object_name,lock_type,lock_duration,lock_status,owner_thread_id from performance_schema.metadata_locks;
  26. +-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+
  27. | object_type | object_schema  | object_name | lock_type   | lock_duration | lock_status | owner_thread_id |
  28. +-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+
  29. | TABLE  | slowtech   | t1    | SHARED_WRITE  | TRANSACTION | GRANTED  |    27 |
  30. | GLOBAL  | NULL    | NULL   | INTENTION_EXCLUSIVE | STATEMENT  | GRANTED  |    29 |
  31. | SCHEMA  | slowtech   | NULL   | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED  |    29 |
  32. | TABLE  | slowtech   | t1    | SHARED_UPGRADABLE | TRANSACTION | GRANTED  |    29 |
  33. | TABLE  | slowtech   | t1    | EXCLUSIVE   | TRANSACTION | PENDING  |    29 |
  34. | TABLE  | performance_schema | metadata_locks | SHARED_READ   | TRANSACTION | GRANTED  |    28 |
  35. +-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+
  36. 6 rows in set (0.00 sec)
复制代码
这里,重点关注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. *************************** 1. row ***************************
  3.    THREAD_ID: 27
  4.     NAME: thread/sql/one_connection
  5.     TYPE: FOREGROUND
  6.   PROCESSLIST_ID: 2
  7. PROCESSLIST_USER: root
  8. PROCESSLIST_HOST: localhost
  9.   PROCESSLIST_DB: NULL
  10. PROCESSLIST_COMMAND: Sleep
  11. PROCESSLIST_TIME: 214
  12. PROCESSLIST_STATE: NULL
  13. PROCESSLIST_INFO: NULL
  14. PARENT_THREAD_ID: 1
  15.     ROLE: NULL
  16.   INSTRUMENTED: YES
  17.    HISTORY: YES
  18. CONNECTION_TYPE: Socket
  19.   THREAD_OS_ID: 9800
  20. *************************** 2. row ***************************
  21.    THREAD_ID: 29
  22.     NAME: thread/sql/one_connection
  23.     TYPE: FOREGROUND
  24.   PROCESSLIST_ID: 4
  25. PROCESSLIST_USER: root
  26. PROCESSLIST_HOST: localhost
  27.   PROCESSLIST_DB: NULL
  28. PROCESSLIST_COMMAND: Query
  29. PROCESSLIST_TIME: 172
  30. PROCESSLIST_STATE: Waiting for table metadata lock
  31. PROCESSLIST_INFO: alter table slowtech.t1 add c1 int
  32. PARENT_THREAD_ID: 1
  33.     ROLE: NULL
  34.   INSTRUMENTED: YES
  35.    HISTORY: YES
  36. CONNECTION_TYPE: Socket
  37.   THREAD_OS_ID: 9907
  38. 2 rows in set (0.00 sec)
复制代码
将这两张表联合,鉴戒sys.innodb_lock _waits的输出,实际上我们也可以直观地出现MDL的等待关系。
  1. SELECT
  2. a.OBJECT_SCHEMA AS locked_schema,
  3. a.OBJECT_NAME AS locked_table,
  4. "Metadata Lock" AS locked_type,
  5. c.PROCESSLIST_ID AS waiting_processlist_id,
  6. c.PROCESSLIST_TIME AS waiting_age,
  7. c.PROCESSLIST_INFO AS waiting_query,
  8. c.PROCESSLIST_STATE AS waiting_state,
  9. d.PROCESSLIST_ID AS blocking_processlist_id,
  10. d.PROCESSLIST_TIME AS blocking_age,
  11. d.PROCESSLIST_INFO AS blocking_query,
  12. concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
  13. FROM
  14. performance_schema.metadata_locks a
  15. JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
  16. AND a.OBJECT_NAME = b.OBJECT_NAME
  17. AND a.lock_status = 'PENDING'
  18. AND b.lock_status = 'GRANTED'
  19. AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
  20. AND a.lock_type = 'EXCLUSIVE'
  21. JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
  22. JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID\G
  23. *************************** 1. row ***************************
  24.     locked_schema: slowtech
  25.     locked_table: t1
  26.      locked_type: Metadata Lock
  27.   waiting_processlist_id: 4
  28.      waiting_age: 259
  29.     waiting_query: alter table slowtech.t1 add c1 int
  30.     waiting_state: Waiting for table metadata lock
  31.   blocking_processlist_id: 2
  32.     blocking_age: 301
  33.     blocking_query: NULL
  34. sql_kill_blocking_connection: KILL 2
  35. 1 row in set (0.00 sec)
复制代码
输出一清二楚,DDL操作如果要得到MDL,实验kill 2即可。
官方的sys.schematablelock_waits
实际上,MySQL 5.7在sys库中也集成了类似功能,同样的场景,其输出如下,
  1. mysql> select * from sys.schema_table_lock_waits\G
  2. *************************** 1. row ***************************
  3.     object_schema: slowtech
  4.      object_name: t1
  5.    waiting_thread_id: 29
  6.      waiting_pid: 4
  7.     waiting_account: root@localhost
  8.    waiting_lock_type: EXCLUSIVE
  9.   waiting_lock_duration: TRANSACTION
  10.     waiting_query: alter table slowtech.t1 add c1 int
  11.    waiting_query_secs: 446
  12. waiting_query_rows_affected: 0
  13. waiting_query_rows_examined: 0
  14.    blocking_thread_id: 27
  15.     blocking_pid: 2
  16.    blocking_account: root@localhost
  17.    blocking_lock_type: SHARED_READ
  18.   blocking_lock_duration: TRANSACTION
  19.   sql_kill_blocking_query: KILL QUERY 2
  20. sql_kill_blocking_connection: KILL 2
  21. *************************** 2. row ***************************
  22.     object_schema: slowtech
  23.      object_name: t1
  24.    waiting_thread_id: 29
  25.      waiting_pid: 4
  26.     waiting_account: root@localhost
  27.    waiting_lock_type: EXCLUSIVE
  28.   waiting_lock_duration: TRANSACTION
  29.     waiting_query: alter table slowtech.t1 add c1 int
  30.    waiting_query_secs: 446
  31. waiting_query_rows_affected: 0
  32. waiting_query_rows_examined: 0
  33.    blocking_thread_id: 29
  34.     blocking_pid: 4
  35.    blocking_account: root@localhost
  36.    blocking_lock_type: SHARED_UPGRADABLE
  37.   blocking_lock_duration: TRANSACTION
  38.   sql_kill_blocking_query: KILL QUERY 4
  39. sql_kill_blocking_connection: KILL 4
  40. 2 rows in set (0.00 sec)
复制代码
具体分析下官方的输出,
只有一个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. locked_schema,
  3. locked_table,
  4. locked_type,
  5. waiting_processlist_id,
  6. waiting_age,
  7. waiting_query,
  8. waiting_state,
  9. blocking_processlist_id,
  10. blocking_age,
  11. substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query,
  12. sql_kill_blocking_connection
  13. FROM
  14. (
  15.   SELECT
  16.    b.OWNER_THREAD_ID AS granted_thread_id,
  17.    a.OBJECT_SCHEMA AS locked_schema,
  18.    a.OBJECT_NAME AS locked_table,
  19.    "Metadata Lock" AS locked_type,
  20.    c.PROCESSLIST_ID AS waiting_processlist_id,
  21.    c.PROCESSLIST_TIME AS waiting_age,
  22.    c.PROCESSLIST_INFO AS waiting_query,
  23.    c.PROCESSLIST_STATE AS waiting_state,
  24.    d.PROCESSLIST_ID AS blocking_processlist_id,
  25.    d.PROCESSLIST_TIME AS blocking_age,
  26.    d.PROCESSLIST_INFO AS blocking_query,
  27.    concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
  28.   FROM
  29.    performance_schema.metadata_locks a
  30.   JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
  31.   AND a.OBJECT_NAME = b.OBJECT_NAME
  32.   AND a.lock_status = 'PENDING'
  33.   AND b.lock_status = 'GRANTED'
  34.   AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
  35.   AND a.lock_type = 'EXCLUSIVE'
  36.   JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
  37.   JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID
  38. ) t1,
  39. (
  40.   SELECT
  41.    thread_id,
  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.   FROM
  44.    performance_schema.events_statements_history
  45.   GROUP BY thread_id
  46. ) t2
  47. WHERE
  48. t1.granted_thread_id = t2.thread_id \G
  49. *************************** 1. row ***************************
  50.     locked_schema: slowtech
  51.     locked_table: t1
  52.      locked_type: Metadata Lock
  53.   waiting_processlist_id: 4
  54.      waiting_age: 294
  55.     waiting_query: alter table slowtech.t1 add c1 int
  56.     waiting_state: Waiting for table metadata lock
  57.   blocking_processlist_id: 2
  58.     blocking_age: 336
  59.     blocking_query: delete from slowtech.t1 where id=2;select * from slowtech.t1;update slowtech.t1 set name='c' where id=1
  60. sql_kill_blocking_connection: KILL 2
  61. 1 row in set, 1 warning (0.00 sec)
复制代码
从上面的输出可以看到,blocking_query中包罗了会话1中当前事件的全部操作,按实验的先后顺序输出。
需要留意的是,默认情况下,events_statements_history只会生存每个线程迩来的10个操作,如果事件中进行的操作较多,实际上也是没办法抓全的。
总结
以上所述是小编给各人先容的办理MySQL 5.7中如何定位DDL被阻塞的标题,盼望对各人有所帮助,如果各人有任何疑问请给我留言,小编会实时复兴各人的。在此也非常感谢各人对草根技术分享网站的支持!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作