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
• Status: Verified
• Category: MySQL Server: SYS Schema
• Severity: S4 (Feature request)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[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] MySQL Verification Team

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

[15 Jul 2019 9:27] Przemyslaw Malkowski

This one works well on 5.7 too, would be great to add it there as well.

[31 Mar 2020 3:27] Morgan Tocker

This is a MySQL 8.0 version which is a little easier to read:

WITH lock_summary AS (
 SELECT
  owner_thread_id,
  GROUP_CONCAT(
   DISTINCT
   CONCAT(
    LOCK_STATUS, ' ',
    lock_type, ' on ',
    IF(object_type='USER LEVEL LOCK', CONCAT(object_name, ' (user lock)'), CONCAT(OBJECT_SCHEMA, '.', OBJECT_NAME))
   )
   ORDER BY object_type ASC, LOCK_STATUS ASC, lock_type ASC
   SEPARATOR '\n' 
  ) AS lock_summary
 FROM performance_schema.metadata_locks
 GROUP BY owner_thread_id
)
SELECT
 ps.*,
 lock_summary.lock_summary
FROM sys.processlist ps
INNER JOIN lock_summary ON ps.thd_id=lock_summary.owner_thread_id;