Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to solve the problem of positioning DDL blocking in MySQL 5.7". It is easy to understand and clear. I hope it can help you solve your doubts. Let the editor lead you to study and learn this article" how to solve the problem of locating DDL blocking in MySQL 5.7".

In MySQL 5.7A new table performance_schema.metadata_locks is introduced for MDL, which can display the relevant information of MDL, including its action object, type and holding wait.

Open the instrument of MDL

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

Take effect temporarily

Modify the performance_schema.setup_instrume nts table, but when the instance is restarted, it will return to the default value.

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

Take effect permanently

Set in the configuration file

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

Test scenario

In combination with a simple Demo, let's take a look at 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: 0session2 > 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 | GRANTED | 29 | TABLE | slowtech | T1 | 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" represents that the thread is waiting for MDL, while "GRANTED" represents that the thread holds the MDL.

How to find the session that is causing blocking

Combined with owner_thread_id, it can be reached that thread 29 is waiting for the MDL of thread 27, and at this time, thread 52 can be lost by kill.

It is important to note, however, that owner_thread_id gives only thread ID, not ID in show processlist. If you want to find the processlist id for the thread, you need to query the performance_ schemas. Threads table.

Session3 > select * from performance_schema.threads where thread_id in (27 row 29)\ Gmail * 1. Row * * THREAD_ID: 27 NAME: thread/sql/one_connection TYPE: FOREGROUND PROCESSLIST_ID: 2 PROCESSLIST_USER: root PROCESSLIST_HOST: localhost PROCESSLIST_DB: NULLPROCESSLIST_COMMAND: Sleep PROCESSLIST_TIME: 214PROCESSLIST_STATE: NULL PROCESSLIST_INFO: NULL PARENT_THREAD_ID: 1 ROLE: NULL INSTRUMENTED: YES HISTORY: YES CONNECTION_TYPE: Socket THREAD_OS_ID: 9800 row * * THREAD_ID: 29 NAME: thread/sql/one_connection TYPE : FOREGROUND PROCESSLIST_ID: 4 PROCESSLIST_USER: root PROCESSLIST_HOST: localhost PROCESSLIST_DB: NULLPROCESSLIST_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: 99072 rows in set (0.00 sec)

By combining these two tables and drawing on the output of sys.innodb_lock _ waits, we can actually visually present the wait 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_connectionFROM performance_schema.metadata_locks aJOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMAAND a.OBJECT_NAME = b.OBJECT_NAMEAND a.lock_status = 'PENDING'AND b.lock_status =' GRANTED'AND a.OWNER_THREAD_ID b.OWNER_THREAD_IDAND a.lock_type = 'EXCLUSIVE'JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_IDJOIN performance_schema .threads d ON b.OWNER_THREAD_ID = d.THREAD_ID\ gateway * 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: NULLsql_kill_blocking_connection: KILL 21 row in set (0.00 sec)

The output is clear at a glance, and if the DDL operation wants to get MDL, execute kill 2.

Official sys.schematablelock_waits

In fact, MySQL 5.7 integrates similar functionality into the sys library, and the output for the same scenario is as follows

Mysql > select * from sys.schema_table_lock_waits\ gateway * 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 2sql_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 4sql_kill_blocking_connection: KILL 42 rows in set (0.00 sec)

Analyze the official output in detail.

There is only one alter table operation, but two records are produced, and the kill objects of the two records are not the same. If you are not familiar with the table structure and do not look at the record content carefully, it is inevitable to kill the wrong object.

Not only that, if N queries are blocked by DDL operations, 2 records will be generated. In the case of a large number of blocking operations, these 2 records are completely noisy.

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

How to view actions that have been performed by a blocking session

But the above SQL also has some regrets, its blocking_query is NULL, and in session 1, it has obviously executed three SQL.

This is related to performance_schema.threads (similar to show processlist), which only outputs the currently running SQL, which is virtually impossible to see for those that have already been executed.

But online, kill is a cautious operation. After all, it's hard to know if kill is a business-critical operation. Or is it a batch update operation? So, is there any way to catch the operation before the transaction?

The answer, yes.

That is, a table in Performance Schema that records Statement Event (action events), including

Events_statements_current,events_statements_history,events_statements_history_long,prepared_statements_instances .

The first three are commonly used.

The table structure of the three is exactly the same, and events_statements_history contains the operation of events_statements_current, so we will use events_statements_history here.

The ultimate 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_connectionFROM (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) t2WHERE t1.granted_thread_id = t2.thread_id\ gateway * 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=1sql_kill_blocking_connection: KILL 21 row in set, 1 warning (0.00 sec)

As you can see from the output above, blocking_query contains all the operations of the current transaction in session 1, outputting them in the order in which they were executed.

It is important to note that by default, events_statements_history retains only the last 10 operations per thread, and it is virtually impossible to capture all of them if there are more operations taking place in the transaction.

The above is all the contents of this article entitled "how to solve the problem of locating DDL blocking in MySQL 5.7.Thank you for reading!" I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.