互斥量是代码中使用的一种同步机制，用于强制在给定时间只有一个线程可以访问公共资源。当在服务器中执行的两个或多个线程需要访问同一资源时，这些线程就会相互竞争。第一个获得互斥量锁的线程会导致其他线程等待锁被释放。
对于InnoDB被检测的互斥量，可以使用
性能模式监视互斥量等待。例如，在 Performance Schema 表中收集的等待事件数据可以帮助识别等待最多或总等待时间最长的互斥量。
以下示例演示了如何启用
InnoDB互斥等待工具、如何启用关联消费者以及如何查询等待事件数据。
要查看可用
InnoDB的互斥等待工具，请查询 Performance Schemasetup_instruments表，如下所示。InnoDB默认情况下禁用 所有互斥等待工具。mysql> SELECT * FROM performance_schema.setup_instruments WHERE 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/buf_pool_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_sys_mutex | NO | NO | | wait/synch/mutex/innodb/file_format_max_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/fts_pll_tokenize_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/mem_pool_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_bh_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/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/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/os_mutex | NO | NO | | wait/synch/mutex/innodb/ut_list_mutex | NO | NO | | wait/synch/mutex/innodb/trx_sys_mutex | NO | NO | | wait/synch/mutex/innodb/zip_pad_mutex | NO | NO | +-------------------------------------------------------+---------+-------+ 46 rows in set (0.00 sec)一些
InnoDB互斥锁实例是在服务器启动时创建的，并且只有在服务器启动时也启用了相关仪器时才会被检测。为确保InnoDB检测和启用所有互斥锁实例，请将以下performance-schema-instrument规则添加到您的 MySQL 配置文件中：performance-schema-instrument='wait/synch/mutex/innodb/%=ON'如果您不需要所有
InnoDB互斥锁的等待事件数据，您可以通过向performance-schema-instrumentMySQL 配置文件添加额外的规则来禁用特定的工具。例如，要禁用InnoDB与全文搜索相关的互斥等待事件工具，请添加以下规则：performance-schema-instrument='wait/synch/mutex/innodb/fts%=OFF'笔记具有较长前缀的规则（例如 ）
wait/synch/mutex/innodb/fts%优先于具有较短前缀的规则（例如wait/synch/mutex/innodb/%.将
performance-schema-instrument规则添加到配置文件后，重新启动服务器。InnoDB除与全文搜索相关的互斥锁外，所有 互斥锁都已启用。要验证，请查询该setup_instruments表。和ENABLED列TIMED应该设置YES为您启用的工具。mysql> SELECT * FROM performance_schema.setup_instruments WHERE 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/zip_pad_mutex | YES | YES | +-------------------------------------------------------+---------+-------+ 46 rows in set (0.00 sec)setup_consumers通过更新表 启用等待事件消费者 。默认情况下禁用等待事件消费者。mysql> UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name like 'events_waits%'; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0setup_consumers您可以通过查询表 来验证等待事件使用者是否已启用。、events_waits_current和 消费者应该被启用events_waits_history。events_waits_history_longmysql> SELECT * FROM performance_schema.setup_consumers; +--------------------------------+---------+ | NAME | ENABLED | +--------------------------------+---------+ | events_stages_current | NO | | events_stages_history | NO | | events_stages_history_long | NO | | events_statements_current | YES | | events_statements_history | NO | | events_statements_history_long | NO | | events_waits_current | YES | | events_waits_history | YES | | events_waits_history_long | YES | | global_instrumentation | YES | | thread_instrumentation | YES | | statements_digest | YES | +--------------------------------+---------+ 12 rows in set (0.00 sec)启用仪器和消费者后，运行您要监控的工作负载。在此示例中， mysqlslap负载仿真客户端用于模拟工作负载。
$> ./mysqlslap --auto-generate-sql --concurrency=100 --iterations=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) 汇总，事件名称是产生事件的仪器的名称。汇总数据包括：COUNT_STAR汇总等待事件的数量。
SUM_TIMER_WAIT汇总的定时等待事件的总等待时间。
MIN_TIMER_WAIT汇总的定时等待事件的最短等待时间。
AVG_TIMER_WAIT汇总的定时等待事件的平均等待时间。
MAX_TIMER_WAIT汇总的定时等待事件的最大等待时间。
以下查询返回仪器名称 (
EVENT_NAME)、等待事件数 (COUNT_STAR) 以及该仪器事件的总等待时间 (SUM_TIMER_WAIT)。因为默认情况下等待时间以皮秒（万亿分之一秒）为单位，所以等待时间除以 1000000000 以显示以毫秒为单位的等待时间。数据按汇总等待事件的数量 ( ) 降序显示COUNT_STAR。您可以调整ORDER BY子句以按总等待时间对数据进行排序。mysql> SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1000000000 SUM_TIMER_WAIT_MS FROM performance_schema.events_waits_summary_global_by_event_name WHERE 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/srv_threads_mutex | 454474 | 16.3490 | | wait/synch/mutex/innodb/trx_mutex | 107586 | 8.2415 | | wait/synch/mutex/innodb/log_sys_mutex | 37292 | 460.9484 | | wait/synch/mutex/innodb/redo_rseg_mutex | 31077 | 3.2212 | | wait/synch/mutex/innodb/trx_sys_mutex | 20005 | 447.8246 | | wait/synch/mutex/innodb/lock_mutex | 19293 | 728.4862 | | wait/synch/mutex/innodb/fil_system_mutex | 15789 | 1.3710 | | wait/synch/mutex/innodb/dict_sys_mutex | 9606 | 319.7662 | | wait/synch/mutex/innodb/rw_lock_list_mutex | 8351 | 0.2046 | | wait/synch/mutex/innodb/trx_undo_mutex | 6274 | 0.5573 | | wait/synch/mutex/innodb/buf_pool_mutex | 3230 | 7.3861 | | wait/synch/mutex/innodb/trx_pool_mutex | 2225 | 9591.4174 | | wait/synch/mutex/innodb/innobase_share_mutex | 2025 | 0.1891 | | wait/synch/mutex/innodb/flush_list_mutex | 1421 | 0.1452 | | wait/synch/mutex/innodb/trx_pool_manager_mutex | 1115 | 0.1503 | | wait/synch/mutex/innodb/file_format_max_mutex | 1032 | 0.0445 | | wait/synch/mutex/innodb/buf_dblwr_mutex | 594 | 0.0950 | | wait/synch/mutex/innodb/log_flush_order_mutex | 502 | 0.0489 | | wait/synch/mutex/innodb/srv_sys_mutex | 417 | 0.6856 | | wait/synch/mutex/innodb/recalc_pool_mutex | 406 | 0.0842 | | wait/synch/mutex/innodb/purge_sys_pq_mutex | 365 | 0.0302 | | wait/synch/mutex/innodb/recv_sys_mutex | 273 | 0.0121 | | wait/synch/mutex/innodb/lock_wait_mutex | 170 | 0.0852 | | wait/synch/mutex/innodb/noredo_rseg_mutex | 32 | 0.0008 | | wait/synch/mutex/innodb/ibuf_mutex | 10 | 0.0024 | | wait/synch/mutex/innodb/srv_innodb_monitor_mutex | 2 | 0.0005 | | wait/synch/mutex/innodb/autoinc_mutex | 2 | 0.0004 | | wait/synch/mutex/innodb/recv_writer_mutex | 1 | 0.0001 | +--------------------------------------------------+------------+-------------------+ 28 rows in set (0.03 sec)笔记上述结果集中包括启动过程中产生的等待事件数据。要排除此数据，您可以
events_waits_summary_global_by_event_name在启动后和运行工作负载之前立即截断该表。但是，截断操作本身可能会产生可忽略不计的等待事件数据。mysql> TRUNCATE performance_schema.events_waits_summary_global_by_event_name;