解决MySQL 5.7中定位DDL被阻塞的问题- Starterknow

在上篇文章《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’;</pre>

</div>

<p><strong>永久生效</strong></p>

<p>在配置文件中设置</p>

<div class=”jb51code”>

<pre class=”brush:sql;”>

[mysqld]

performance-schema-instrument=’wait/lock/metadata/sql/mdl=ON’ </pre>

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.OВJECT_SCHEMA AS locked_schema,

a.OВJECT_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.OВJECT_SCHEMA = b.OВJECT_SCHEMA

AND a.OВJECT_NAME = b.OВJECT_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.OВJECT_SCHEMA AS locked_schema,

a.OВJECT_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.OВJECT_SCHEMA = b.OВJECT_SCHEMA

  AND a.OВJECT_NAME = b.OВJECT_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被阻塞的问题，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对共生网络网站的支持！

原创文章，作者：starterknow，如若转载，请注明出处：https://www.starterknow.com/115072.html

解决MySQL 5.7中定位DDL被阻塞的问题

相关文章：

相关推荐

请登录

联系我们