查看可用的InnoDB互斥等待仪器，查询性能模式setup_instruments表格所有InnoDB互斥锁等待工具在默认情况下是禁用的。

SELECT * FROM performance_schema。setup_instrumentsWHERE NAME LIKE '%wait/synch/mutex/innodb%'; +---------------------------------------------------------+---------+-------+ | NAME | ENABLED | TIMED | +---------------------------------------------------------+---------+-------+ | wait/synch/mutex/innodb/commit_cond_mutex | NO | NO | | wait/synch/mutex/innodb/innobase_share_mutex | NO | NO | | wait/synch/mutex/innodb/autoinc_mutex | NO | NO | | wait/synch/mutex/innodb/autoinc_persisted_mutex | NO | NO | | wait/synch/mutex/innodb/buf_pool_flush_state_mutex | NO | NO | | wait/synch/mutex/innodb/buf_pool_LRU_list_mutex | NO | NO | | wait/synch/mutex/innodb/buf_pool_free_list_mutex | NO | NO | | wait/synch/mutex/innodb/buf_pool_zip_free_mutex | NO | NO | | wait/synch/mutex/innodb/buf_pool_zip_hash_mutex | NO | NO | | wait/synch/mutex/innodb/buf_pool_zip_mutex | NO | NO | | wait/synch/mutex/innodb/cache_last_read_mutex | NO | NO | | wait/synch/mutex/innodb/dict_foreign_err_mutex | NO | NO | | wait/synch/mutex/innodb/dict_persist_dirty_tables_mutex | NO | NO | | wait/synch/mutex/innodb/dict_sys_mutex | NO | NO | | wait/synch/mutex/innodb/recalc_pool_mutex | NO | NO | | wait/synch/mutex/innodb/fil_system_mutex | NO | NO | | wait/synch/mutex/innodb/flush_list_mutex | NO | NO | | wait/synch/mutex/innodb/fts_bg_threads_mutex | NO | NO | | wait/synch/mutex/innodb/fts_delete_mutex | NO | NO | | wait/synch/mutex/innodb/fts_optimize_mutex | NO | NO | | wait/synch/mutex/innodb/fts_doc_id_mutex | NO | NO | | wait/synch/mutex/innodb/log_flush_order_mutex | NO | NO | | wait/synch/mutex/innodb/hash_table_mutex | NO | NO | | wait/synch/mutex/innodb/ibuf_bitmap_mutex | NO | NO | | wait/synch/mutex/innodb/ibuf_mutex | NO | NO | | wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex | NO | NO | | wait/synch/mutex/innodb/log_sys_mutex | NO | NO | | wait/synch/mutex/innodb/log_sys_write_mutex | NO | NO | | wait/synch/mutex/innodb/mutex_list_mutex | NO | NO | | wait/synch/mutex/innodb/page_zip_stat_per_index_mutex | NO | NO | | wait/synch/mutex/innodb/purge_sys_pq_mutex | NO | NO | | wait/synch/mutex/innodb/recv_sys_mutex | NO | NO | | wait/synch/mutex/innodb/recv_writer_mutex | NO | NO | | wait/synch/mutex/innodb/redo_rseg_mutex | NO | NO | | wait/synch/mutex/innodb/noredo_rseg_mutex | NO | NO | | wait/synch/mutex/innodb/rw_lock_list_mutex | NO | NO | | wait/synch/mutex/innodb/rw_lock_mutex | NO | NO | | wait/synch/mutex/innodb/srv_dict_tmpfile_mutex | NO | NO | | wait/synch/mutex/innodb/srv_innodb_monitor_mutex | NO | NO | | wait/synch/mutex/innodb/srv_misc_tmpfile_mutex | NO | NO | | wait/synch/mutex/innodb/srv_monitor_file_mutex | NO | NO | | wait/synch/mutex/innodb/buf_dblwr_mutex | NO | NO | | wait/synch/mutex/innodb/trx_undo_mutex | NO | NO | | wait/synch/mutex/innodb/trx_pool_mutex | NO | NO | | wait/synch/mutex/innodb/trx_pool_manager_mutex | NO | NO | | wait/synch/mutex/innodb/srv_sys_mutex | NO | NO | | wait/synch/mutex/innodb/lock_mutex | NO | NO | | wait/synch/mutex/innodb/lock_wait_mutex | NO | NO | | wait/synch/mutex/innodb/trx_mutex | NO | NO | | wait/synch/mutex/innodb/srv_threads_mutex | NO | NO | | wait/synch/mutex/innodb/rtr_active_mutex | NO | NO | | wait/synch/mutex/innodb/rtr_match_mutex | NO | NO | | wait/synch/mutex/innodb/rtr_path_mutex | NO | NO | | wait/synch/mutex/innodb/rtr_ssn_mutex | NO | NO | | wait/synch/mutex/innodb/trx_sys_mutex | NO | NO | | wait/synch/mutex/innodb/zip_pad_mutex | NO | NO | | wait/synch/mutex/innodb/master_key_id_mutex | NO | NO | +---------------------------------------------------------+---------+-------+

