MySQL 8.4 版本資訊
互斥鎖是一種同步機制，在程式碼中使用以強制一次只有一個執行緒可以存取共用資源。當伺服器中兩個或多個執行緒需要存取相同的資源時，執行緒會彼此競爭。第一個取得互斥鎖的執行緒會導致其他執行緒等待直到鎖定釋放。
對於已檢測的 InnoDB 互斥鎖，可以使用效能綱要監控互斥鎖等待。在效能綱要資料表中收集的等待事件資料可以幫助識別具有最多等待次數或總等待時間最長的互斥鎖，例如。
以下範例示範如何啟用 InnoDB 互斥鎖等待檢測、如何啟用相關取用者，以及如何查詢等待事件資料。
若要檢視可用的
InnoDB互斥鎖等待檢測，請查詢效能綱要setup_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/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-instrument規則新增至您的 MySQL 組態檔中performance-schema-instrument='wait/synch/mutex/innodb/%=ON'如果您不需要所有
InnoDB互斥鎖的等待事件資料，您可以透過將其他performance-schema-instrument規則新增至您的 MySQL 組態檔來停用特定檢測。例如，若要停用與全文搜尋相關的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/master_key_id_mutex | YES | YES | +-------------------------------------------------------+---------+-------+ 49 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: 0您可以透過查詢
setup_consumers表格來驗證等待事件消費者是否已啟用。events_waits_current、events_waits_history和events_waits_history_long消費者應該已啟用。mysql> 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 | YES | | events_statements_history_long | NO | | events_transactions_current | YES | | events_transactions_history | YES | | events_transactions_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 | +----------------------------------+---------+ 15 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/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 | +---------------------------------------------------------+------------+-------------------+注意上述結果集包含啟動過程中產生的等待事件資料。若要排除此資料，您可以在啟動後且執行工作負載之前立即截斷
events_waits_summary_global_by_event_name表格。但是，截斷操作本身可能會產生微不足道的等待事件資料。mysql> TRUNCATE performance_schema.events_waits_summary_global_by_event_name;