互斥锁是代码中使用的一种同步机制,用于强制在给定时间只有一个线程可以访问公共资源。当服务器中执行的两个或更多线程需要访问同一资源时,这些线程会相互竞争。在互斥锁上获得锁的第一个线程导致其他线程 await,直到锁被释放。
对于已检测的InnoDB个互斥锁,可以使用Performance Schema监视互斥锁 await。例如,性能模式 table 中收集的 await 事件数据可以帮助识别 await 时间最多或总 await 时间最长的 Mutex。
以下示例演示了如何启用InnoDB互斥锁 await 工具,如何启用关联的使用者以及如何查询 await 事件数据。
要查看可用的InnoDB互斥锁 await 工具,请查询 Performance Schema setup_instrumentstable,如下所示。默认情况下,禁用所有InnoDB互斥锁 await 工具。
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/recalc_pool_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/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/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 |
+-------------------------------------------------------+---------+-------+
49 rows in set (0.02 sec)
某些InnoDB互斥锁实例是在服务器启动时创建的,只有在服务器启动时也启用了关联的工具的情况下,才进行检测。为确保已检测并启用所有InnoDB互斥锁实例,请将以下performance-schema-instrument规则添加至 MySQL 配置文件:
performance-schema-instrument='wait/synch/mutex/innodb/%=ON'
如果不需要所有InnoDB互斥锁的 await 事件数据,则可以通过将其他performance-schema-instrument规则添加到 MySQL 配置文件来禁用特定工具。例如,要禁用与全文搜索有关的InnoDB互斥锁 await 事件工具,请添加以下规则:
performance-schema-instrument='wait/synch/mutex/innodb/fts%=OFF'
Note
前缀较长的规则(例如wait/synch/mutex/innodb/fts%)优先于前缀较短的规则(例如wait/synch/mutex/innodb/%)。
将performance-schema-instrument规则添加到配置文件后,重新启动服务器。除与全文搜索有关的所有InnoDB互斥锁外,其他所有启用。要进行验证,请查询setup_instrumentstable。对于启用的工具,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 |
+-------------------------------------------------------+---------+-------+
49 rows in set (0.00 sec)
通过更新setup_consumerstable 来启用 await 事件使用者。默认情况下,禁用 await 事件使用者。
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
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负载模拟 Client 端用于模拟工作负载。
shell> ./mysqlslap --auto-generate-sql --concurrency=100 --iterations=10
--number-of-queries=1000 --number-char-cols=6 --number-int-cols=6;
查询 await 事件数据。在此示例中,从events_waits_summary_global_by_event_nametable 中查询 await 事件数据,该 table 汇总了在events_waits_current,events_waits_history和events_waits_history_longtable 中找到的数据。数据通过事件名称(EVENT_NAME)进行汇总,事件名称是产生该事件的仪器的名称。汇总数据包括:
COUNT_STAR
汇总的 await 事件数。
SUM_TIMER_WAIT
汇总的定时 await 事件的总 await 时间。
MIN_TIMER_WAIT
摘要定时 await 事件的最短 await 时间。
AVG_TIMER_WAIT
汇总的定时 await 事件的平均 await 时间。
MAX_TIMER_WAIT
摘要定时 await 事件的最大 await 时间。
以下查询返回仪器名称(EVENT_NAME),await 事件数(COUNT_STAR)以及该仪器事件的总 await 时间(SUM_TIMER_WAIT)。因为默认情况下,await 时间以皮秒(万亿分之一秒)为单位,所以 await 时间除以 1000000000,以毫秒为单位显示 await 时间。数据以降序 Sequences 显示,即汇总的 await 事件数(COUNT_STAR)。您可以调整ORDER BY子句以按总 await 时间排序数据。
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/os_mutex | 78831 | 10.3283 |
| wait/synch/mutex/innodb/log_sys_mutex | 41488 | 6510.3233 |
| wait/synch/mutex/innodb/trx_sys_mutex | 29770 | 1107.9687 |
| wait/synch/mutex/innodb/lock_mutex | 24212 | 104.0724 |
| wait/synch/mutex/innodb/trx_mutex | 22756 | 1.9421 |
| wait/synch/mutex/innodb/rseg_mutex | 20333 | 3.6220 |
| wait/synch/mutex/innodb/dict_sys_mutex | 13422 | 2.2284 |
| wait/synch/mutex/innodb/mutex_list_mutex | 12694 | 344.1164 |
| wait/synch/mutex/innodb/fil_system_mutex | 9208 | 0.9542 |
| wait/synch/mutex/innodb/rw_lock_list_mutex | 8304 | 0.1794 |
| wait/synch/mutex/innodb/trx_undo_mutex | 6190 | 0.6801 |
| wait/synch/mutex/innodb/buf_pool_mutex | 2869 | 29.4623 |
| wait/synch/mutex/innodb/innobase_share_mutex | 2005 | 0.1349 |
| wait/synch/mutex/innodb/flush_list_mutex | 1274 | 0.1300 |
| wait/synch/mutex/innodb/file_format_max_mutex | 1016 | 0.0469 |
| wait/synch/mutex/innodb/purge_sys_bh_mutex | 1004 | 0.0326 |
| wait/synch/mutex/innodb/buf_dblwr_mutex | 640 | 0.0437 |
| wait/synch/mutex/innodb/log_flush_order_mutex | 437 | 0.0510 |
| wait/synch/mutex/innodb/recv_sys_mutex | 394 | 0.0202 |
| wait/synch/mutex/innodb/srv_sys_mutex | 169 | 0.5259 |
| wait/synch/mutex/innodb/lock_wait_mutex | 154 | 0.1172 |
| wait/synch/mutex/innodb/ibuf_mutex | 9 | 0.0027 |
| wait/synch/mutex/innodb/srv_innodb_monitor_mutex | 2 | 0.0009 |
| wait/synch/mutex/innodb/ut_list_mutex | 1 | 0.0001 |
| wait/synch/mutex/innodb/recv_writer_mutex | 1 | 0.0005 |
+--------------------------------------------------+------------+-------------------+
25 rows in set (0.01 sec)
Note
前面的结果集包括在启动过程中产生的 await 事件数据。要排除此数据,您可以在启动后和运行工作负载之前立即截断events_waits_summary_global_by_event_nametable。但是,截断操作本身可能会产生数量可忽略的 await 事件数据。
mysql> TRUNCATE performance_schema.events_waits_summary_global_by_event_name;