15.16.2 使用Performance Schema监控InnoDB表的锁等待

mutex是用来保证在同一时刻只能有一个线程访问同一资源的同步机制。当在服务器中有两个或更多线程需要访问相同资源时，这些线程之间相互竞争。一个线程获取到了mutex上的锁之后，其它线程会等待，直到锁被释放。

对于组件化/工具化的InnoDB的mutex来说，可以使用Performance Schema来监控mutex等待。例如，Performance Schema表中收集的事件数据可以帮助你阻塞线程最多的mutex，或等待时间最长的mutex。

下面的例子展示了怎样启用InnoDB的mutex等待组件，怎样启用相关消费者，以及怎样查询等待事件数据。

要查看可用的InnoDB mutex等待组件，查询Performance Schema 中的setup_instruments表。默认情况下，所有的InnoDB mutex等待组件都被禁用。

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_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 mutex实例在服务启动阶段被创建，并且只有在其它相关组件也在服务启动阶段被启用的情况下，才会展示（这里翻译的有些问题？？？）。要保证所有的InnoDB mutex实例可测量并且被启用，在MySQL配置文件中添加规则performance-schema-instrument。

performance-schema-instrument='wait/synch/mutex/innodb/%=ON'

如果你不需要获取所有 InnoDB mutex的等待事件数据，你可以在MySQL配置文件中添加performance-schema-instrument规则来禁用特定组件。

例如，要禁用与全文索引相关的InnoDB mutex等待事件组件，添加下面的规则：

performance-schema-instrument='wait/synch/mutex/innodb/fts%=OFF'

具有较长前缀的规则（比如 wait/synch/mutex/innodb/fts%）比具有较短前缀的规则（比如 wait/synch/ mutex/innodb/%）优先级更高。

在配置文件中添加performance-schema-instrument规则后，重启MySQL服务。除与全文索引相关的所有的InnoDB mutex等待组件都会被启用。想要验证这一点，查询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;

mysqlslap是mysql自带的压力测试工具，详见：https://dev.mysql.com/doc/refman/8.0/en/mysqlslap.html。

查询等待事件数据。在下例中，从events_waits_summary_global_by_event_name表中查询等待事件数据，这张表会聚合events_waits_current,events_waits_history,和events_waits_history_long表中的数据。数据被根据事件名称来总结数据。事件名称是产生事件的组件的名称。被总结的数据包括：

COUNT_STAR 总结的等待事件数量。
SUM_TIMER_WAIT 总结的等待事件的等待时间之和。
MIN_TIMER_WAIT 总结的等待事件的最小等待时间。
AVG_TIMER_WAIT 总结的等待事件的平均等待时间。
MAX_TIMER_WAIT 总结的等待事件的最大等待时间。

下面的查询返回事件名称（EVENT_NAME）,等待事件数量（COUNT_STAR），以及等待这个组件的等待时间之和（SUM_TIMER_WAIT）。因为默认情况下等待时间的计时单位是皮秒，等待时间展示时被除以 1000000000，以毫秒为单位展示。数据被倒序方式展示，依据的是总结的等待事件数量（COUNTER_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 |
+---------------------------------------------------------+------------+-------------------+

上面的结果集中包括启动过程中产生的等待事件数据。要排除这些数据，你可以在启动后马上清空events_waits_summary_global_by_event_name表，然后再运行工作负载。然而，清空操作本身也会产生一些等待事件数据。
mysql> TRUNCATE performance_schema.events_waits_summary_global_by_event_name;