处理waiting for metadata lock，需要:
注意，默认情况下，instruments是没有打开的：
|
1 2 3 4 5 6 7 8 9 |
mysql> select * from performance_schema.setup_instruments WHERE NAME = 'wait/lock/metadata/sql/mdl'; +----------------------------+---------+-------+ | NAME | ENABLED | TIMED | +----------------------------+---------+-------+ | wait/lock/metadata/sql/mdl | NO | NO | +----------------------------+---------+-------+ 1 row in set (0.00 sec) mysql> |
如下的方式可以打开instruments：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> mysql> select * from performance_schema.setup_instruments WHERE NAME = 'wait/lock/metadata/sql/mdl'; +----------------------------+---------+-------+ | NAME | ENABLED | TIMED | +----------------------------+---------+-------+ | wait/lock/metadata/sql/mdl | YES | NO | +----------------------------+---------+-------+ 1 row in set (0.00 sec) mysql> |
我们来测试一下，如何利用PS的instruments找到metadata lock的holder：
session 1：
|
1 2 3 4 5 6 7 |
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> delete from orasup_test1; Query OK, 131072 rows affected (0.48 sec) mysql> |
session 3：
|
1 2 3 4 5 |
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> drop table orasup_test1 --hang |
session 2： （此时，在查innodb_lock_waits等表是看不到信息的，因为innodb_lock_waits是行锁，而metadata lock是表锁。）
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> select * from sys.innodb_lock_waits \G; Empty set (0.00 sec) mysql> mysql> select * from information_schema.INNODB_LOCK_WAITS; Empty set (0.00 sec) mysql> select * from information_schema.INNODB_LOCKS; Empty set (0.00 sec) mysql> mysql> |
session 2：（需要从PS.metadata_locks 进行查询）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
mysql> select * from performance_schema.metadata_locks where OBJECT_NAME='orasup_test1' \G; *************************** 1. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: myawr OBJECT_NAME: orasup_test1 OBJECT_INSTANCE_BEGIN: 140695273259776 LOCK_TYPE: EXCLUSIVE LOCK_DURATION: TRANSACTION LOCK_STATUS: PENDING SOURCE: sql_parse.cc:5776 OWNER_THREAD_ID: 525483 OWNER_EVENT_ID: 21 *************************** 2. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: myawr OBJECT_NAME: orasup_test1 OBJECT_INSTANCE_BEGIN: 140694337352464 LOCK_TYPE: SHARED_WRITE LOCK_DURATION: TRANSACTION LOCK_STATUS: GRANTED SOURCE: sql_parse.cc:5776 OWNER_THREAD_ID: 523569 OWNER_EVENT_ID: 194 2 rows in set (0.00 sec) ERROR: No query specified mysql> mysql> mysql> mysql> mysql> show processlist; +--------+-----------------+-------------------+----------+-------------+---------+---------------------------------------------------------------+-------------------------+ | Id | User | Host | db | Command | Time | State | Info | +--------+-----------------+-------------------+----------+-------------+---------+---------------------------------------------------------------+-------------------------+ | 1 | event_scheduler | localhost | NULL | Daemon | 6 | Waiting for next activation | NULL | | 28368 | dbsync | 10.10.1.75:13766 | NULL | Binlog Dump | 1330136 | Master has sent all binlog to slave; waiting for more updates | NULL | | 521693 | itsm | 10.10.2.102:45586 | dji_itsm | Sleep | 284 | | NULL | | 522968 | itsm | 10.10.1.175:55165 | dji_itsm | Sleep | 166 | | NULL | | 523305 | root | localhost | myawr | Sleep | 5393 | | NULL | | 523542 | root | localhost | myawr | Sleep | 35 | | NULL | | 523950 | itsm | 10.10.2.102:46490 | dji_itsm | Sleep | 168 | | NULL | | 524308 | itsm | 10.10.1.175:55619 | dji_itsm | Sleep | 165 | | NULL | | 524531 | itsm | 10.10.2.102:46685 | dji_itsm | Sleep | 16 | | NULL | | 524718 | itsm | 10.10.1.175:55755 | dji_itsm | Sleep | 26 | | NULL | | 524858 | itsm | 10.10.1.175:55797 | dji_itsm | Sleep | 0 | | NULL | | 524873 | itsm | 10.10.1.175:55806 | dji_itsm | Sleep | 143 | | NULL | | 525070 | itsm | 10.10.2.102:46883 | dji_itsm | Sleep | 16 | | NULL | | 525309 | itsm | 10.10.2.102:46984 | dji_itsm | Sleep | 1 | | NULL | | 525456 | root | localhost | myawr | Query | 21 | Waiting for table metadata lock | drop table orasup_test1 | | 525496 | itsm | 10.10.2.102:47050 | dji_itsm | Sleep | 174 | | NULL | | 525497 | itsm | 10.10.2.102:47051 | dji_itsm | Sleep | 0 | | NULL | | 525498 | itsm | 10.10.2.102:47052 | dji_itsm | Sleep | 301 | | NULL | | 525522 | itsm | 10.10.1.175:56005 | dji_itsm | Sleep | 1 | | NULL | | 525545 | root | localhost | NULL | Query | 0 | starting | show processlist | +--------+-----------------+-------------------+----------+-------------+---------+---------------------------------------------------------------+-------------------------+ 20 rows in set (0.00 sec) mysql> mysql> mysql> select * from performance_schema.threads where thread_id='523569'\G; *************************** 1. row *************************** THREAD_ID: 523569 NAME: thread/sql/one_connection TYPE: FOREGROUND PROCESSLIST_ID: 523542 PROCESSLIST_USER: root PROCESSLIST_HOST: localhost PROCESSLIST_DB: myawr PROCESSLIST_COMMAND: Sleep PROCESSLIST_TIME: 62 PROCESSLIST_STATE: NULL PROCESSLIST_INFO: NULL PARENT_THREAD_ID: NULL ROLE: NULL INSTRUMENTED: YES HISTORY: YES CONNECTION_TYPE: Socket THREAD_OS_ID: 17276 1 row in set (0.00 sec) ERROR: No query specified mysql> kill 523542; Query OK, 0 rows affected (0.00 sec) mysql> |
session 3：
|
1 2 3 4 |
mysql> drop table orasup_test1; Query OK, 0 rows affected (1 min 3.79 sec) mysql> |