在上篇文章《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表
,但实例重启后,又会恢复为默认值。
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';
永久生效
在配置文件中设置
[mysqld] performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
测试场景
下面结合一个简单的Demo,来看看在MySQL 5.7中如何定位DDL操作的阻塞问题。
session1> begin; Query OK, 0 rows affected (0.00 sec) session1> delete from slowtech.t1 where id=2; Query OK, 1 row affected (0.00 sec) session1> select * from slowtech.t1; +------+------+ | id | name | +------+------+ | 1 | a | +------+------+ 1 row in set (0.00 sec) session1> update slowtech.t1 set name='c' where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 session2> alter table slowtech.t1 add c1 int; ##被阻塞 session3> show processlist; +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+ | 2 | root | localhost | NULL | Sleep | 51 | | NULL | | 3 | root | localhost | NULL | Query | 0 | starting | show processlist | | 4 | root | localhost | NULL | Query | 9 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int | +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+ 3 rows in set (0.00 sec) session3> 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 | slowtech | t1 | SHARED_WRITE | TRANSACTION | GRANTED | 27 | | GLOBAL | NULL | NULL | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | 29 | | SCHEMA | slowtech | NULL | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | 29 | | TABLE | slowtech | t1 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | 29 | | TABLE | slowtech | t1 | EXCLUSIVE | TRANSACTION | PENDING | 29 | | TABLE | performance_schema | metadata_locks | SHARED_READ | TRANSACTION | GRANTED | 28 | +-------------+--------------------+----------------+---------------------+---------------+-------------+-----------------+ 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表。
session3> select * from performance_schema.threads where thread_id in (27,29)\G *************************** 1. row *************************** THREAD_ID: 27 NAME: thread/sql/one_connection TYPE: FOREGROUND PROCESSLIST_ID: 2 PROCESSLIST_USER: root PROCESSLIST_HOST: localhost PROCESSLIST_DB: NULL PROCESSLIST_COMMAND: Sleep PROCESSLIST_TIME: 214 PROCESSLIST_STATE: NULL PROCESSLIST_INFO: NULL PARENT_THREAD_ID: 1 ROLE: NULL INSTRUMENTED: YES HISTORY: YES CONNECTION_TYPE: Socket THREAD_OS_ID: 9800 *************************** 2. row *************************** THREAD_ID: 29 NAME: thread/sql/one_connection TYPE: FOREGROUND PROCESSLIST_ID: 4 PROCESSLIST_USER: root PROCESSLIST_HOST: localhost PROCESSLIST_DB: NULL PROCESSLIST_COMMAND: Query PROCESSLIST_TIME: 172 PROCESSLIST_STATE: Waiting for table metadata lock PROCESSLIST_INFO: alter table slowtech.t1 add c1 int PARENT_THREAD_ID: 1 ROLE: NULL INSTRUMENTED: YES HISTORY: YES CONNECTION_TYPE: Socket THREAD_OS_ID: 9907 2 rows in set (0.00 sec)
将这两张表结合,借鉴sys.innodb_lock _waits的输出,实际上我们也可以直观地呈现MDL的等待关系。
SELECT a.OBJECT_SCHEMA AS locked_schema, a.OBJECT_NAME AS locked_table, "Metadata Lock" AS locked_type, c.PROCESSLIST_ID AS waiting_processlist_id, c.PROCESSLIST_TIME AS waiting_age, c.PROCESSLIST_INFO AS waiting_query, c.PROCESSLIST_STATE AS waiting_state, d.PROCESSLIST_ID AS blocking_processlist_id, d.PROCESSLIST_TIME AS blocking_age, d.PROCESSLIST_INFO AS blocking_query, concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection FROM performance_schema.metadata_locks a JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA AND a.OBJECT_NAME = b.OBJECT_NAME AND a.lock_status = 'PENDING' AND b.lock_status = 'GRANTED' AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID AND a.lock_type = 'EXCLUSIVE' JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID\G *************************** 1. row *************************** locked_schema: slowtech locked_table: t1 locked_type: Metadata Lock waiting_processlist_id: 4 waiting_age: 259 waiting_query: alter table slowtech.t1 add c1 int waiting_state: Waiting for table metadata lock blocking_processlist_id: 2 blocking_age: 301 blocking_query: NULL sql_kill_blocking_connection: KILL 2 1 row in set (0.00 sec)
输出一目了然,DDL操作如果要获得MDL,执行kill 2即可。
官方的sys.schematablelock_waits
实际上,MySQL 5.7在sys库中也集成了类似功能,同样的场景,其输出如下,
mysql> select * from sys.schema_table_lock_waits\G *************************** 1. row *************************** object_schema: slowtech object_name: t1 waiting_thread_id: 29 waiting_pid: 4 waiting_account: root@localhost waiting_lock_type: EXCLUSIVE waiting_lock_duration: TRANSACTION waiting_query: alter table slowtech.t1 add c1 int waiting_query_secs: 446 waiting_query_rows_affected: 0 waiting_query_rows_examined: 0 blocking_thread_id: 27 blocking_pid: 2 blocking_account: root@localhost blocking_lock_type: SHARED_READ blocking_lock_duration: TRANSACTION sql_kill_blocking_query: KILL QUERY 2 sql_kill_blocking_connection: KILL 2 *************************** 2. row *************************** object_schema: slowtech object_name: t1 waiting_thread_id: 29 waiting_pid: 4 waiting_account: root@localhost waiting_lock_type: EXCLUSIVE waiting_lock_duration: TRANSACTION waiting_query: alter table slowtech.t1 add c1 int waiting_query_secs: 446 waiting_query_rows_affected: 0 waiting_query_rows_examined: 0 blocking_thread_id: 29 blocking_pid: 4 blocking_account: root@localhost blocking_lock_type: SHARED_UPGRADABLE blocking_lock_duration: TRANSACTION sql_kill_blocking_query: KILL QUERY 4 sql_kill_blocking_connection: KILL 4 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如下,
SELECT locked_schema, locked_table, locked_type, waiting_processlist_id, waiting_age, waiting_query, waiting_state, blocking_processlist_id, blocking_age, substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query, sql_kill_blocking_connection FROM ( SELECT b.OWNER_THREAD_ID AS granted_thread_id, a.OBJECT_SCHEMA AS locked_schema, a.OBJECT_NAME AS locked_table, "Metadata Lock" AS locked_type, c.PROCESSLIST_ID AS waiting_processlist_id, c.PROCESSLIST_TIME AS waiting_age, c.PROCESSLIST_INFO AS waiting_query, c.PROCESSLIST_STATE AS waiting_state, d.PROCESSLIST_ID AS blocking_processlist_id, d.PROCESSLIST_TIME AS blocking_age, d.PROCESSLIST_INFO AS blocking_query, concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection FROM performance_schema.metadata_locks a JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA AND a.OBJECT_NAME = b.OBJECT_NAME AND a.lock_status = 'PENDING' AND b.lock_status = 'GRANTED' AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID AND a.lock_type = 'EXCLUSIVE' JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID ) t1, ( SELECT thread_id, group_concat( CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text FROM performance_schema.events_statements_history GROUP BY thread_id ) t2 WHERE t1.granted_thread_id = t2.thread_id \G *************************** 1. row *************************** locked_schema: slowtech locked_table: t1 locked_type: Metadata Lock waiting_processlist_id: 4 waiting_age: 294 waiting_query: alter table slowtech.t1 add c1 int waiting_state: Waiting for table metadata lock blocking_processlist_id: 2 blocking_age: 336 blocking_query: delete from slowtech.t1 where id=2;select * from slowtech.t1;update slowtech.t1 set name='c' where id=1 sql_kill_blocking_connection: KILL 2 1 row in set, 1 warning (0.00 sec)
从上面的输出可以看到,blocking_query中包含了会话1中当前事务的所有操作,按执行的先后顺序输出。
需要注意的是,默认情况下,events_statements_history只会保留每个线程最近的10个操作,如果事务中进行的操作较多,实际上也是没办法抓全的。
总结
以上所述是小编给大家介绍的解决MySQL 5.7中如何定位DDL被阻塞的问题,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对网站的支持!
《魔兽世界》大逃杀!60人新游玩模式《强袭风暴》3月21日上线
暴雪近日发布了《魔兽世界》10.2.6 更新内容,新游玩模式《强袭风暴》即将于3月21 日在亚服上线,届时玩家将前往阿拉希高地展开一场 60 人大逃杀对战。
艾泽拉斯的冒险者已经征服了艾泽拉斯的大地及遥远的彼岸。他们在对抗世界上最致命的敌人时展现出过人的手腕,并且成功阻止终结宇宙等级的威胁。当他们在为即将于《魔兽世界》资料片《地心之战》中来袭的萨拉塔斯势力做战斗准备时,他们还需要在熟悉的阿拉希高地面对一个全新的敌人──那就是彼此。在《巨龙崛起》10.2.6 更新的《强袭风暴》中,玩家将会进入一个全新的海盗主题大逃杀式限时活动,其中包含极高的风险和史诗级的奖励。
《强袭风暴》不是普通的战场,作为一个独立于主游戏之外的活动,玩家可以用大逃杀的风格来体验《魔兽世界》,不分职业、不分装备(除了你在赛局中捡到的),光是技巧和战略的强弱之分就能决定出谁才是能坚持到最后的赢家。本次活动将会开放单人和双人模式,玩家在加入海盗主题的预赛大厅区域前,可以从强袭风暴角色画面新增好友。游玩游戏将可以累计名望轨迹,《巨龙崛起》和《魔兽世界:巫妖王之怒 经典版》的玩家都可以获得奖励。
更新动态
- 群星.1999-上华世纪国语总冠军4CD【上华】【WAV+CUE】
- 叶丽仪.1992-仙鹤神针电视剧原声音乐专辑【星传真】【WAV+CUE】
- 陈柏宇.2011-QUINQUENNIUM【SONY】【WAV+CUE】
- 《51首热门当红歌曲 十大顶级发烧女声 3CD》[WAV+CUE][1.6GB]
- 《赵传 深受摇滚迷青睐的台湾音乐人 约定》[WAV+CUE][320MB]
- 《庄心妍 实力唱将 发烧音乐精选集》[WAV+CUE][380MB]
- [音展纪念碟]2006年《TAA台湾第十六届国际HI-ENDHI-FI音响展》2cd[WAV]
- 中国音乐地图之听见西藏山歌牧歌酒歌2020[WAV分轨]
- 王宏伟2017-永远的红飘带[太平洋影音][WAV+CUE]
- 费翔.1997-NOW现在【第一中国】【WAV+CUE】
- 新格合唱团.1981-我唱你和2辑(滚石新格民歌系列)【新格】【WAV+CUE】
- 陈柏宇.2011-QUINQUENNIUM【SONY】【WAV+CUE】
- 雨果唱片-《金唛系列-蔷薇依韵琼瑶》WAV
- 陈百强《凝望(2012华纳LPCD45限量版)[DMI]》[WAV整轨]
- 模拟之声慢刻CD《徐小凤45周年》[德国母带][低速原抓WAV+CUE]