MySQL :: MySQL 8.3 Reference Manual :: 17.16.2 Monitoring InnoDB Mutex Waits Using Performance Schema

version 8.3

8.0 current
5.7

8.0 Japanese

MySQL 8.3 Reference Manual / ... / Monitoring InnoDB Mutex Waits Using Performance Schema

17.16.2 使用性能模式监控InnoDB互斥锁等待

互斥锁是一种同步机制，用于在代码中强制只有一个线程在给定的时间内可以访问公共资源。当两个或多个线程在服务器中需要访问同一个资源时，这些线程将相互竞争。第一个获得互斥锁的线程将导致其他线程等待，直到锁被释放。

对于instrumented的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互斥锁实例是在服务器启动时创建的，只有在服务器启动时启用相关仪表时才会被instrumented。为了确保所有InnoDB互斥锁实例都被instrumented和启用，请将以下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'
```
Note

具有更长前缀的规则，如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 |
+---------------------------------------------------------+------------+-------------------+

Note

前面的结果集包括在启动过程中产生的等待事件数据。要排除这些数据，可以在启动后立即截断 events_waits_summary_global_by_event_name 表，然后再运行您的工作负载。然而，截断操作本身可能会产生少量的等待事件数据。

mysql> TRUNCATE performance_schema.events_waits_summary_global_by_event_name;

PREV HOME UP NEXT