MySQL 8.4 Release Notes
互斥锁是一种同步机制，用于在代码中确保在任何给定时间只有一个线程可以访问公共资源。当在服务器中执行的两个或多个线程需要访问相同资源时，这些线程会相互竞争。第一个获取互斥锁的线程会导致其他线程等待，直到锁被释放。A mutex is a synchronization mechanism used in the code to enforce that only one thread at a given time can have access to a common resource. When two or more threads executing in the server need to access the same resource, the threads compete against each other. The first thread to obtain a lock on the mutex causes the other threads to wait until the lock is released.
对于已加仪表的 InnoDB 互斥锁，可以使用 性能模式（Performance Schema） 监控互斥锁等待。收集在性能模式表中的等待事件数据可以帮助识别等待次数最多或总等待时间最长的互斥锁。For InnoDB mutexes that are instrumented, mutex
waits can be monitored using
Performance Schema. Wait
event data collected in Performance Schema tables can help
identify mutexes with the most waits or the greatest total wait
time, for example.
下面的示例演示如何启用 InnoDB 互斥锁等待仪表，如何启用相关消费者，以及如何查询等待事件数据。The following example demonstrates how to enable
InnoDB mutex wait instruments, how to enable
associated consumers, and how to query wait event data.
要查看可用的
InnoDB互斥锁等待仪表，可查询性能模式setup_instruments表。所有InnoDB互斥锁等待仪表默认都是禁用的。To view availableInnoDBmutex wait instruments, query the Performance Schemasetup_instrumentstable. AllInnoDBmutex wait instruments are disabled by default.mysql>
SELECT *FROM performance_schema.setup_instrumentsWHERE 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互斥实例都被监控并启用，请在您的 MySQL 配置文件中添加以下performance-schema-instrument规则：SomeInnoDBmutex instances are created at server startup and are only instrumented if the associated instrument is also enabled at server startup. To ensure that allInnoDBmutex instances are instrumented and enabled, add the followingperformance-schema-instrumentrule to your MySQL configuration file:performance-schema-instrument='wait/synch/mutex/innodb/%=ON'
如果您不需要所有
InnoDB互斥的等待事件数据，可以通过在 MySQL 配置文件中添加额外的performance-schema-instrument规则来禁用特定仪器。例如，要禁用与全文搜索相关的InnoDB互斥等待事件仪器，请添加以下规则：If you do not require wait event data for allInnoDBmutexes, you can disable specific instruments by adding additionalperformance-schema-instrumentrules to your MySQL configuration file. For example, to disableInnoDBmutex wait event instruments related to full-text search, add the following rule:performance-schema-instrument='wait/synch/mutex/innodb/fts%=OFF'
Note具有较长前缀的规则，例如
wait/synch/mutex/innodb/fts%，优先于具有较短前缀的规则，例如wait/synch/mutex/innodb/%。Rules with a longer prefix such aswait/synch/mutex/innodb/fts%take precedence over rules with shorter prefixes such aswait/synch/mutex/innodb/%.将
performance-schema-instrument规则添加到配置文件后，重新启动服务器。除全文检索相关的锁之外，所有InnoDB互斥锁都已启用。要验证，请查询setup_instruments表。对于已启用的工具，其ENABLED和TIMED列应设置为YES。After adding theperformance-schema-instrumentrules to your configuration file, restart the server. All theInnoDBmutexes except for those related to full text search are enabled. To verify, query thesetup_instrumentstable. TheENABLEDandTIMEDcolumns should be set toYESfor the instruments that you enabled.mysql>
SELECT *FROM performance_schema.setup_instrumentsWHERE 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表来启用等待事件消费者。等待事件消费者默认是禁用的。Enable wait event consumers by updating thesetup_consumerstable. Wait event consumers are disabled by default.mysql>
UPDATE performance_schema.setup_consumersSET 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消费者应该已启用。You can verify that wait event consumers are enabled by querying thesetup_consumerstable. Theevents_waits_current,events_waits_history, andevents_waits_history_longconsumers should be enabled.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 负载仿真客户端来模拟工作负载。Once instruments and consumers are enabled, run the workload that you want to monitor. In this example, the mysqlslap load emulation client is used to simulate a workload.
$>
./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）汇总，事件名称是产生事件的工具的名称。汇总数据包括：Query the wait event data. In this example, wait event data is queried from theevents_waits_summary_global_by_event_nametable which aggregates data found in theevents_waits_current,events_waits_history, andevents_waits_history_longtables. Data is summarized by event name (EVENT_NAME), which is the name of the instrument that produced the event. Summarized data includes:COUNT_STAR汇总等待事件的数量。The number of summarized wait events.
SUM_TIMER_WAIT汇总计时等待事件的总等待时间。The total wait time of the summarized timed wait events.
MIN_TIMER_WAIT汇总计时等待事件的最短等待时间。The minimum wait time of the summarized timed wait events.
AVG_TIMER_WAIT汇总计时等待事件的平均等待时间。The average wait time of the summarized timed wait events.
MAX_TIMER_WAIT汇总计时等待事件的最长等待时间。The maximum wait time of the summarized timed wait events.
以下查询返回仪器名称（
EVENT_NAME）、等待事件的数量（COUNT_STAR）以及该仪器事件的总等待时间（SUM_TIMER_WAIT）。由于等待默认以皮秒（万亿分之一秒）为单位，因此等待时间除以 1000000000 以显示为毫秒。数据按汇总的等待事件数量（COUNT_STAR）降序显示。你可以调整ORDER BY子句，以按总等待时间对数据进行排序。The following query returns the instrument name (EVENT_NAME), the number of wait events (COUNT_STAR), and the total wait time for the events for that instrument (SUM_TIMER_WAIT). Because waits are timed in picoseconds (trillionths of a second) by default, wait times are divided by 1000000000 to show wait times in milliseconds. Data is presented in descending order, by the number of summarized wait events (COUNT_STAR). You can adjust theORDER BYclause to order the data by total wait time.mysql>
SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1000000000 SUM_TIMER_WAIT_MSFROM performance_schema.events_waits_summary_global_by_event_nameWHERE 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前面的结果集包括在启动过程中产生的等待事件数据。要排除此数据，您可以在启动后且运行工作负载之前立即截断
events_waits_summary_global_by_event_name表。然而，截断操作本身可能会产生极少量的等待事件数据。The preceding result set includes wait event data produced during the startup process. To exclude this data, you can truncate theevents_waits_summary_global_by_event_nametable immediately after startup and before running your workload. However, the truncate operation itself may produce a negligible amount wait event data.mysql>
TRUNCATE performance_schema.events_waits_summary_global_by_event_name;