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

This article mainly explains "USE DB congestion fault analysis in Mysql". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "USE DB congestion fault analysis in Mysql".

1. Fault information extraction

The sentence types are summarized as follows:

1 、 CREATE TABLE An 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.

II. Information analysis

It is not easy to analyze this case list because it is a combination of MYSQL layer MDL LOCK and RR mode innodb row lock, and we have to be sensitive to schema.processlist 's STATE.

In this section, MDL LOCK is validated in the following two ways:

In mode 1, the author adds log output to the lock function of MDL LOCK source code, but if you want to analyze various types of statements plus MDL LOCK, you can only use this way, because MDL LOCK locking is often flash, and performance_schema.metadata_locks has no way to observe it.

Mode 2 output of performance_schema.metadata_locks in the case of congestion

Open as follows:

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME =' global_instrumentation'

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

Select * from performance_schema.metadata_locks\ G

1. The analysis of table B sending data by CREATE TABLE An AS SELECT B.

The state of sending data can actually mean a lot. From my current understanding of it, it is a general term for the upper layer of MYSQL to exchange data between the INNODB layer and the MySQL layer when this kind of statement is used for SELECT type statements, so it may include:

It is really necessary to access a large amount of data, which may need to be optimized.

Because the acquisition of row lock in the INNODB layer needs to wait, such as our common SELECT FOR UPDATE

At the same time, we also need to note that in RR mode, the locking method of SELECT B is consistent with that of INSERT...SELECT.

Http://blog.itpub.net/7728585/viewspace-2146183/

From his reaction to the situation because he killed a long-term unsubmitted thing in the end, so he because it is case 2. And the whole CREATE TABLE An AS SELECT B statement can not be obtained because some databases on the B table are locked, so the whole statement is in the sending data state.

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

This is the most important link in this case, SHOW TABLE STATUS [like'A'] is blocked and its STATE is Waiting for table metadata lock and notice here is table because there are many MDL LOCK types. In the article I introduced by MDL, I mentioned that when desc has a table, it will MDL_SHARED_HIGH_PRIO (SH). In fact, it will also be MDL_SHARED_HIGH_PRIO (SH) when it comes to SHOW TABLE STATUS.

Method 1:

Mysql > SHOW TABLE STATUS like 'a'\ G2017-11-10T03:01:48.142334Z 6 [Note] (acquire_lock) * * THIS MDL LOCK acquire WAIT (MDL_LOCK WAIT QUE)! * * 2017-11-10T03:01:48.142381Z 6 [Note] (> MDL PRINT) Thread id is 6: 2017-11-10T03:01:48.142396Z 6 [Note] (- > MDL PRINT) DB_name is:test 2017-11-10T03:01:48.142409Z 6 [Note] (--> MDL) PRINT) OBJ_name is:a 2017-11-10T03:01:48.142421Z 6 [Note] (--> MDL PRINT) Namespace is:TABLE 2017-11-10T03:01:48.142434Z 6 [Note] (- > MDL PRINT) Mdl type is:MDL_SHARED_HIGH_PRIO (SH) 2017-11-10T03:01:48.142447Z 6 [Note] (- > MDL PRINT) Mdl duration is:MDL_TRANSACTION

Mode 2:

* * 7. Row * * OBJECT_TYPE: TABLE OBJECT_SCHEMA: test OBJECT_NAME: aOBJECT_INSTANCE_BEGIN: 140733864665152 LOCK_TYPE: SHARED_HIGH_PRIO LOCK_DURATION: TRANSACTION LOCK_STATUS: PENDING SOURCE: sql_base.cc:2821 OWNER_THREAD_ID: 38 OWNER_EVENT_ID: 1695

The existence of MDL_SHARED_HIGH_PRIO (SH) can be observed in both ways and what I simulate is in a clogged situation.

But MDL_SHARED_HIGH_PRIO (SH) is a very high priority MDL LOCK type that looks like this:

Compatibility:

Request | Granted requests for lock | type | S SH SR SW SWLP SU SRO SNW SNRW X |-+-+ SH | +-|

Blocking queue priority:

Request | Pending requests for lock | type | S SH SR SW SU SNW SNRW X |-+-- + SH | + + |

There is no other possibility for it to be blocked except by MDL_EXCLUSIVE (X). So this is a very important breakthrough.

3. The analysis of adding MDL LOCK to table A by CREATE TABLE An AS SELECT B.

This is also something I didn't know before, and it takes the most time in this column. I have analyzed earlier that there is only one possibility for SHOW TABLE STATUS [like'A'], which only reads MDL_SHARED_HIGH_PRIO (SH) MDL LOCK, to block on MDL LOCK, and that is the MDL_EXCLUSIVE (X) on table A. So I'll start.