一些InnoDB互斥锁实例是在服务器启动时创建的，只有在服务器启动时也启用了相关的工具时，才会检测互斥锁实例。确保所有InnoDB互斥锁实例已检测并启用，请添加以下内容performance-schema-instrumentMySQL配置文件规则:

performance-schema-instrument = '等待/同步/互斥/ innodb / % =“

如果不需要等待所有事件数据InnoDB互斥锁，您可以通过添加额外的performance-schema-instrumentMySQL配置文件的规则。例如，禁用InnoDB互斥锁等待事件工具相关全文搜索，添加以下规则:

performance-schema-instrument = '等待/同步/互斥/ innodb / fts % =了'

后添加performance-schema-instrument规则，重新启动服务器。所有的InnoDB除与全文搜索相关的互斥锁外，互斥锁都是启用的。若要验证，请查询setup_instruments表格的启用而且定时列应该设置为是的你启用的仪器。

SELECT * FROM performance_schema。setup_instrumentsWHERE NAME LIKE '%wait/synch/mutex/innodb%'; +-------------------------------------------------------+---------+-------+ | NAME | ENABLED | TIMED | +-------------------------------------------------------+---------+-------+ | wait/synch/mutex/innodb/commit_cond_mutex | YES | YES | | wait/synch/mutex/innodb/innobase_share_mutex | YES | YES | | wait/synch/mutex/innodb/autoinc_mutex | YES | YES | ... | wait/synch/mutex/innodb/master_key_id_mutex | YES | YES | +-------------------------------------------------------+---------+-------+ 49 rows in set (0.00 sec)

控件启用等待事件使用者setup_consumers表格默认情况下，等待事件消费者是禁用的。

mysql >更新performance_schema。setup_consumersSET enabled = 'YES' WHERE name like 'events_waits%'; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0

的方法，可以验证等待事件使用者是否已启用setup_consumers表格的events_waits_current，events_waits_history,events_waits_history_long应该启用消费者。

SELECT * FROM performance_schema.setup_consumers;+----------------------------------+---------+ | 启用名字|  | +----------------------------------+---------+ | events_stages_current没有| | | events_stages_history没有| | | events_stages_history_long没有| | | events_statements_current |是| | events_statements_history |是| | events_statements_history_long没有| | | events_transactions_current |是| | events_transactions_history |是| | events_transactions_history_long没有| | | events_waits_current |是| | events_waits_history |是的||events_waits_history_long|是的||global_instrumentation | YES | | thread_instrumentation | YES | | statements_digest | YES | +----------------------------------+---------+ 15 rows in set (0.00 sec)

一旦启用了工具和使用者，就可以运行要监视的工作负载。在本例中，mysqlslap负载模拟客户机用于模拟工作负载。

Shell > ./mysqlslap——auto-generate-sql——并发=100——迭代=10——number-of-queries=1000——number-char-cols=6——number-int-cols=6;

查询等待事件数据。在本例中，从events_waits_summary_global_by_event_name集合中找到的数据的events_waits_current，events_waits_history,events_waits_history_long表。数据按事件名称(EVENT_NAME)，这是产生这一事件的仪器的名称。总结数据包括:

