MySQL 8.4 Release Notes
17.16.2 使用性能模式监控 InnoDB 互斥锁等待
互斥锁是一种同步机制，用于在代码中确保只有一个线程在给定的时间内可以访问公共资源。当两个或多个执行在服务器上的线程需要访问同一个资源时，这些线程之间会竞争。第一个获得互斥锁的线程将导致其他线程等待直到锁被释放。
对于已instrumented的InnoDB互斥锁，可以使用性能模式来监控互斥锁等待。Performance Schema表中的等待事件数据可以帮助识别最多等待或总等待时间最长的互斥锁，例如。
以下示例演示了如何启用InnoDB互斥锁等待instrument、启用关联的消费者和查询等待事件数据。
-
要查看可用的
InnoDB互斥锁等待instrument，可以查询Performance Schemasetup_instruments表。所有InnoDB互斥锁等待instrument都默认禁用。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互斥锁实例是在服务器启动时创建的，并且只有在关联的instrument也启用时才会被instrument。要确保所有InnoDB互斥锁实例都被instrument和启用，可以将以下performance- schema-instrument规则添加到您的MySQL配置文件中：performance-schema-instrument='wait/synch/mutex/innodb/%=ON'如果您不需要为所有
InnoDB互斥体获取等待事件数据，可以通过在MySQL配置文件中添加额外的performance-schema-instrument规则来禁用特定的仪器。例如，要禁用与全文搜索相关的InnoDB互斥体等待事件仪器，可以添加以下规则：performance-schema-instrument='wait/synch/mutex/innodb/fts%=OFF'Note具有更长前缀的规则，如
wait/synch/mutex/innodb/fts%，将优先于具有短前缀的规则，如wait/synch/mutex/innodb/%。在添加
performance-schema-instrument规则到配置文件后，重新启动服务器。所有except for those related to full text search的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）。由于默认情况下等待是以-picoseconds（十亿分之一秒）计时的，因此等待时间被除以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 | +---------------------------------------------------------+------------+-------------------+Note前面的结果集包括启动过程中生成的等待事件数据。要排除这些数据，可以在启动后立即truncate
events_waits_summary_global_by_event_name表，并在运行工作负载前执行。然而，truncate操作本身可能会生成一些可忽略的等待事件数据。mysql> TRUNCATE performance_schema.events_waits_summary_global_by_event_name; -