Bug #71357 Consider enabling Performance Schema metadata lock instrumentation by default
Submitted: 12 Jan 2014 23:45 Modified: 19 Apr 2017 8:05
Reporter: Jesper wisborg Krogh Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S4 (Feature request)
Version: OS:Any
Assigned to: Marc Alff CPU Architecture:Any

[12 Jan 2014 23:45] Jesper wisborg Krogh
Description:
MySQL 5.7.3 introduced support for monitoring metadata locks through the metadata_locks table in the performance_schema database.

However by default it is disabled.

Depending on whether there is any significant performance impact, the metadata lock instrumentation should be enabled by default as it is too late to enable it, once you have a lock issue (as only new locks/request for locks are instrumented after enabling the instrument).

How to repeat:
1) Start MySQL 5.7.3
2) In one connection cause a metadata lock:

   connection 1> LOCK TABLES world.City WRITE;

3) Enable MDL instrumentation:

   connection 2> UPDATE setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';

4) In a third connection try to SELECT from the world.City table:

   connection 3> SELECT * FROM world.City WHERE ID = 130;

   (this will block)

5) See the processlist:

   connection 2> SELECT THREAD_ID, PROCESSLIST_ID, PROCESSLIST_COMMAND, PROCESSLIST_STATE, PROCESSLIST_INFO FROM threads WHERE PROCESSLIST_ID <> CONNECTION_ID()\G
*************************** 1. row ***************************
          THREAD_ID: 20
     PROCESSLIST_ID: 1
PROCESSLIST_COMMAND: Sleep
  PROCESSLIST_STATE: Waiting on empty queue
   PROCESSLIST_INFO: NULL
*************************** 2. row ***************************
          THREAD_ID: 22
     PROCESSLIST_ID: 3
PROCESSLIST_COMMAND: Sleep
  PROCESSLIST_STATE: NULL
   PROCESSLIST_INFO: NULL
*************************** 3. row ***************************
          THREAD_ID: 23
     PROCESSLIST_ID: 4
PROCESSLIST_COMMAND: Query
  PROCESSLIST_STATE: Waiting for table metadata lock
   PROCESSLIST_INFO: SELECT * FROM world.City WHERE ID = 130
3 rows in set (0.01 sec)

6) Check the metadata_locks table:

   connection 2> SELECT * FROM metadata_locks\G
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: world
          OBJECT_NAME: City
OBJECT_INSTANCE_BEGIN: 194627536
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: PENDING
               SOURCE: sql_parse.cc:5542
      OWNER_THREAD_ID: 23
       OWNER_EVENT_ID: 3
*************************** 2. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: performance_schema
          OBJECT_NAME: metadata_locks
OBJECT_INSTANCE_BEGIN: 194628976
            LOCK_TYPE: SHARED_READ
        LOCK_DURATION: TRANSACTION
          LOCK_STATUS: GRANTED
               SOURCE: sql_parse.cc:5542
      OWNER_THREAD_ID: 21
       OWNER_EVENT_ID: 87
2 rows in set (0.00 sec)

So the metadata lock held by thread_id = 22 is not included.

Suggested fix:
Set the wait/lock/metadata/sql/mdl instrument to enabled by default.
[27 Jan 2017 0:13] Sveta Smirnova
I run SysBench OLTP RW tests and found that for this kind of load MLD instrumentation almost unnoticeable: https://www.percona.com/blog/2017/01/26/performance-schema-benchmarks-oltp-rw/
[19 Apr 2017 8:05] Marc Alff
The MDL instrumentation is now enabled by default in MySQL 8.0.2.