It is suspected that this DDL statement will MDL_EXCLUSIVE (X) on table A before the end of the statement, and then the actual test is as follows:

Method 1:

2017-11-10T05:38:16.824713Z 4 [Note] (acquire_lock) THIS MDL LOCK acquire okages 2017-11-10T05:38:16.824727Z 4 [Note] (> MDL PRINT) Thread id is 4: 2017-11-10T05:38:16.824739Z 4 [Note] (- > MDL PRINT) DB_name is:test 2017-11-10T05:38:16.824752Z 4 [Note] (--> MDL PRINT) OBJ_name is:a 2017-11-10T05:38:16.824764Z 4 [ Note] (- > MDL PRINT) Namespace is:TABLE 2017-11-10T05:38:16.824776Z 4 [Note] (- > MDL PRINT) Fast path is: (y) 2017-11-10T05:38:16.824788Z 4 [Note] (- > MDL PRINT) Mdl type is:MDL_SHARED (S) 2017-11-10T05:38:16.824799Z 4 [Note] (- > MDL PRINT) Mdl duration is:MDL_TRANSACTION 2017-11-10T05:38 : 825286Z 4 [Note] (upgrade_shared_lock) THIS MDL LOCK upgrade TO2017-11-10T05:38:16.825312Z 4 [Note] (> MDL PRINT) Thread id is 4: 2017-11-10T05:38:16.825332Z 4 [Note] (- > MDL PRINT) DB_name is:test 2017-11-10T05:38:16.825345Z 4 [Note] (- > MDL PRINT) OBJ_name is:a 2017-11-10T05:38:16.825357Z 4 [Note] ( > MDL PRINT) Namespace is:TABLE 2017-11-10T05:38:16.825369Z 4 [Note] (- > MDL PRINT) Mdl type is:MDL_EXCLUSIVE (X) 2017-11-10T05:38:16.825381Z 4 [Note] (- > MDL PRINT) Mdl duration is:MDL_TRANSACTION

Mode 2:

* * 1. Row * * OBJECT_TYPE: TABLE OBJECT_SCHEMA: test OBJECT_NAME: aOBJECT_INSTANCE_BEGIN: 140733998842016 LOCK_TYPE: SHARED LOCK_DURATION: TRANSACTION LOCK_STATUS: GRANTED SOURCE: sql_parse.cc:6314 OWNER_THREAD_ID: 36 OWNER_EVENT_ID: 1553

It's a pity that MDL_EXCLUSIVE (X) is not shown in performance_schema.metadata_locks, but as MDL_SHARED (S), but we can see in my output log that there is an upgrade operation here to upgrade MDL_SHARED (S) to MDL_EXCLUSIVE (X). And judging from the previous compatibility list, only MDL_EXCLUSIVE (X) blocks MDL_SHARED_HIGH_PRIO (SH). So we should be able to confirm that an upgrade has been done 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, and the MDL_SHARED_READ (SR) at the MYSQL layer is as follows:

Method 1:

Select * from a 2017-11-10T03:31:31.209772Z 6 [Note] (acquire_lock) THIS MDL LOCK acquire WAIT (MDL_LOCK WAIT QUE)! 2017-11-10T03:31:31.209824Z 6 [Note] (> MDL PRINT) Thread id is 6: 2017-11-10T03:31:31.209851Z 6 [Note] (- > MDL PRINT) DB_name is:test 2017-11-10T03:31:31.209870Z 6 [Note] (--> MDL PRINT) OBJ_name is:a 2017-11-10T03: 31Namespace is:TABLE 31.209885Z 6 [Note] (--> MDL PRINT) Namespace is:TABLE 2017-11-10T03:31:31.209965Z 6 [Note] (- > MDL PRINT) Mdl type is:MDL_SHARED_READ (SR) 2017-11-10T03:31:31.209985Z 6 [Note] (- > MDL PRINT) Mdl duration is:MDL_TRANSACTION

Mode 2:

OBJECT_TYPE: TABLE OBJECT_SCHEMA: test OBJECT_NAME: aOBJECT_INSTANCE_BEGIN: 140733864625136 LOCK_TYPE: SHARED_READ LOCK_DURATION: TRANSACTION LOCK_STATUS: PENDING SOURCE: sql_parse.cc:6314 OWNER_THREAD_ID: 38 OWNER_EVENT_ID: 1764

You can see that MDL_SHARED_READ (SR) does exist and is currently blocked.

Its compatibility is as follows:

Request | Granted requests for lock | type | S SH SR SW SWLP SU SRO SNW SNRW X |-+-+ SR | + +-- |

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 easy to analyze because table A has an X lock and DROP TABLE A must have a MDL_EXCLUSIVE (X) lock which is of course incompatible with MDL_EXCLUSIVE (X). As follows:

Method 1:

Drop table a 2017-11-09T10:58:28.673015Z 3 [Note] (acquire_lock) THIS MDL LOCK acquire okages 2017-11-09T10:58:28.673030Z 3 [Note] (> MDL PRINT) Thread id is 3: 2017-11-09T10:58:28.673042Z 3 [Note] (- > MDL PRINT) DB_name is:test 2017-11-09T10:58:28.673054Z 3 [Note] (--> MDL PRINT) OBJ_name is:t10 2017-11-09T10:58:28.673067Z 3 [ Note] (--> MDL PRINT) Namespace is:TABLE 2017-11-09T10:58:28.673094Z 3 [Note] (- > MDL PRINT) Mdl type is:MDL_EXCLUSIVE (X) 2017-11-09T10:58:28.673109Z 3 [Note] (- > MDL PRINT) Mdl duration is:MDL_TRANSACTION

Mode 2:

OBJECT_TYPE: TABLE OBJECT_SCHEMA: test OBJECT_NAME: aOBJECT_INSTANCE_BEGIN: 140733864625472 LOCK_TYPE: EXCLUSIVE LOCK_DURATION: TRANSACTION LOCK_STATUS: PENDING SOURCE: sql_parse.cc:6314 OWNER_THREAD_ID: 38 OWNER_EVENT_ID: 1832

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

6. Why is use db blocked?

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

1. The MDL (SH) lock on each table under db is as follows (call MDL_context::acquire_lock to give the information of congestion):

Method 1:

Use test2017-11-10T03:46:50.223628Z 5 [Note] (acquire_lock) THIS MDL LOCK acquire WAIT (MDL_LOCK WAIT QUE)! 2017-11-10T03:46:50.223666Z 5 [Note] (> MDL PRINT) Thread id is 5: 2017-11-10T03:46:50.223696Z 5 [Note] (- > MDL PRINT) DB_name is:test 2017-11-10T03:46:50.223714Z 5 [Note] (- > MDL PRINT) OBJ_name is:a 2017-11-10T03 46Namespace is:TABLE 50.223725Z 5 [Note] (--> MDL PRINT) Namespace is:TABLE 2017-11-10T03:46:50.223735Z 5 [Note] (- > MDL PRINT) Mdl type is:MDL_SHARED_HIGH_PRIO (SH) 2017-11-10T03:46:50.223755Z 5 [Note] (- > MDL PRINT) Mdl duration is:MDL_TRANSACTION

Mode 2:

* * 7. Row * * OBJECT_TYPE: TABLE OBJECT_SCHEMA: test OBJECT_NAME: aOBJECT_INSTANCE_BEGIN: 140733797429008 LOCK_TYPE: SHARED_HIGH_PRIO LOCK_DURATION: TRANSACTION LOCK_STATUS: PENDING SOURCE: sql_base.cc:2821 OWNER_THREAD_ID: 37 OWNER_EVENT_ID: 187

You can see that USE DB is indeed blocked because of MDL_SHARED_HIGH_PRIO (SH).

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

Then this situation is exactly the same as when SHOW TABLE STATUS [like 'A'] is blocked, and it is also due to MDL lock incompatibility.

Third, analyze and sort out

With the previous analysis, we can sort out the reasons for this failure as follows:

1. There is a DML that has not been submitted on form B for a long time.

The statement adds innodb row lock to some of the data in Table B at the innodb layer.

2. The blockage of CREATE TABLE An AS SELECT B is caused by step 1.

Because SELECT B must lock the data satisfied on table B in RR mode, wait is triggered because step 1 has been locked, and STATE is sending data.

3. Other statements are blocked by step 2.

Because CRATE TABLE An AS SELECT B MDL_EXCLUSIVE (X) before table An is created, this lock blocks all other statements about table A, including DESC/SHOW TABLE STATUS/USE DB (non-A), which is only on MDL_SHARED_HIGH_PRIO (SH) MDL LOCK. STATE is unified as Waiting for table metadata lock.

Fourth, simulation test

Test environment:

5.7.14

GITD shuts down

RR isolation level

Use scripts:

Create table b (id int); insert into b values (1); set global innodb_lock_wait_timeout=1000;UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME =' global_instrumentation';UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME =' wait/lock/metadata/sql/mdl' Select * from performance_schema.metadata_locks\ G (please reconnect to let the parameters take effect) Thank you for reading. The above is the content of "USE DB congestion Fault Analysis in Mysql". After the study of this article, I believe you have a deeper understanding of the problem of USE DB congestion fault analysis in Mysql, and the specific usage needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.