Starting with MySQL 5.7, performance_schema has been enhanced to instrument metadata locks, and can be turned on by adding the following line to your my.cnf file:
|
1 |
performance-schema-instrument='wait/lock/metadata/sql/%=ON' |
(At runtime, it can also be enabled by modifying the setup_instruments table in performance_schema.)
From here, you can now query performance_schema.metadata_locks to reveal all currently open metadata locks on your server:
|
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
|
mysql> select * from performance_schema.metadata_locks\G *************************** 1. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: wordpress_tockerca OBJECT_NAME: wp_posts OBJECT_INSTANCE_BEGIN: 139671129331184 LOCK_TYPE: SHARED_READ LOCK_DURATION: TRANSACTION LOCK_STATUS: GRANTED SOURCE: sql_parse.cc:5920 OWNER_THREAD_ID: 1817 OWNER_EVENT_ID: 25 *************************** 2. row *************************** OBJECT_TYPE: USER LEVEL LOCK OBJECT_SCHEMA: NULL OBJECT_NAME: abcd OBJECT_INSTANCE_BEGIN: 139671129776448 LOCK_TYPE: EXCLUSIVE LOCK_DURATION: EXPLICIT LOCK_STATUS: GRANTED SOURCE: item_func.cc:5636 OWNER_THREAD_ID: 1817 OWNER_EVENT_ID: 26 *************************** 3. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: performance_schema OBJECT_NAME: metadata_locks OBJECT_INSTANCE_BEGIN: 139672612788784 LOCK_TYPE: SHARED_READ LOCK_DURATION: TRANSACTION LOCK_STATUS: GRANTED SOURCE: sql_parse.cc:5920 OWNER_THREAD_ID: 1128 OWNER_EVENT_ID: 22 3 rows in set (0.00 sec) |
Using SYS
As I have previously demonstrated, SYS is the DBA’s companion to performance_schema and contains a number of views that are more task-oriented. Seeing the raw locks is not always immediately useful, what is preferable is a view that shows this next to user sessions.
SYS has a view called session, but in this example I am going to join metadata locks to sys.processlist instead. Processlist is a superset of session, that also includes background threads.
|
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
|
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: 1817 conn_id: 1793 user: root@localhost db: wordpress_tockerca command: Sleep state: NULL time: 317 current_statement: NULL statement_latency: NULL progress: NULL lock_latency: 0 ps rows_examined: 0 rows_sent: 1 rows_affected: 0 tmp_tables: 0 tmp_disk_tables: 0 full_scan: NO last_statement: select get_lock('abcd', 10) last_statement_latency: 315.92 us current_memory: 259.14 KiB last_wait: NULL last_wait_latency: NULL source: NULL trx_latency: 6.00 m trx_state: ACTIVE trx_autocommit: NO pid: 21518 program_name: mysql lock_summary: GRANTED SHARED_READ on wordpress_tockerca.wp_posts GRANTED EXCLUSIVE on abcd (user lock) *************************** 2. row *************************** thd_id: 1128 conn_id: 1104 user: root@localhost db: NULL command: Query state: Sending data time: 0 current_statement: SELECT ps.*, lock_summary.loc ... =lock_summary.owner_thread_id) statement_latency: 11.04 ms progress: NULL lock_latency: 4.08 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.97 MiB last_wait: NULL last_wait_latency: NULL source: NULL trx_latency: 70.00 ms trx_state: ACTIVE trx_autocommit: YES pid: 21498 program_name: mysql lock_summary: GRANTED SHARED on sys.format_statement GRANTED SHARED on sys.format_time GRANTED SHARED on sys.format_bytes GRANTED SHARED on sys.sys_get_config GRANTED SHARED_READ on sys.processlist GRANTED SHARED_READ on performance_schema.threads GRANTED SHARED_READ on performance_schema.events_waits_current GRANTED SHARED_READ on performance_schema.events_stages_current GRANTED SHARED_READ on performance_schema.events_statements_current GRANTED SHARED_READ on performance_schema.events_transactions_current GRANTED SHARED_READ on sys.x$memory_by_thread_by_current_bytes GRANTED SHARED_READ on performance_schema.session_connect_attrs GRANTED SHARED_READ on performance_schema.metadata_locks GRANTED SHARED_READ on sys.sys_config GRANTED SHARED_READ on performance_schema.memory_summary_by_thread_by_event_name 2 rows in set (0.09 sec) |
On my testing system I have saved this query as a view called sys.session_metadata_locks. I have also opened BUG #80823 so that the sys developers can evaluate if this should be included as part of a future release.
It is always interesting to hear from DBAs on what information they find useful to be included in views. Please try it out, and let me know your feedback 🙂