mysql latency timer_MySql 5.7 中文文档 - 14.17.2 使用性能模式监视 InnoDB Mutexawait | Docs4dev...

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

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 事件数据。在此示例中，从events_waits_summary_global_by_event_nametable 中查询 await 事件数据，该 table 汇总了在events_waits_current，events_waits_history和events_waits_history_longtable 中找到的数据。数据通过事件名称(EVENT_NAME)进行汇总，事件名称是产生该事件的仪器的名称。汇总数据包括：

COUNT_STAR

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