MySQL executes DDL statement what if hang resides? 02/24 Update SLTechnology News&Howtos

MySQL executes DDL statement what if hang resides?

2025-02-24 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

MySQL executes DDL statement what if hang resides? Don't panic, light a cigarette first and listen to me! Two days ago, shortly after 7: 00 in the morning, I grabbed my phone and suddenly saw a case of hang living in a DDL sentence in the circle. I read at the end of the article and found that there were some questions left in the article (of course, the article was in July, and now maybe the author has solved these questions), so a grunt got out of bed and operated according to the reproduction method in the article (see the end of the article). According to their own ideas to solve the problem, and now sort out the whole process to share with you. Environment database version: MySQL 5.7.27 data information sysbench simulates two 1000W tables operating system version: CentOS Linux release 7.3.1611 (Core) server information CPU:Intel (R) Xeon (R) CPU E5-4627 v2 @ 3.30GHz * 2 memory: 248g disk: 1.6T LSI flash card network card: 10 Gigabit network card phenomenon when we find a fault problem, the first thing we need to do is to confirm the phenomenon first. That is, you have to see with your own eyes what the fault looks like, instead of going straight up or random troubleshooting. Especially when the fault is feedback from others, we must first confirm the performance of the fault phenomenon and its authenticity (most of the time, the problem from others is not a problem at all, but his own posture is incorrect). In this article, the fault phenomenon mentioned is discovered by the DBA side when it executes the DDL statement to modify the field length (of course, here is the simulated DBA side operation). Then, according to the above guidelines, let's first open another session to see what happens when we execute the DDL statement. # using the show processlist statement to view the session state information, it is found that the state column value of the DDL statement is Waiting for table metadata lock, indicating that you are waiting for the MDL metadata lock. According to the online ddl feature in MySQL 5.7and later, this statement should be executed immediately (it only modifies the number of elements, because it only modifies the field length, not the other properties of the field). Therefore, the fault phenomenon confirms that admin@localhost: (none) 11:48:22 > show processlist +-+-- + | Id | User | Host | db | Command | Time | State | Info | +-+-- + -+ | 27 | admin | localhost | sbtest | Sleep | 123 | NULL | 28 | admin | localhost | sbtest | Query | Waiting for table metadata lock | alter table sbtest1 modify column `pad` varchar (70) COLLATE utf8_bin NOT NULL DEFAULT''| 29 | admin | localhost | NULL | Query | 0 | starting | show processlist | 30 | admin | localhost | NULL | Sleep | 93 | NULL | + -+ 4 rows in set (0.00 sec) friendly tip: confirm the location of the fault phenomenon and make no mistake. Where the fault is found, first of all, where the fault is identified. As a counterexample, I once met a classmate who was in the architecture level of "application-- > LVS-- > sub-library middleware-- > read-write separation middleware-- > database master-slave cluster". The application side reported that the database connection was very slow, and the student logged in directly to the database to confirm the phenomenon. Obviously, he made a mistake in confirming the fault phenomenon. In this counterexample, the location of the fault phenomenon should be confirmed by the location of the feedback fault phenomenon on the application side (first try to connect using LVS's VIP), and confirm the fault phenomenon layer by layer. The reason why the DDL statement hang resides is because you are waiting for the MDL metadata lock, but you don't know who holds the MDL metadata lock. Next, around this phenomenon, speculate on some of the possible causes of the problem, and what causes the problem? We can follow the following ideas to eliminate the excessive host load of the server (CPU, memory, disk throughput and IOPS, network card bandwidth). Are there any other programs that occupy the resources of the database process?

* you might say that the phenomenon here is obviously due to waiting for the MDL lock, which has nothing to do with the server load. Someone must have added the lock and not released it. But I would like to remind you, is this phenomenon really a simple question? Could it be caused by a chain reaction? Although waiting for the MDL lock is the reason why the DDL statement is blocked, it may also be due to the high load on the server and the excessive load on the resulting database process.

* number of active sessions in the database and their status

* there are other sessions in the QPS/TPS of the database that are executing DML statements, or transactions are not committed in time after some DML statements are executed, or some other session is also executing a DDL statement to modify the table structure information of the sbtest1 table at the same time, and then check one by one according to the ideas mentioned above.

