Solve the problem of blocking positioning DDL in MySQL 5.7

In the previous article "MySQL table structure changes, you must know the Metadata Lock", we introduced the background and basic concepts of the introduction of MDL, and learned what MDL is from the "Tao" level. Next, let’s look at how to identify MDL-related issues from a “technical” perspective.

In MySQL 5.7, a new table performance_schema.metadata_locks is introduced for MDL. This table can display relevant information of MDL, including its object, type and holding wait status.

Enable MDL instrumentation

However, the relevant instrument is not enabled (MySQL 8.0 is enabled by default). It can be enabled in the following two ways:

Temporary effect

performance_schema.setup_instrume nts表is modified, but after the instance is restarted, it will be restored to the default value.

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

Permanent

Set in the configuration file

[mysqld]
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

Test scenario

The following is a simple demo to show how to locate the blocking problem of DDL operations in MySQL 5.7.

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; ##Blocked 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)

Here, focus on lock_status, "PENDING" means the thread is waiting for MDL, and "GRANTED" means the thread holds MDL.

How to find the session that is causing the blockage

Combined with owner_thread_id, we can see that thread 29 is waiting for the MDL of thread 27. At this time, thread 52 can be killed.

But it should be noted that owner_thread_id only gives the thread ID, not the ID in show processlist. If you want to find the processlist id corresponding to a thread, you need to query the performance_schema.threads table.

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)

By combining these two tables and referring to the output of sys.innodb_lock_waits, we can actually intuitively present the waiting relationship of 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)

The output is clear at a glance. If you want to obtain MDL for DDL operations, execute kill 2.

Official sys.schematablelock_waits

In fact, MySQL 5.7 also integrates similar functions in the sys library. In the same scenario, the output is as follows:

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)

Specifically analyze the official output,

There is only one alter table operation, but two records are generated, and the kill objects of the two records are different. If you are not familiar with the table structure and do not read the record content carefully, you will inevitably kill the wrong object.

Moreover, if N queries are blocked by DDL operations, N*2 records will be generated. When there are many blocking operations, these N*2 records are completely noise.

In the previous SQL, no matter how many operations were blocked, an alter table operation would only output one record.

How to view the operations that have been performed by the blocking session

But the above SQL also has a regret, its blocking_query is NULL, and in session 1, it has clearly executed three SQLs.

This is related to performance_schema.threads (similar to show processlist), which only outputs the SQL that is currently running. It is actually impossible to see the SQL that has already been executed.

But online, killing is an operation that requires caution. After all, it is difficult to know whether the killing is a business-critical operation. Or is it a batch update operation? So, is there any way to capture the operations before the transaction?

The answer is yes.

This is the table in the Performance Schema that records Statement Events (operation events), including

events_statements_current, events_statements_history, events_statements_history_long, prepared_statements_instances.

The first three are commonly used.

The table structures of the three are exactly the same. Among them, events_statements_history also includes the operations of events_statements_current, so we will use events_statements_history here.

The final SQL is as follows,

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)

From the above output, we can see that blocking_query contains all operations of the current transaction in session 1, which are output in the order in which they are executed.

It should be noted that, by default, events_statements_history will only retain the 10 most recent operations of each thread. If there are many operations in a transaction, it is actually impossible to capture all of them.

Summarize

The above is what I have introduced to you about how to locate the problem of DDL being blocked in MySQL 5.7. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!

You may also be interested in:

How to quickly add columns in MySQL 8.0
Detailed explanation of the use of MySQL Online DDL
How to solve the synchronization delay caused by MySQL DDL
Detailed explanation of MySQL 8.0 atomic DDL syntax
MySQL online DDL tool gh-ost principle analysis
Use of MySQL DDL statements
Summary of common Mysql DDL operations
Analysis of the new features of MySQL 8.0 - transactional data dictionary and atomic DDL
Basic statements of MySQL data definition language DDL
MySQL 8.0 DDL atomicity feature and implementation principle
Summary of using MySQL online DDL gh-ost
MySQL 8.0 new features: support for atomic DDL statements
MySQL exposes Riddle vulnerability that can cause username and password leakage
Summary of MySQL 8.0 Online DDL Quick Column Addition

<<: Detailed explanation of how Node.js handles ES6 modules

>>: Linux common text processing commands and vim text editor

Solve the problem of blocking positioning DDL in MySQL 5.7

Vue implements accordion effect

Learn how to deploy and start multiple tomcats and migrate projects in one article

HTML checkbox Click the description text to select/uncheck the state

HTML exceeds the text line interception implementation principle and code

Detailed explanation of as, question mark and exclamation mark in Typescript

Simple Implementation of HTML to Create Personal Resume

Two box models in web pages (W3C box model, IE box model)

Detailed process of installing Docker, creating images, loading and running NodeJS programs

Detailed Introduction to Nginx Installation and Configuration Rules

MySql forgotten password modification method is suitable for versions 5.7 and above

Recommend

How to use CSS attribute value regular matching selector (tips)

Detailed explanation of crontab scheduled execution command under Linux

mysql show simple operation example

How to deploy Node.js with Docker

Implementing a web calculator with native JavaScript

WeChat applet calculator example

Sharing some details about MySQL indexes

Graphic tutorial on installing Ubuntu 18.04 on VMware 15 virtual machine

js to implement collision detection

Linux debugging tools that developers and operators must look at [Recommended]

How to use MySQL binlog to restore accidentally deleted databases

MySQL 5.7.20 zip installation tutorial

MySQL sorting principles and case analysis

Linux series of commonly used operation and maintenance commands (summary)

How to use the flash plug-in to call the PC's camera and embed it into the TML page