以下查询返回仪器名称(EVENT_NAME)，等待事件的数量(COUNT_STAR)，以及该仪器事件的总等待时间(SUM_TIMER_WAIT)．因为默认情况下，等待的计时单位是皮秒(万亿分之一秒)，所以等待时间除以1000000000以显示以毫秒为单位的等待时间。数据按汇总的等待事件的数量(COUNT_STAR)．您可以调整命令子句按总等待时间对数据进行排序。

mysql> SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1000000000 SUM_TIMER_WAIT_MS FROM performance_schema。events_waits_summary_global_by_event_nameWHERE SUM_TIMER_WAIT > 0 AND EVENT_NAME LIKE 'wait/synch/mutex/innodb/%' ORDER BY COUNT_STAR DESC; +---------------------------------------------------------+------------+-------------------+ | EVENT_NAME | COUNT_STAR | SUM_TIMER_WAIT_MS | +---------------------------------------------------------+------------+-------------------+ | wait/synch/mutex/innodb/trx_mutex | 201111 | 23.4719 | | wait/synch/mutex/innodb/fil_system_mutex | 62244 | 9.6426 | | wait/synch/mutex/innodb/redo_rseg_mutex | 48238 | 3.1135 | | wait/synch/mutex/innodb/log_sys_mutex | 46113 | 2.0434 | | wait/synch/mutex/innodb/trx_sys_mutex | 35134 | 1068.1588 | | wait/synch/mutex/innodb/lock_mutex | 34872 | 1039.2589 | | wait/synch/mutex/innodb/log_sys_write_mutex | 17805 | 1526.0490 | | wait/synch/mutex/innodb/dict_sys_mutex | 14912 | 1606.7348 | | wait/synch/mutex/innodb/trx_undo_mutex | 10634 | 1.1424 | | wait/synch/mutex/innodb/rw_lock_list_mutex | 8538 | 0.1960 | | wait/synch/mutex/innodb/buf_pool_free_list_mutex | 5961 | 0.6473 | | wait/synch/mutex/innodb/trx_pool_mutex | 4885 | 8821.7496 | | wait/synch/mutex/innodb/buf_pool_LRU_list_mutex | 4364 | 0.2077 | | wait/synch/mutex/innodb/innobase_share_mutex | 3212 | 0.2650 | | wait/synch/mutex/innodb/flush_list_mutex | 3178 | 0.2349 | | wait/synch/mutex/innodb/trx_pool_manager_mutex | 2495 | 0.1310 | | wait/synch/mutex/innodb/buf_pool_flush_state_mutex | 1318 | 0.2161 | | wait/synch/mutex/innodb/log_flush_order_mutex | 1250 | 0.0893 | | wait/synch/mutex/innodb/buf_dblwr_mutex | 951 | 0.0918 | | wait/synch/mutex/innodb/recalc_pool_mutex | 670 | 0.0942 | | wait/synch/mutex/innodb/dict_persist_dirty_tables_mutex | 345 | 0.0414 | | wait/synch/mutex/innodb/lock_wait_mutex | 303 | 0.1565 | | wait/synch/mutex/innodb/autoinc_mutex | 196 | 0.0213 | | wait/synch/mutex/innodb/autoinc_persisted_mutex | 196 | 0.0175 | | wait/synch/mutex/innodb/purge_sys_pq_mutex | 117 | 0.0308 | | wait/synch/mutex/innodb/srv_sys_mutex | 94 | 0.0077 | | wait/synch/mutex/innodb/ibuf_mutex | 22 | 0.0086 | | wait/synch/mutex/innodb/recv_sys_mutex | 12 | 0.0008 | | wait/synch/mutex/innodb/srv_innodb_monitor_mutex | 4 | 0.0009 | | wait/synch/mutex/innodb/recv_writer_mutex | 1 | 0.0005 | +---------------------------------------------------------+------------+-------------------+

mysql >截断performance_schema.events_waits_summary_global_by_event_name;