First of all, let's look at the load information of the host. From the following figure, we can see that the host is basically in a no-load state and there is no pressure.

Then, when we look at the number and status of active sessions in the database, we can see that there are not a large number of sessions in the database, there are no executing DML statements operating on the table sbtest1, and there are no other sessions operating on the same table using DDL at the same time, but there is no way to confirm whether there are uncommitted transactions.

Check the transaction information recorded in information_schema first

# found that no transaction exists. Admin@localhost: sbtest 05:49:17 > select * from information_schema.innodb_trx\ GEmpty set (0.00 sec) # you can also use the sys.innodb_lock_waits view to confirm whether there are some transaction locks waiting for admin@localhost: performance_schema 06:27:35 > select * from sys.innodb_lock_waits\ GEmpty set 3 warnings (0.00 sec) # query result is empty to view MDL metadata lock record information # WTF.. under performance_schema Unexpectedly empty admin@localhost: sbtest 06:00:21 > select * from performance_schema.metadata_locks;Empty set (0.00 sec) # you can also use the sys.schema_table_lock_waits view to view table-level locks and wait for admin@localhost: performance_schema 06:28:12 > select * from sys.schema_table_lock_waits\ GEmpty set (0.00 sec) # query results are empty

View the handle holding information under performance_schema

View unlocked and transaction information through show engine innodb status

# still no valid lock information found admin@localhost: performance_schema 06:14:13 > show engine innodb status;.-TRANSACTIONS-Trx id counter 11559Purge done for trx's NRO

< 11557 undo n:o < 0 state: running but idleHistory list length 60LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 421628104988048, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421628104987136, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421628104985312, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421628104984400, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 421628104986224, not started0 lock struct(s), heap size 1136, 0 row lock(s)...... 排查到这里，视乎已经没招了，也许我们还可以用mysqladmin debug命令试试看 # 执行debug命令，执行该命令之后，一些锁信息可能会被debug出来打印到错误日志中[root@physical-machine ~]# mysqladmin debug# 很遗憾，在错误日志中仍然没有发现有效的锁信息[root@physical-machine ~]# vim /data/mysqldata1/log/error.log还有最后一招，可以使用pstack和gdb命令查看，但，生产系统不建议随随便便使用这类命令，这类命令会让整个实例级别发生一段时间的阻塞。且上述故障问题并不能说明整个数据库实例级别存在问题，也许只是发生MDL锁等待的两个会话之间的问题。so...咋办呢？要认怂吗？等等，我们好像忽略了点什么，既然DDL语句在等待MDL元数据锁，为啥在performance_schema.metadata_locks表中没有记录？查看一下MDL事件采集器试试看？ # 喔。。发现居然MDL锁信息的采集器开关并没有打开，难怪metadata_locks表中无法记录MDL元数据锁信息admin@localhost : performance_schema 06:30:16>

Select * from performance_schema.setup_instruments where name like'% / mdl' +-- + | NAME | ENABLED | TIMED | +-+ | wait/lock/metadata/sql/mdl | NO | NO | +- -+ 1 row in set (0.00 sec) # now We enabled the mdl collector admin@localhost: sbtest 07:18:52 > call sys.ps_setup_enable_instrument ('sql/mdl') +-+ | summary | +-- + | Enabled 6 instruments | +-- + 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) about instruments collector, if it is disabled beforehand, after it is enabled A new request is needed to collect the corresponding event information, so if we want to collect the information about the MDL lock, we have to reproduce the fault phenomenon. After reading this, maybe you will say, why didn't you say so earlier? Okay, SORRY, I did it on purpose. Now, let's stop the DDL statement and do it again using the steps at the end of the article.

Then, we re-query the transaction and lock information under information_schema

# check the information_schema.innodb_ trx table and still have no transaction information admin@localhost: sbtest 07:17:03 > select * from information_schema.innodb_trx\ GEmpty set (0.00 sec) # View transaction lock waiting information, it is still empty admin@localhost: sbtest 07:17:30 > select * from sys.innodb_lock_waits\ GEmpty set, 3 warnings (0.01 sec)

View MDL metadata lock record information under performance_schema

