MySQL 9.0 發行說明
互斥鎖是一種在程式碼中使用的同步機制，用於強制一次只有一個執行緒可以存取共用資源。當伺服器中執行的兩個或多個執行緒需要存取相同的資源時，這些執行緒會互相競爭。第一個取得互斥鎖的執行緒會導致其他執行緒等待，直到該鎖被釋放。
對於已檢測的 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規則將特定的工具停用。例如，若要停用與全文搜尋相關的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資料表。您所啟用的 instrument 的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)一旦 instrument 和消費者啟用後，執行您想要監控的工作負載。在此範例中，使用 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) 進行彙總，即產生事件的 instrument 的名稱。彙總資料包括COUNT_STAR彙總的等待事件數量。
SUM_TIMER_WAIT彙總的計時等待事件的總等待時間。
MIN_TIMER_WAIT彙總的計時等待事件的最小等待時間。
AVG_TIMER_WAIT彙總的計時等待事件的平均等待時間。
MAX_TIMER_WAIT彙總的計時等待事件的最大等待時間。
以下查詢會傳回 instrument 名稱 (
EVENT_NAME)、等待事件的數量 (COUNT_STAR) 以及該 instrument 事件的總等待時間 (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;