Detailed explanation of MySQL solution to USE DB congestion

When we encounter a fault, we often think about how to solve the fault instead of thinking about the root cause of the fault? This will only result in us gaining the fish but losing the fishing method. Today, let's share a thinking case caused by a USE DB congestion failure.

Fault description

Today a friend encountered a serious database failure. The failure environment is as follows:

MYSQL 5.6.16

RR Isolation Level

GITD Close

The performance is as follows:

Use db cannot access the database

show table status cannot query table information

According to schema.processlist, there are a lot of Waiting for table metadata locks

In desperation, he killed a lot of threads and found that he still could not recover. Finally, he killed a transaction that was not submitted in time to restore to normal. Only a screenshot as shown below is left:

Fault information extraction

Back to the above picture, we can summarize the statement types as follows:

1. CREATE TABLE A AS SELECT B

Its STATE is sending data

2. DROP TABLE A

Its STATE is Waiting for table metadata lock

3. SELECT * FROM A

Its STATE is Waiting for table metadata lock

4. SHOW TABLE STATUS [like 'A']

Its STATE is Waiting for table metadata lock

Information Analysis

It is not easy to analyze this case because it is a combination of MYSQL-level MDL LOCK and RR-mode innodb row lock, and we need to be sensitive to the STATE of schema.processlist.

It is recommended to read my following articles to learn MDL LOCK:

https://www.jb51.net/article/131383.htm

This section uses the following two methods to verify MDL LOCK:

Method 1 : I added log output to the MDL LOCK source code locking function. If you want to analyze the types of MDL LOCK added to various statements, you can only use this method, because MDL LOCK often flashes by and performance_schema.metadata_locks cannot observe it.

Method 2 : Use performance_schema.metadata_locks 5.7 to observe in a blocked state.

The method to open mdl monitoring in P_S is as follows:

1. Analysis of sending data of table B in CREATE TABLE A AS SELECT B

The status of sending data can actually represent many meanings. From my current understanding, this is a general term for SELECT type statements in the MYSQL upper layer when the INNODB layer and the MYSQL layer interact with each other, so its possible appearance includes:

The amount of data that needs to be accessed is really large and may need to be optimized.

Because the acquisition of row lock at the INNODB layer requires waiting, such as our common SELECT FOR UPDATE.

At the same time, we also need to note that the locking method of SELECT B in RR mode is consistent with that of INSERT...SELECT, so I will not repeat it here:

From his reaction, because he killed a long-standing uncommitted transaction at the end, he was in situation 2. And the entire CREATE TABLE A AS SELECT B statement cannot be obtained because some databases on table B are locked, causing the entire statement to be in the sending data state.

2. Analysis of SHOW TABLE STATUS [like 'A'] Waiting for table metadata lock

This is the most important part of this case. SHOW TABLE STATUS[like 'A'] is blocked. Its STATE is Waiting for table metadata lock. Note that it is table here because there are many types of MDL LOCK. In the article introducing MDL, I mentioned that when desc a table, MDL_SHARED_HIGH_PRIO(SH) will be displayed. In fact, when SHOW TABLE STATUS is executed, MDL_SHARED_HIGH_PRIO(SH) will also be displayed for this table.

Method 1

Method 2

Both methods can observe the existence of MDL_SHARED_HIGH_PRIO(SH) and I simulated a blocking situation.

However, MDL_SHARED_HIGH_PRIO (SH) is a MDL LOCK type with a very high priority, as shown below:

compatibility:

Blocking queue priority:

There is no other possible blocking condition except being blocked by MDL_EXCLUSIVE(X). So this is a very important breakthrough.

III. Analysis of adding MDL LOCK to table A in CREATE TABLE A AS SELECT B

This is something I didn't know before, and it's also the part that takes up the most time in this case. As analyzed in the previous article, there is only one possibility for a statement like SHOW TABLE STATUS [like 'A'] that will only set MDL_SHARED_HIGH_PRIO (SH) MDL LOCK to be blocked on MDL LOCK, and that is that table A has set MDL_EXCLUSIVE (X).

Then I began to suspect that this DDL statement would perform MDL_EXCLUSIVE(X) on table A before the end of the statement. Then I performed an actual test and found that it was indeed the case as follows:

Method 1

Method 2

It is a pity that MDL_EXCLUSIVE(X) is not displayed in performance_schema.metadata_locks, but MDL_SHARED(S) is displayed. We can see in the log I output that an upgrade operation was performed here to upgrade MDL_SHARED(S) to MDL_EXCLUSIVE(X). And from the previous compatibility list, only MDL_EXCLUSIVE(X) will block MDL_SHARED_HIGH_PRIO(SH). So we should be able to confirm that the upgrade operation is indeed performed here, otherwise SHOW TABLE STATUS[like 'A'] will not be blocked.

4. Analysis of SELECT * FROM A Waiting for table metadata lock

You may think that SELECT will not be locked, but that is at the innodb level. At the MYSQL level, MDL_SHARED_READ(SR) will be set as follows:

Method 1

Method 2

It can be seen that MDL_SHARED_READ(SR) does exist and is currently blocked.

Its compatibility is as follows:

Apparently MDL_SHARED_READ(SR) and MDL_SHARED_HIGH_PRIO(SH) are incompatible and need to wait.

5. Analysis of DROP TABLE A Waiting for table metadata lock

This is easy to analyze because table A has an X lock and DROP TABLE A must have an MDL_EXCLUSIVE(X) lock, which is of course incompatible with MDL_EXCLUSIVE(X). as follows:

Method 1

Method 2

Among them, EXCLUSIVE is what we call MDL_EXCLUSIVE(X). It does exist and is currently blocked.

6. Why does use db also get blocked?

If you use the mysql client without the -A option (or no-auto-rehash), you must do at least the following when using USE DB:

1. MDL (SH) lock on each table in db is as follows (call MDL_context::acquire_lock to give the information when the blockage occurs)

Method 1

Method 2

It can be seen that USE DB is indeed blocked due to MDL_SHARED_HIGH_PRIO(SH).

2. Add each table to the table cache and open the table (call open_table_from_share())

Then this situation is exactly the same as the situation where SHOW TABLE STATUS [like 'A'] is blocked, which is also caused by MDL lock incompatibility.

Analysis and sorting

With the previous analysis, we can sort out the causes of this failure as follows:

There is a long-uncommitted DML on table B
The statement will add innodb row lock to certain data in table B at the innodb layer.

Step 1 causes the blockage of CREATE TABLE A AS SELECT B <br /> Because SELECT B in RR mode must lock the data in table B, and since step 1 has locked the data, the wait is triggered, and STATE is sending data.

Step 2 causes other statements to be blocked <br /> Because CRATE TABLE A AS SELECT B will hold MDL_EXCLUSIVE(X) before table A is created, this lock will block all other statements related to table A, including DESC/SHOW TABLE STATUS/USE DB (non-A) statements that only hold MDL_SHARED_HIGH_PRIO(SH)MDL LOCK. STATE is unified as Waiting for table metadata lock.

Simulation test

Test environment:

5.7.14

GITD Close

RR Isolation Level

Use the script:

Here are the steps:

session1