# check the MDL lock information in the metadata_ locks table. Hey, there's a message! However, it's a bit of a mess (we can only query the sbtest1 table under the sbtest library here, because this table is only involved in our operation) admin@localhost: (none) 11:52:46 > select * from performance_schema.metadata_locks where OBJECT_SCHEMA='sbtest' and OBJECT_NAME='sbtest1' + -+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | + From this line of information, we can see There is a SHARED_WRITE lock on the table sbtest1 that is in the GRANTED state and held by the thread of thread_id=69. An SHARED_WRITE type MDL lock is an intended exclusive IX lock, usually when performing MDL or select. | TABLE | sbtest | sbtest1 | 140048817276288 | SHARED_WRITE | TRANSACTION | GRANTED | | 69 | 11 | # from this line of information, we can see that there is a SHARED_UPGRADABLE lock on the table sbtest1 that is in the GRANTED state and held by the thread of thread_id=70. From the above information, we know that the process id of thread_id=70 is 28, that is, the session id where the DDL statement is blocked. A MDL lock of type SHARED_UPGRADABLE is a shared upgrade lock that is typically generated when an online DDL statement is executed. Its function is to allow DML of the same table during the execution of online ddl but prevent DDL | TABLE | sbtest | sbtest1 | 140049018604784 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | | 70 | 6 | # We can see from this line of information that there is an EXCLUSIVE in the PENDING state on the table sbtest1, and the thread for thread_id=70 is waiting. From the above information, we can know that the process id of thread_id=70 is 28, that is, the session id in which the DDL statement is blocked. An MDL lock of type EXCLUSIVE is an exclusive X lock that prevents other threads from reading and writing metadata information Usually generated when executing DDL | TABLE | sbtest | sbtest1 | 140049018564112 | EXCLUSIVE | TRANSACTION | PENDING | | 70 | 6 | +-- -+ 3 rows in set (0.00 sec) # We can know from the above information The EXCLUSIVE lock that the thread_id=70 thread needs to acquire conflicts with the SHARED_WRITE lock held by the thread_id=69 thread, but what is the process id of the thread_id=69 thread? Check the performance_schema.threads table admin@localhost: (none) 11:53:47 > select thread_id,processlist_id,type from performance_schema.threads where processlist_id is not null and type='FOREGROUND' +-+ | thread_id | processlist_id | type | +-+ | 43 | 1 | FOREGROUND | | 69 | 27 | FOREGROUND | # found the thread process id of thread_id=69 27 | 70 | 28 | FOREGROUND | | 71 | 29 | FOREGROUND | | 74 | 32 | FOREGROUND | +-+ 5 rows in set (0.01sec) # here We know that the DDL statement is blocked by a thread with a process id of 27, but it is not very intuitive and cumbersome to view, so let's just use the sys.schema_table_lock_waits view to view the table-level lock and wait and try, we can find that the printed information in this view looks very clear (you can clearly see who is waiting and who holds the lock), but why are there two lines? What is the right behavior? Admin@localhost: (none) 11:59:04 > select * from sys.schema_table_lock_waits\ gateway * 1. Row * * object_schema: sbtest object_name: sbtest1 waiting_thread_id: 70 waiting_pid: 28 waiting_account: admin@localhost waiting_lock_type: EXCLUSIVE waiting_lock_duration: TRANSACTION waiting_query: alter table sbtest1 modify col... E utf8_bin NOT NULL DEFAULT''waiting_query_secs: 744 waiting_query_rows_affected: 0 waiting_query_rows_examined: 0 blocking_thread_id: 69 blocking_pid: 27 blocking_account: admin@localhost blocking_lock_type: SHARED_WRITE blocking_lock_duration: TRANSACTION sql_kill_blocking_query: KILL QUERY 27 # this row of table locks waits for messages Prompt kill to drop the thread with process id 27 sql_kill_blocking_connection: KILL 27 thread * 2. Row * * object_schema: sbtest object_name: sbtest1 waiting_thread_id: 70 Waiting_pid: 28 waiting_account: admin@localhost waiting_lock_type: EXCLUSIVE waiting_lock_duration: TRANSACTION waiting_query: alter table sbtest1 modify col... E utf8_bin NOT NULL DEFAULT''waiting_query_secs: 744 waiting_query_rows_affected: 0 waiting_query_rows_examined: 0 blocking_thread_id: 70 blocking_pid: 28 blocking_account: admin@localhost blocking_lock_type: SHARED_UPGRADABLE blocking_lock_duration: TRANSACTION sql_kill_blocking_query: KILL QUERY 28 # the row table locks waiting for the letter The message prompts kill to drop the thread with process id 28 sql_kill_blocking_connection: KILL 282 rows in set (0.01sec) through the above toss We finally found out who holds the MDL lock, but unfortunately, we can't know what the thread holding the MDL lock did, so we can't kill it hastily. As people on the DBA side, maybe we can speculate that it may have something to do with the INSERT error operation of the application, but there is no direct evidence of correlation, so what should we do? Please move on to PS:

