MySQL Performance Schema：利用 events_waits_summary_by_thread_by_event_name 表分析线程等待
大家好，今天我们来深入探讨 MySQL Performance Schema 中的 events_waits_summary_by_thread_by_event_name 表，学习如何利用它来诊断和解决数据库性能瓶颈，特别是与线程等待相关的性能问题。
Performance Schema 概述
Performance Schema 是 MySQL 5.5 版本引入的一个性能监控工具，它提供了一种机制来收集和分析服务器运行时的各种性能数据。这些数据可以帮助我们了解服务器的内部运作，识别性能瓶颈，并优化数据库配置和应用程序。Performance Schema 通过一系列的表来存储这些数据，这些表可以被查询，并根据不同的维度进行聚合和分析。
events_waits_summary_by_thread_by_event_name 表
events_waits_summary_by_thread_by_event_name 表是 Performance Schema 中一个非常重要的表，它按照线程和事件名称对等待事件进行汇总。这意味着它可以帮助我们了解每个线程在不同类型的等待事件上花费的时间。
- 线程 (Thread): 指 MySQL 服务器中执行任务的独立单元，例如一个客户端连接。
- 事件名称 (Event Name): 指等待事件的类型，例如等待 I/O 完成、等待锁释放等。
- 汇总 (Summary): 指对特定线程在特定事件名称上的等待时间进行聚合，包括总等待时间、平均等待时间、最大等待时间等。
表结构详解
events_waits_summary_by_thread_by_event_name 表的结构如下：
| 列名 | 数据类型 | 描述 |
|---|---|---|
| THREAD_ID | BIGINT UNSIGNED | 线程 ID。 |
| EVENT_NAME | VARCHAR(128) | 等待事件的名称。 例如 wait/io/file/sql/MYI (等待 MYI 文件 I/O)，wait/synch/mutex/sql/LOCK_open (等待 LOCK_open 互斥锁)。 |
| COUNT_STAR | BIGINT UNSIGNED | 等待事件发生的总次数。 |
| SUM_TIMER_WAIT | BIGINT UNSIGNED | 等待事件的总时间，单位是皮秒 (picoseconds)。 |
| MIN_TIMER_WAIT | BIGINT UNSIGNED | 等待事件的最短时间，单位是皮秒 (picoseconds)。 |
| AVG_TIMER_WAIT | BIGINT UNSIGNED | 等待事件的平均时间，单位是皮秒 (picoseconds)。 |
| MAX_TIMER_WAIT | BIGINT UNSIGNED | 等待事件的最长时间，单位是皮秒 (picoseconds)。 |
| SUM_NUMBER_OF_BYTES | BIGINT UNSIGNED | (一些事件适用) 等待事件涉及的总字节数。 例如，对于文件 I/O 事件，这表示读取或写入的总字节数。 |
| COUNT_READ | BIGINT UNSIGNED | (一些事件适用) 读取操作的次数。 |
| SUM_NUMBER_OF_BYTES_READ | BIGINT UNSIGNED | (一些事件适用) 读取的总字节数。 |
| COUNT_WRITE | BIGINT UNSIGNED | (一些事件适用) 写入操作的次数。 |
| SUM_NUMBER_OF_BYTES_WRITE | BIGINT UNSIGNED | (一些事件适用) 写入的总字节数。 |
| FIRST_SEEN | TIMESTAMP | 首次观察到该事件的时间戳。 |
| LAST_SEEN | TIMESTAMP | 最后一次观察到该事件的时间戳。 |
启用 Performance Schema
在开始之前，我们需要确保 Performance Schema 已经启用。 可以通过以下步骤启用 Performance Schema：
-
检查 Performance Schema 是否启用：
SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%wait%'; SELECT * FROM performance_schema.setup_consumers; -
如果未启用，修改
my.cnf(或my.ini) 文件：在
[mysqld]部分添加或修改以下行：performance_schema=ON performance_schema_instrument='wait/%'='ON' -
重启 MySQL 服务器。
-
验证 Performance Schema 是否已启用：
再次运行步骤 1 中的查询。
使用场景和示例
下面我们通过一些具体的例子，演示如何利用 events_waits_summary_by_thread_by_event_name 表来分析线程等待。
1. 查找总等待时间最长的线程
这个查询可以帮助我们找到哪些线程花费了最多的时间在等待上，从而找出潜在的瓶颈。
SELECT
THREAD_ID,
SUM(SUM_TIMER_WAIT) AS total_wait_time
FROM
performance_schema.events_waits_summary_by_thread_by_event_name
GROUP BY
THREAD_ID
ORDER BY
total_wait_time DESC
LIMIT 10;解释：
SUM(SUM_TIMER_WAIT): 计算每个线程所有等待事件的总等待时间。GROUP BY THREAD_ID: 按照线程 ID 进行分组。ORDER BY total_wait_time DESC: 按照总等待时间降序排序。LIMIT 10: 只显示前 10 个结果。
2. 查找特定线程的等待事件详情
如果我们已经知道某个线程存在性能问题，可以使用以下查询来查看该线程在不同等待事件上的花费时间。
SELECT
EVENT_NAME,
SUM_TIMER_WAIT,
COUNT_STAR
FROM
performance_schema.events_waits_summary_by_thread_by_event_name
WHERE
THREAD_ID = <thread_id> -- 替换为实际的线程 ID
ORDER BY
SUM_TIMER_WAIT DESC
LIMIT 10;解释：
WHERE THREAD_ID = <thread_id>: 过滤出指定线程 ID 的数据。ORDER BY SUM_TIMER_WAIT DESC: 按照等待时间降序排序。
3. 查找所有线程等待特定事件的详情
这个查询可以帮助我们了解哪些事件是导致线程等待的主要原因。例如，我们可以查找所有线程在等待锁释放上的花费时间。
SELECT
THREAD_ID,
SUM_TIMER_WAIT,
COUNT_STAR
FROM
performance_schema.events_waits_summary_by_thread_by_event_name
WHERE
EVENT_NAME LIKE 'wait/synch/mutex/innodb/%' -- 替换为实际的事件名称或模式
ORDER BY
SUM_TIMER_WAIT DESC
LIMIT 10;解释：
WHERE EVENT_NAME LIKE 'wait/synch/mutex/innodb/%': 过滤出事件名称以wait/synch/mutex/innodb/开头的数据，这些通常是 InnoDB 内部的互斥锁等待事件。 可以根据实际情况修改LIKE模式。
4. 分析文件 I/O 等待
如果发现文件 I/O 成为瓶颈，可以使用以下查询来分析哪些线程在等待文件 I/O。
SELECT
THREAD_ID,
EVENT_NAME,
SUM_NUMBER_OF_BYTES,
SUM_TIMER_WAIT,
COUNT_STAR
FROM
performance_schema.events_waits_summary_by_thread_by_event_name
WHERE
EVENT_NAME LIKE 'wait/io/file/%'
ORDER BY
SUM_TIMER_WAIT DESC
LIMIT 10;解释：
WHERE EVENT_NAME LIKE 'wait/io/file/%': 过滤出事件名称以wait/io/file/开头的数据，这些通常是文件 I/O 等待事件。SUM_NUMBER_OF_BYTES: 显示等待事件涉及的总字节数。
5. 转换为更易读的时间单位
由于 SUM_TIMER_WAIT 的单位是皮秒，为了方便阅读，我们可以将其转换为毫秒或秒。
SELECT
THREAD_ID,
EVENT_NAME,
SUM_TIMER_WAIT / 1000000000000 AS wait_seconds, -- 转换为秒
SUM_TIMER_WAIT / 1000000000 AS wait_milliseconds, -- 转换为毫秒
COUNT_STAR
FROM
performance_schema.events_waits_summary_by_thread_by_event_name
WHERE
EVENT_NAME LIKE 'wait/io/file/%'
ORDER BY
SUM_TIMER_WAIT DESC
LIMIT 10;常见的等待事件类型及其含义
了解常见的等待事件类型对于分析性能问题至关重要。 以下是一些常见的等待事件类型：
| 事件名称 | 含义 | 可能的原因 | 解决方案 |
|---|---|---|---|
wait/io/file/sql/MYI |
等待 MYI 文件的 I/O 操作完成 (MyISAM 表的索引文件)。 | 磁盘 I/O 瓶颈，大量 MyISAM 表的读写操作。 | 考虑将 MyISAM 表转换为 InnoDB 表，优化磁盘 I/O 性能，增加内存。 |
wait/io/file/sql/FRM |
等待 FRM 文件的 I/O 操作完成 (表的定义文件)。 | 磁盘 I/O 瓶颈，频繁创建或删除表。 | 优化磁盘 I/O 性能，减少表的创建和删除操作。 |
wait/io/file/innodb/innodb_data_file |
等待 InnoDB 数据文件的 I/O 操作完成。 | 磁盘 I/O 瓶颈，大量 InnoDB 表的读写操作，缓冲池不足。 | 优化磁盘 I/O 性能，增加 InnoDB 缓冲池的大小 (innodb_buffer_pool_size)，优化 SQL 查询。 |
wait/io/file/innodb/innodb_log_file |
等待 InnoDB 日志文件的 I/O 操作完成。 | 磁盘 I/O 瓶颈，大量的事务写入操作，日志文件过小。 | 优化磁盘 I/O 性能，增加 InnoDB 日志文件的大小 (innodb_log_file_size, innodb_log_files_in_group)，优化事务处理。 |
wait/synch/mutex/sql/LOCK_open |
等待 LOCK_open 互斥锁。 |
频繁打开和关闭表，表缓存不足。 | 增加 table_open_cache 的大小。 |
wait/synch/mutex/innodb/buf_pool_mutex |
等待 InnoDB 缓冲池互斥锁。 | 多个线程同时访问缓冲池，缓冲池竞争激烈。 | 增加 innodb_buffer_pool_instances 的数量（如果 innodb_buffer_pool_size 足够大），优化 SQL 查询。 |
wait/synch/mutex/innodb/log_sys_mutex |
等待 InnoDB 日志系统互斥锁。 | 多个线程同时写入日志，日志系统竞争激烈。 | 优化事务处理，减少日志写入量。 |
wait/synch/cond/sql/COND_thread_count |
等待线程计数条件变量。 | 服务器线程数达到上限。 | 增加 max_connections 的值，优化应用程序的连接管理。 |
wait/synch/rwlock/sql/LOCK_table_cache |
等待表缓存读写锁。 | 并发访问相同的表，表缓存竞争激烈。 | 优化 SQL 查询，减少对相同表的并发访问。 |
wait/lock/table/sql/handler |
等待表锁。 | 表锁冲突，例如多个线程同时尝试修改同一个表。 | 优化 SQL 查询，减少表锁的使用，考虑使用行锁 (InnoDB)。 |
wait/lock/metadata/sql/mdl |
等待元数据锁 (MDL)。 | 多个线程同时尝试修改表的结构，例如 ALTER TABLE 操作。 | 避免长时间的 ALTER TABLE 操作，尽量在业务低峰期执行。 |
实战案例分析
假设我们通过监控发现数据库服务器 CPU 使用率很高，并且怀疑存在 I/O 瓶颈。我们可以按照以下步骤进行分析：
-
查找总等待时间最长的线程：
SELECT THREAD_ID, SUM(SUM_TIMER_WAIT) AS total_wait_time FROM performance_schema.events_waits_summary_by_thread_by_event_name GROUP BY THREAD_ID ORDER BY total_wait_time DESC LIMIT 10;假设我们发现线程 ID 为 123 的线程等待时间最长。
-
查找线程 123 的等待事件详情：
SELECT EVENT_NAME, SUM_TIMER_WAIT / 1000000000 AS wait_seconds, COUNT_STAR FROM performance_schema.events_waits_summary_by_thread_by_event_name WHERE THREAD_ID = 123 ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;假设我们发现线程 123 花费了大量时间在
wait/io/file/innodb/innodb_data_file事件上。 -
分析文件 I/O 等待：
SELECT EVENT_NAME, SUM_NUMBER_OF_BYTES, SUM_TIMER_WAIT / 1000000000 AS wait_seconds, COUNT_STAR FROM performance_schema.events_waits_summary_by_thread_by_event_name WHERE THREAD_ID = 123 AND EVENT_NAME LIKE 'wait/io/file/innodb/innodb_data_file' ORDER BY SUM_TIMER_WAIT DESC;通过分析
SUM_NUMBER_OF_BYTES和wait_seconds，我们可以了解线程 123 在 InnoDB 数据文件上的 I/O 情况。 如果发现 I/O 量很大，并且等待时间很长，则可以确定存在 I/O 瓶颈。 -
解决方案：
- 优化磁盘 I/O 性能 (例如，使用 SSD)。
- 增加 InnoDB 缓冲池的大小 (innodb_buffer_pool_size)。
- 优化 SQL 查询，减少对 InnoDB 数据的访问。
注意事项
- Performance Schema 的开销： 启用 Performance Schema 会带来一定的性能开销，因为它需要收集和存储大量的性能数据。 在生产环境中，应该谨慎评估 Performance Schema 的开销，并根据实际需求进行配置。
- 数据清理： Performance Schema 中的数据是易失性的，服务器重启后数据会丢失。 如果需要长期保存性能数据，可以将数据导出到外部存储系统。 也可以配置 Performance Schema 的 history size，来保留更多历史数据。
- 数据解读：
events_waits_summary_by_thread_by_event_name表提供的是汇总数据，如果需要更详细的等待事件信息，可以使用events_waits_current和events_waits_history表。 - 灵活运用：
events_waits_summary_by_thread_by_event_name和其他 Performance Schema 表结合起来使用，可以更全面地了解服务器的性能状况。
结合 Performance Schema 和其他工具
为了更深入地分析性能问题，可以将 Performance Schema 与其他监控工具结合使用，例如：
- MySQL Enterprise Monitor: MySQL 官方提供的监控工具，可以提供更友好的图形界面和更强大的分析功能。
- 第三方监控工具: 例如 Prometheus, Grafana 等，可以自定义监控指标和报表。
- 操作系统工具: 例如
top,iostat,vmstat等，可以监控 CPU、内存、磁盘 I/O 等系统资源的使用情况.
总结：等待事件分析，性能瓶颈定位
通过 events_waits_summary_by_thread_by_event_name 表，我们可以深入了解 MySQL 线程的等待情况，从而定位性能瓶颈，并采取相应的优化措施。 结合实际案例，能够更好地理解和运用这个表，提升数据库性能。 结合其他工具和监控手段，可以实现全方位的性能监控和分析。