14.17.2 使用性能模式监视 InnoDB Mutexawait

互斥锁是代码中使用的一种同步机制,用于强制在给定时间只有一个线程可以访问公共资源。当服务器中执行的两个或更多线程需要访问同一资源时,这些线程会相互竞争。在互斥锁上获得锁的第一个线程导致其他线程 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。对于启用的工具,ENABLEDTIMED列应设置为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

您可以通过查询setup_consumerstable 来验证是否启用了 await 事件使用者。应当启用events_waits_currentevents_waits_historyevents_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负载模拟 Client 端用于模拟工作负载。
shell> ./mysqlslap --auto-generate-sql --concurrency=100 --iterations=10
       --number-of-queries=1000 --number-char-cols=6 --number-int-cols=6;

汇总的 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;