In MySQL version 5.7, the instruments collector of mdl is turned off by default. If the collector is not enabled in advance, it will cause great trouble to troubleshoot the problem. Therefore, it is recommended that you enable this collector in MySQL 5.7.

* enabling this collector will not cause much performance loss, but you can see the convenience it brings.

* more often, we may encounter that the statement is executing normally, but the execution time is so long that we cannot see what specific operations are being performed inside the MySQL at this time. At this time, it is even recommended that you enable all wait events by default. In many cases, you can easily check what specific operations are being performed in each session. Note, however, that performance_schema is enabled by default in MySQL version 5.7, resulting in a 1% to 15% performance loss compared to turning off performance_schema, and a 1% to 15% performance loss after enabling all wait events compared to turning off performance_schema. The specific wear and tear depends on the specific statement type. In MySQL version 8.0, mdl's instruments collector is enabled by default to solve the problem. We have found the cause of the problem above, and the general solution has also been found, but we are caught in a dilemma. At this time, how do we choose the way to solve this problem? Here we list the following solutions for reference:

Way 1: kill the session that blocks the DDL statement, but this is usually not a wise move, because we cannot find out what the session that holds the MDL lock did or determine what caused the MDL lock not to be released. And operating without the knowledge of the application side, there is a certain risk way 2: terminate the DDL statement, select the business trough or other time period to execute, obviously, this passive dry and may need to try again and again, and may not necessarily solve the problem is not the most efficient way 3: application developers communicate to confirm whether the INSERT statement must immediately insert ultra-long data (in addition Don't forget to ask application developers to work together to figure out what the thread with process id 27 did so that the MDL lock was not released)

* if not, it is recommended that the application developer adjust the data length to match the table structure to define the length, so that the DDL statement does not need to be executed for the time being, and the DDL operation can be revoked directly on the DBA side. If there is a need later, you can choose to execute it during a business trough or a maintenance window.

* if the INSERT statement must insert very long data immediately, it is suggested and instructed the application developer to deal with possible transaction rollback related issues (at this time the transaction is not committed, theoretically there is no risk of data loss in the rollback transaction at the database level), and then the DBA side executes the DDL statement to modify the field definition length PS: perhaps some students will question the second mode, in the production environment Can the DBA side make a wayward attempt to execute DDL statements? Of course not, as we mentioned above, the DDL operation we simulated here only modifies the metadata, so why only modify the metadata, because we change the length of the varchar type column from 60 characters to 70 characters, and the character set in the database environment is utf8. As to why the online ddl in MySQL 5.7 and later versions only need to modify the metadata to complete the operation after meeting these conditions, this article will not repeat it. Interested students please study the online ddl features of MySQL 5.7 or MySQL 8.0 by themselves. If the MDL lock waiting phenomenon is not detected in time for too long, it is dangerous in high concurrency business scenarios. Once the DML requests related to the table involved in the DDL continue to come in, it may cause a large number of lock waiting, or even quickly fill up the number of database connections. In order to prevent this situation, it is necessary to make prevention strategies on both the application side and the DBA side.

In the session where the DDL statement is executed, the DBA side can set the lock_wait_timeout system variable to a small value at the session level, and automatically abandon the DDL operation when the required lock cannot be obtained after that time value (please evaluate the requirement yourself)

Root@localhost: sbtest 04:37:43 > set lock_wait_timeout=10;Query OK, 0 rows affected (0.00 sec) root@localhost: sbtest 04:37:47 > alter table sbtest1 modify column `pad` varchar (70) COLLATE utf8_bin NOT NULL DEFAULT''; ERROR 1205 (HY000): Lock wait timeout exceeded Try restarting transaction application side needs to have corresponding retry and rollback mechanism (to release transaction-related resources in time when an exception occurs) and log record appendix-recurrence method 1 for each request. Start three sessions in the same database.

2) in session 1, open an explicit transaction and use the insert statement to insert a row of data that exceeds the defined length of the field (insert data on the simulation application side)

