How Mysql solves use db congestion and how mysqlusedb

How Mysql solves use db congestion and how mysqlusedb

In case of a fault, we often think about how to solve the fault, rather than thinking about the cause of the fault from the root of the fault? In this case, we can only get the fish and lose the fish. Today, we will share a Case Study About the use db congestion failure.

Fault description

Today, a friend encounters a serious fault in the database. The fault environment is as follows:

MYSQL 5.6.16

RR isolation level

Disable GITD

The performance is as follows:

Use db cannot access the database

Show table status cannot query table information

Schema. processlist: There are a lot of Waiting for table metadata locks.

In a hurry, he killed a lot of threads and found that he still could not recover. Finally, he killed a thing that had not been submitted in time before returning to normal. Only one of the following is left:

Fault Information Extraction

Back, We can summarize the statement types as follows:

1,CREATE TABLE A AS SELECT B

The STATE is sending data.

2,DROP TABLE

Its STATE is Waiting for table metadata lock.

3,SELECT * FROM

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 column because it is a comprehensive case column of MYSQL-layer mdl lock and RR-mode innodb row lock, and we need to apply the schema. processlist STATE is sensitive.

We recommend that you read the following articles to learn about mdl lock:

Http://www.bkjia.com/article/131383.htm

The following two methods are used for mdl lock verification in this section:

Method 1: The author adds log output to the mdl lock source code LOCK function. If you want to analyze the types of statements and mdl lock, you can only use this method, because the mdl lock is often a flash, performance_schema.metadata_locks cannot be observed.

Method 2: Use performance_schema.metadata_locks of version 5.7 when it is blocked.

The following describes how to enable mdl monitoring in P_S:

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

The status of sending data actually represents many meanings, this is a general term for SQL statements of the SELECT type at the upper layer of MYSQL when data is exchanged between the INNODB layer and the MYSQL layer. Therefore, it may include:

It is true that the access data volume is extremely large and may need to be optimized.

Because the INNODB layer needs to wait to get the row lock, FOR example, our common select for update.

In the RR mode, we also need to note that the locking method of select B is the same as that of INSERT... SELECT:

From his reaction, because he finally killed a long uncommitted thing, he was in Case 2. The entire create table a as select B statement is in the sending data state because some databases on table B are locked and cannot be obtained.

Ii. Analysis on show table status [like 'a'] Waiting for table metadata lock

This is the most important part in this case. The show table status [like 'a'] is blocked and its STATE is Waiting for table metadata lock. Note that table is divided into many types because mdl lock. The article I introduced in MDL mentioned that MDL_SHARED_HIGH_PRIO (SH) is used when desc is a TABLE. In fact, MDL_SHARED_HIGH_PRIO (SH) is also used when SHOW TABLE STATUS is used ).

Method 1

Method 2

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

However, MDL_SHARED_HIGH_PRIO (SH) is an mdl lock type with a very high priority, which is shown as follows:

Compatibility:

Blocking queue priority:

The blocking condition except MDL_EXCLUSIVE (X) has no other possibility. This is a very important breakthrough.

Iii. Analysis on the addition of mdl lock to table a by CREATE TABLE A AS SELECT B

This is what I did not know before. It is also the most time-consuming place in this case. The previous article has analyzed how to make SHOW TABLE STATUS [like 'a'] Only use MDL_SHARED_HIGH_PRIO (SH) mdl lock statements block mdl lock. There is only one possibility that the MDL_EXCLUSIVE (X) Table A is blocked ).

Then I began to suspect that the DDL statement would perform the actual test on the MDL_EXCLUSIVE (X) of table A before the end of the statement. As expected, the actual test is as follows:

Method 1

Method 2

Unfortunately, MDL_EXCLUSIVE (X) is not displayed in performance_schema.metadata_locks, but MDL_SHARED (S) is displayed) we can see in the output log that the upgrade operation is performed here to upgrade MDL_SHARED (S) to MDL_EXCLUSIVE (X ). From the compatibility list above, only MDL_EXCLUSIVE (X) will block MDL_SHARED_HIGH_PRIO (SH ). Therefore, we should be able to confirm that the upgrade is done here, otherwise the 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 won't be locked, but it is at the innodb level. At the MYSQL layer, MDL_SHARED_READ (SR) is as follows:

Method 1

Method 2

It can be seen that MDL_SHARED_READ (SR) exists and is currently in the blocking status.

Its compatibility is as follows:

Obviously, 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 A good analysis because table a has the X lock, and drop table a must have the MDL_EXCLUSIVE (X) Lock. Of course, it is not compatible with MDL_EXCLUSIVE (X. As follows:

Method 1

Method 2

EXCLUSIVE is what we call MDL_EXCLUSIVE (X). It is indeed blocked.

6. Why is the use of db congested?

If the mysql client does not USE the-A option (or no-auto-rehash), at least do the following when using the database:

1. lock the MDL (SH) lock on each table in the database as follows (call MDL_context: acquire_lock to provide the blocking information here)

Method 1

Method 2

We can see that the use db is indeed blocked by MDL_SHARED_HIGH_PRIO (SH.

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

This situation is exactly the same as the case where show table status [like 'a'] is blocked. It is also caused by incompatibility of the MDL lock.

Analysis

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

There is a DML that has not been submitted for a long time in table B.
The statement adds innodb row lock to some data in Table B at the innodb layer.

The create table a as select B blockage is caused by step 1.
In RR mode, select B is bound to lock the data on Table B. Because step 1 has been locked, the wait is triggered and the STATE is sending data.

Other statements are blocked due to step 2.
Because crate table a as select B will mount MDL_EXCLUSIVE (X) before TABLE A is created, this lock will block all other statements about TABLE, this includes DESC/show table status/use db (non-A) statements that only USE MDL_SHARED_HIGH_PRIO (SH) mdl lock. The STATE is always Waiting for table metadata lock.

Simulated Test

Test environment:

5.7.14

Disable GITD

RR isolation level

Script:

The procedure is as follows:

Session1Session2Session3Session4 ------ use test; --- use test; begin; delete from B; ------------ use test; create table a asselect * from B; (due to innodb row lock blockage in table B) ------------ show table status like 'a'; (because mdl lock of table a is blocked) ------------ use test (because mdl lock of table a is blocked)

Finally, we can see the following waiting status:

In this way, we can simulate the final state perfectly. If we kill things in session1, we will naturally unlock them all. Let's take a look at the output in performance_schema.metadata_locks:

We can see the above output, but note that LOCK_TYPE: SHARED cannot block LOCK_TYPE: SHARED_HIGH_PRIO (refer to the appendix or my previous mdl lock analysis article) as shown in the above analysis, the upgrade operation is actually upgraded to MDL_EXCLUSIVE (X ).

Summary

In RC mode, although table B in create table a select B does not have any innodb row lock, if table B is very large, TABLE A is protected by MDL_EXCLUSIVE (X, therefore, the use db \ show table status wait is also triggered.

If you open GTID, you cannot use statements such as create table a select B.

For systems mixed with DML and DDL, pay attention to concurrency, just as in this example, if you notice high concurrency, you can try to avoid it.

This case column once again illustrates that long-term uncommitted transactions may lead to tragedies, so we recommend that you monitor transactions that have not ended in more than N seconds.

Appendix

MDL LOCK TYPE

Compatibility Matrix

Waiting queue priority Matrix