Comments on: Quickly Troubleshoot Metadata Locks in MySQL 5.7 https://www.percona.com/blog/quickly-troubleshooting-metadata-locks-mysql-5-7/ Tue, 13 Feb 2024 17:04:07 +0000 hourly 1 https://wordpress.org/?v=6.5.2 By: Adam Mulla https://www.percona.com/blog/quickly-troubleshooting-metadata-locks-mysql-5-7/#comment-10971530 Thu, 12 Dec 2019 16:48:19 +0000 https://www.percona.com/blog/?p=39656#comment-10971530 I am facing same issue while creating index on table other queries hitting on table so process list shows waiting for table metadata lock ..and these sessions increasing more than 200 sessions
And currently max_connectio is 100 mysql server.
How to avoid increasing session because table

Pleas help I am not able to find why number of sessions increasing?

]]>
By: Fernando https://www.percona.com/blog/quickly-troubleshooting-metadata-locks-mysql-5-7/#comment-10968877 Thu, 11 Jan 2018 16:20:41 +0000 https://www.percona.com/blog/?p=39656#comment-10968877 Hi, what about pre 5.7 versions?

]]>
By: michaeljxq https://www.percona.com/blog/quickly-troubleshooting-metadata-locks-mysql-5-7/#comment-10968600 Fri, 27 Oct 2017 07:13:35 +0000 https://www.percona.com/blog/?p=39656#comment-10968600 test@(none) 01:55:16>select version();
+—————+
| version() |
+—————+
| 5.7.19-17-log |
+—————+
1 row in set (0.00 sec)

test@(none) 01:54:44>select * from information_schema.processlist where command!=’sleep’ and user!=’system user’\G
*************************** 1. row ***************************
ID: 101176
USER: test
HOST: localhost
DB: test
COMMAND: Query
TIME: 64
STATE: Waiting for table metadata lock
INFO: alter table t1 add column name5 char(10)
TIME_MS: 64148
ROWS_SENT: 0
ROWS_EXAMINED: 0

test@(none) 01:55:30>CALL test.procShowMetadataLockSummary();
Query OK, 0 rows affected (0.00 sec)

test@(none) 01:55:53>SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, THREAD_ID, PROCESSLIST_ID, PROCESSLIST_INFO FROM performance_schema.metadata_locks INNER JOIN performance_schema.threads ON THREAD_ID = OWNER_THREAD_ID WHERE PROCESSLIST_ID CONNECTION_ID();
+————-+——————–+——————-+————-+————-+———–+—————-+——————+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_STATUS | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_INFO |
+————-+——————–+——————-+————-+————-+———–+—————-+——————+
| TABLE | performance_schema | global_status | SHARED_READ | GRANTED | 53741 | 53559 | NULL |
| TABLE | performance_schema | session_variables | SHARED_READ | GRANTED | 53741 | 53559 | NULL |
+————-+——————–+——————-+————-+————-+———–+—————-+——————+
2 rows in set (0.05 sec)

But when i restart mysqld, I can see it.
He is unstable

]]>
By: michaeljxq https://www.percona.com/blog/quickly-troubleshooting-metadata-locks-mysql-5-7/#comment-10968598 Fri, 27 Oct 2017 03:20:41 +0000 https://www.percona.com/blog/?p=39656#comment-10968598 I am using the mysql version as follows
Server version: 5.7.19-17-log Percona Server (GPL), Release 17, Revision e19a6b7b73f

However, I can not view the thread holding the MDL lock.
I refer to the following article
https://www.percona.com/blog/2016/12/28/quickly-troubleshooting-metadata-locks-mysql-5-7/

the test is described below

The following is the official version of the phenomenon (5.7.19)
test@test 06:13:51>select * from performance_schema.metadata_locks\G
*************************** 1. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: performance_schema
OBJECT_NAME: metadata_locks
OBJECT_INSTANCE_BEGIN: 140091634556304
LOCK_TYPE: SHARED_READ
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:5993
OWNER_THREAD_ID: 30
OWNER_EVENT_ID: 52
1 row in set (0.00 sec)

test@test 06:13:55>select * from performance_schema.setup_instruments where name like ‘wait/lock/metadata/sql/mdl’;
+—————————-+———+——-+
| NAME | ENABLED | TIMED |
+—————————-+———+——-+
| wait/lock/metadata/sql/mdl | YES | NO |
+—————————-+———+——-+
1 row in set (0.00 sec)

The following is the percona version of the phenomenon (5.7.19)
dbadmin@test 10:03:01>select * from performance_schema.metadata_locks\G
Empty set (0.00 sec)

dbadmin@test 10:12:19>select * from performance_schema.setup_instruments where name like ‘wait/lock/metadata/sql/mdl’;
+—————————-+———+——-+
| NAME | ENABLED | TIMED |
+—————————-+———+——-+
| wait/lock/metadata/sql/mdl | YES | NO |
+—————————-+———+——-+
1 row in set (0.00 sec)

thank you very mach.

]]>
By: Maggie https://www.percona.com/blog/quickly-troubleshooting-metadata-locks-mysql-5-7/#comment-10968285 Sun, 16 Jul 2017 17:05:40 +0000 https://www.percona.com/blog/?p=39656#comment-10968285 Thank you. This helps us to identify blocking sessions for metadatalock.

]]>
By: Michel https://www.percona.com/blog/quickly-troubleshooting-metadata-locks-mysql-5-7/#comment-10968141 Wed, 07 Jun 2017 12:04:18 +0000 https://www.percona.com/blog/?p=39656#comment-10968141 Thank you Jaime! I have implemented this solution because we have from time to time some long running processes accumulating in one of the nodes of our Percona cluster. Just waiting for the next occurrence of the problem!

]]>
By: Luke https://www.percona.com/blog/quickly-troubleshooting-metadata-locks-mysql-5-7/#comment-10967552 Wed, 11 Jan 2017 02:36:44 +0000 https://www.percona.com/blog/?p=39656#comment-10967552 nice job

]]>