Admin@localhost: sbtest:13: > begin;Query OK, 0 rows affected (0.00 sec) admin@localhost: sbtest:17: > insert into sbtest1 values: > insert into sbtest1 values (2Magne2Magne2Magneto 40393031789-25132409365-58213491013-66541287984-65586459874-05762316127-59922091522-12151119251-49498591378-18011532520 "pad' at row 2976863337-73672352543-26439979097-89323822066-87557735686') ERROR 1406 (22001)

3) in session 2, lengthen the length of the error field pad to 70 characters (the simulation DBA side uses DDL statements to modify the column length definition)

# looking at the field definition length in the table structure, we can find that the error pad column defines a length of 60 characters of type varchar root@localhost: sbtest 04:12:03 > show create table sbtest1 +-+ | Table | Create Table | +- -+-+ | sbtest1 | CREATE TABLE `sbtest1` (`id` int (11) NOT NULL AUTO_INCREMENT The defined length of the # pad field is 60 PRIMARY KEY (`id`), `k` int (11) NOT NULL DEFAULT '0mm, `c` char (120) COLLATE utf8_bin NOT NULL DEFAULT'', `pad` varchar (60) COLLATE utf8_bin NOT NULL DEFAULT''. KEY `k1` (`k`) ENGINE=InnoDB AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin | +-+- -+ 1 row in set (0.01 sec) # View in the INSERT statement Pad column gives the character length and byte length of the data string the character length of the pad column value given in the # # INSERT statement is 64root@localhost: sbtest 11:01:33 > select char_length ('test-29736863337-73672352543-26439979097-89323822066-87557735686') +-- + | char_length ('test-29736863337-73672352543-26439979097-89323822066-87557735686') | +- -+ | 64 | + The byte length of the pad column value given in the + 1 row in set (0.00 sec) # # INSERT statement is 64root@localhost: sbtest 11:02:19 > select length ('test-29736863337-73672352543-26439979097-89323822066-87557735686') +-- + | length ('test-29736863337-73672352543-26439979097-89323822066-87557735686') | +- -+ | 64 | +- -+ 1 row in set (0.00 sec) # use the alter statement to modify the pad column definition length to 70root@localhost: sbtest 04:12:47 > alter table sbtest1 modify column `pad`varchar (70) COLLATE utf8_bin NOT NULL DEFAULT'' # # blocking occurs

4) in session 3, view the session state information in the database (troubleshooting problems on the simulated DBA side)

Note: in MySQL 5.7A statement exceeding the field definition length reported an error because the sql_mode parameter default value set strict mode. In MySQL 5.6and earlier versions, the sql_mode parameter default value was not set strict mode. In this case, the INSERT operation that exceeds the field definition length can still be performed successfully. But the data successfully inserted into the database is the truncated data (the extra-long part is discarded)

There are many types of PS:MDL metadata locks. According to the description of the LOCK_TYPE field of the performance_schema.metadata_locks table in the official manual, there are 9 (INTENTION_EXCLUSIVE, SHARED, SHARED_HIGH_PRIO, SHARED_READ, SHARED_WRITE, SHARED_UPGRADABLE, SHARED_NO_WRITE, SHARED_NO_READ_WRITE, EXCLUSIVE). However, the specific meaning and scenario of each MDL lock is not found in the official manual. For more information about MDL locks, please refer to the following two links

Https://blog.csdn.net/wanbf123/article/details/80697787

Https://blog.csdn.net/finalkof1983/article/details/88063328

| | author profile |

Luo Xiaobo Walk senior database technology expert

IT has worked for many years and is mainly responsible for the database support and after-sale second-line support of MySQL products. Participated in the design and preparation of version release system, lightweight monitoring system, operation and maintenance management platform, database management platform, familiar with MySQL architecture, Innodb storage engine, like to specialize in open source technology, has done offline database topic sharing in public many times, and published many database-related research articles.

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.