Bug #80823 sys should have a mdl session oriented view
Submitted: 22 Mar 2016 12:38 Modified: 14 Apr 2016 8:26
Reporter: Morgan Tocker Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: SYS Schema Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to:
Triage: Needs Triage: D5 (Feature request)

[22 Mar 2016 12:38] Morgan Tocker
Description:
MDL instrumentation is not enabled by default, but when enabled there are not any helpful SyS views.  What might be helpful is a session-like view that summarizes either:

a) All locks (as shown below), or;
b) All exclusive locks and pending locks (less verbose)

This requires the following configuration:
performance-schema-instrument='wait/lock/metadata/sql/%=ON'

How to repeat:
mysql> SELECT ps.*,  lock_summary.lock_summary  FROM sys.processlist ps  INNER JOIN (SELECT owner_thread_id,  GROUP_CONCAT(   DISTINCT CONCAT(mdl.LOCK_STATUS, ' ', mdl.lock_type, ' on ', IF(mdl.object_type='USER LEVEL LOCK', CONCAT(mdl.object_name, ' (user lock)'), CONCAT(mdl.OBJECT_SCHEMA, '.', mdl.OBJECT_NAME)))   ORDER BY mdl.object_type ASC, mdl.LOCK_STATUS ASC, mdl.lock_type ASC   SEPARATOR '\n'  ) as lock_summary FROM performance_schema.metadata_locks mdl GROUP BY owner_thread_id) lock_summary ON (ps.thd_id=lock_summary.owner_thread_id)\G
*************************** 1. row ***************************
                thd_id: 10841
               conn_id: 10816
                  user: root@localhost
                    db: sys
               command: Query
                 state: Sending data
                  time: 0
     current_statement: SELECT ps.*,  lock_summary.loc ... =lock_summary.owner_thread_id)
     statement_latency: 2.20 ms
              progress: NULL
          lock_latency: 1.16 ms
         rows_examined: 0
             rows_sent: 0
         rows_affected: 0
            tmp_tables: 6
       tmp_disk_tables: 2
             full_scan: YES
        last_statement: NULL
last_statement_latency: NULL
        current_memory: 2.94 MiB
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
           trx_latency: 1.08 ms
             trx_state: ACTIVE
        trx_autocommit: YES
                   pid: 3585
          program_name: mysql
          lock_summary: GRANTED SHARED on sys.sys_get_config
GRANTED SHARED on sys.format_bytes
GRANTED SHARED on sys.format_time
GRANTED SHARED on sys.format_statement
GRANTED SHARED_READ on sys.sys_config
GRANTED SHARED_READ on performance_schema.metadata_locks
GRANTED SHARED_READ on performance_schema.session_connect_attrs
GRANTED SHARED_READ on performance_schema.memory_summary_by_thread_by_event_name
GRANTED SHARED_READ on sys.x$memory_by_thread_by_current_bytes
GRANTED SHARED_READ on performance_schema.events_transactions_current
GRANTED SHARED_READ on performance_schema.events_statements_current
GRANTED SHARED_READ on performance_schema.events_stages_current
GRANTED SHARED_READ on performance_schema.events_waits_current
GRANTED SHARED_READ on performance_schema.threads
GRANTED SHARED_READ on sys.processlist
1 row in set (0.06 sec)

Suggested fix:
CREATE VIEW session_metadata_locks as SELECT ps.*,  lock_summary.lock_summary  FROM sys.processlist ps  INNER JOIN (SELECT owner_thread_id,  GROUP_CONCAT(   DISTINCT CONCAT(mdl.LOCK_STATUS, ' ', mdl.lock_type, ' on ', IF(mdl.object_type='USER LEVEL LOCK', CONCAT(mdl.object_name, ' (user lock)'), CONCAT(mdl.OBJECT_SCHEMA, '.', mdl.OBJECT_NAME)))   ORDER BY mdl.object_type ASC, mdl.LOCK_STATUS ASC, mdl.lock_type ASC   SEPARATOR '\n'  ) as lock_summary FROM performance_schema.metadata_locks mdl GROUP BY owner_thread_id) lock_summary ON (ps.thd_id=lock_summary.owner_thread_id);
[14 Apr 2016 8:26] Umesh Shastry
Hello Morgan,

Thank you for the feature request!

Thanks,
Umesh
[18 Jun 2016 21:36] Omer Barnir
Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0