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

This article will explain in detail how to locate the blocking of DDL in MySQL 5.6. the editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

For the location of the blocking problem of DDL, we are mainly based on the performance_schema.metadata_ locks table newly introduced by MySQL 5.7. The proposed positioning method is a kind of "icing on the cake", and it is only applicable to the version starting from MySQL 5.7.

However, MySQL 5.6 still accounts for an absolute majority in actual production. Although MySQL 8.0 has been GA, but in view of the particularity of the database, in dealing with the upgrade of this matter, a considerable number of people still hold a "not active" attitude.

Since there are so many users of MySQL 5.6, is there a way to solve this pain point of MySQL 5.6?

Or the previous test Demo.

Session 1 opened the transaction and performed three operations, but did not commit. At this point, session 2 performed an alter table operation and was blocked.

Session1 > begin

Query OK, 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:

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 | | 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)

Hint, similar code can slide left and right

In fact, there are only two types of operations that cause DDL blocking:

1. Slow query

two。 There are transactions on the table that are not committed

Among them, the first category is easier to locate and can be found through show processlist. The second type is almost impossible to locate, because connections with uncommitted transactions have the same output in show processlist as idle connections.

For example, for the connection with Id 2 below, although the Command is displayed as "Sleep", the transaction is actually uncommitted.

Mysql > show processlist +-+-- + | Id | User | Host | db | Command | Time | State | Info | +-+-- -+-+ | 2 | root | localhost | NULL | Sleep | 77 | | NULL | | 3 | root | localhost | NULL | Query | 0 | starting | | show processlist | | 4 | root | localhost | NULL | Query | 44 | Waiting for table metadata lock | alter table slowtech.t1 add C1 int | +-+-- -+-- + 3 rows in set (0.00 sec)

Therefore, it is reasonable to say that there is a free kill connection (Command is Sleep) on the Internet, but it is too simple and rude to do so, and it is inevitable to kill by mistake.

In fact, since it is a transaction, in information_schema. There must be records in innodb_trx, such as transactions in session 1, which are recorded in the table as follows

Mysql > select * from information_schema.innodb_trx\ gateway * 1. Row * * trx_id: 1050390 trx_state: RUNNING trx_started: 2018-07-17 08:55:32 Trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 4 trx_mysql_thread_id: 2 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 1 trx_lock_structs: 2 trx_lock_memory_bytes: 1136 trx _ rows_locked: 3 trx_rows_modified: 2 trx_concurrency_tickets: 0trx _ isolation_level: REPEATABLE READ trx_unique_checks: 1trx _ foreign_key_checks: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0trx _ adaptive_hash_timeout: 0trx _ is_read_only: 0trx_autocommit_non_locking: 01 row in set (0.00 sec)

Where trx_mysql_thread_id is thread id, combined with performance_schema.threads, you can know which connections have active transactions, thus further reducing the scope of threads that can be kill.

However, in terms of impact, it is not much different from all kill connections whose Command is Sleep. After all, the real idle connection of kill has little impact on the business.

Is there any way to locate the blocking session more accurately?

Yes, the answer still lies in the performance_ schema.events_statements_ chart introduced earlier.

In the analysis of MySQL 5.7in the previous article, we first knew the ID of the thread that caused the blocking, and then looked at the relevant SQL of that thread using the events_statements_ arguments table.

In MySQL 5.6, we don't know the thread ID that caused the blocking, but we can do the opposite, using exhaustive method, first count all the SQL that all threads have executed in the current transaction, and then determine whether these SQL contain target tables.

The specific SQL is as follows

SELECT processlist_id, sql_text FROM (SELECT c.processlist_id, substring_index (sql_text, "transaction_begin;",-1) sql_text FROM information_schema.innodb_trx a, (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) b, performance_schema.threads c WHERE a.trx_mysql_thread_id = c.processlist_id AND b.thread_id = c.thread_id) t WHERE sql_text LIKE'% T1%' +- -+ | processlist_id | sql_text | + -+ | 2 | delete from slowtech.t1 where id=2 Select * from slowtech.t1 Update slowtech.t1 set name='c' where id=1 | +-+- -+ 1 row in set (0.01 sec)

From the point of view of the output, it has indeed achieved the desired results.

It is important to note that in MySQL5.6, events_ statements_history is not enabled by default.

Mysql > SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE'% statements%' +-- +-+ | NAME | ENABLED | +-+-+ | events_statements_current | YES | | events_statements_ History | NO | | events_statements_history_long | NO | | statements_digest | YES | +-- +-+ 4 rows in set (0.00 sec) this is the end of the article on "how to locate the problem of DDL blocking in MySQL 5.6". Hope that the above content can be helpful to you, so that you can learn more knowledge, if you think the article is good, please share it for more people to see.

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.