神刀安全网

Extending the SYS schema to show metadata locks

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:

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:

mysql> select * fromperformance_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: USERLEVELLOCK         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 rowsin 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.

mysql> SELECTps.*,  lock_summary.lock_summary  FROMsys.processlistps  INNERJOIN (SELECTowner_thread_id,  GROUP_CONCAT(  DISTINCTCONCAT(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)))  ORDERBYmdl.object_typeASC, mdl.LOCK_STATUSASC, mdl.lock_typeASC  SEPARATOR '/n'  ) as lock_summaryFROMperformance_schema.metadata_locksmdlGROUPBYowner_thread_id) lock_summaryON (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: selectget_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: GRANTEDSHARED_READonwordpress_tockerca.wp_posts GRANTEDEXCLUSIVEonabcd (userlock) *************************** 2. row ***************************                 thd_id: 1128               conn_id: 1104                   user: root@localhost                     db: NULL               command: Query                 state: Sendingdata                   time: 0     current_statement: SELECTps.*,  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: GRANTEDSHAREDonsys.format_statement GRANTEDSHAREDonsys.format_time GRANTEDSHAREDonsys.format_bytes GRANTEDSHAREDonsys.sys_get_config GRANTEDSHARED_READonsys.processlist GRANTEDSHARED_READonperformance_schema.threads GRANTEDSHARED_READonperformance_schema.events_waits_current GRANTEDSHARED_READonperformance_schema.events_stages_current GRANTEDSHARED_READonperformance_schema.events_statements_current GRANTEDSHARED_READonperformance_schema.events_transactions_current GRANTEDSHARED_READonsys.x$memory_by_thread_by_current_bytes GRANTEDSHARED_READonperformance_schema.session_connect_attrs GRANTEDSHARED_READonperformance_schema.metadata_locks GRANTEDSHARED_READonsys.sys_config GRANTEDSHARED_READonperformance_schema.memory_summary_by_thread_by_event_name 2 rowsin 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

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » Extending the SYS schema to show metadata locks

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
分享按钮