MySQL的`性能架构`：如何利用`events_waits`表分析`I/O`、`CPU`、`Lock`等不同类型的等待事件？ - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL 性能分析：利用 `events_waits` 表深入理解等待事件

大家好，今天我们来深入探讨 MySQL 的性能架构，并重点讲解如何利用 events_waits 表来分析 I/O、CPU、Lock 等不同类型的等待事件。events_waits 表是 Performance Schema 的核心组件，它记录了线程在执行过程中发生的各种等待事件，是性能调优的重要依据。

一、MySQL Performance Schema 简介

Performance Schema 是 MySQL 5.5 及更高版本引入的性能监控工具，用于收集服务器运行时的性能数据。它提供了一组表，用于记录各种事件，包括：

等待事件 (Wait Events): 线程等待资源的时间，例如 I/O、Lock、CPU 等。
阶段事件 (Stage Events): 线程执行的阶段，例如初始化、优化、执行等。
语句事件 (Statement Events): 线程执行的 SQL 语句。
事务事件 (Transaction Events): 事务的开始、提交、回滚等。

通过分析这些事件，我们可以了解 MySQL 服务器的性能瓶颈，并采取相应的优化措施。

要使用 Performance Schema，你需要确保它已启用。可以通过以下方式检查和启用：

-- 检查 Performance Schema 是否启用
SELECT @@performance_schema;

-- 如果未启用，则启用 Performance Schema
UPDATE performance_schema.setup_instruments SET enabled = 'YES' WHERE name LIKE '%wait%';
UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name LIKE '%wait%';

注意: 启用 Performance Schema 会增加服务器的开销，因此建议在生产环境中谨慎使用，并根据实际需要进行配置。

二、`events_waits` 表结构详解

events_waits 表记录了线程等待事件的详细信息。它的重要字段包括：

列名	数据类型	描述
`THREAD_ID`	BIGINT	线程 ID。
`EVENT_ID`	BIGINT	事件 ID，用于唯一标识一个等待事件。
`END_EVENT_ID`	BIGINT	事件结束 ID。如果是当前等待事件，则为 NULL。
`EVENT_NAME`	VARCHAR	等待事件的名称，例如 `wait/io/file/sql/data`、`wait/synch/mutex/innodb/lock_sys_mutex` 等。
`SOURCE`	VARCHAR	产生等待事件的源代码文件和行号。
`TIMER_START`	BIGINT	等待事件开始的时间，以皮秒 (picosecond) 为单位。
`TIMER_END`	BIGINT	等待事件结束的时间，以皮秒 (picosecond) 为单位。如果是当前等待事件，则为 NULL。
`TIMER_WAIT`	BIGINT	等待事件持续的时间，以皮秒 (picosecond) 为单位。 `TIMER_END` – `TIMER_START`。如果是当前等待事件，则为 NULL。
`SPINS`	BIGINT	自旋的次数。主要用于自旋锁的等待事件。
`OBJECT_SCHEMA`	VARCHAR	等待事件相关的数据库名。
`OBJECT_NAME`	VARCHAR	等待事件相关的对象名，例如表名。
`INDEX_NAME`	VARCHAR	等待事件相关的索引名。
`OBJECT_TYPE`	VARCHAR	等待事件相关的对象类型，例如 `TABLE`、`INDEX` 等。
`OBJECT_INSTANCE_BEGIN`	BIGINT	对象实例的起始地址。
`NESTING_EVENT_ID`	BIGINT	嵌套事件 ID。用于表示等待事件是否嵌套在其他事件中。
`NESTING_EVENT_TYPE`	ENUM	嵌套事件的类型，例如 `STATEMENT`、`STAGE` 等。
`OPERATION`	VARCHAR	等待事件相关的操作，例如 `read`, `write`, `flush`等。
`NUMBER_OF_BYTES`	BIGINT	等待事件相关的字节数。
`FLAGS`	ENUM	等待事件相关的标志位。

三、分析不同类型的等待事件

events_waits 表记录了各种类型的等待事件，我们可以根据 EVENT_NAME 字段来区分不同的事件类型。

1. I/O 等待事件

I/O 等待事件通常与磁盘 I/O 操作相关，例如读取或写入数据文件。常见的 I/O 等待事件包括：

wait/io/file/sql/data: 等待读取或写入表数据文件。
wait/io/file/sql/index: 等待读取或写入索引文件。
wait/io/file/innodb/innodb_data: 等待读取或写入 InnoDB 数据文件。
wait/io/file/innodb/innodb_log: 等待读取或写入 InnoDB 日志文件。

示例：查询 I/O 等待事件

SELECT
    EVENT_NAME,
    COUNT(*) AS event_count,
    SUM(TIMER_WAIT) AS total_wait_time,
    AVG(TIMER_WAIT) AS avg_wait_time
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'wait/io%'
ORDER BY total_wait_time DESC
LIMIT 10;

这个查询会显示 I/O 等待事件的统计信息，包括事件名称、事件数量、总等待时间和平均等待时间。通过分析这些数据，我们可以找出 I/O 瓶颈所在。

示例：分析特定表的 I/O 等待

SELECT
    OBJECT_NAME,
    COUNT(*) AS event_count,
    SUM(TIMER_WAIT) AS total_wait_time,
    AVG(TIMER_WAIT) AS avg_wait_time
FROM performance_schema.events_waits_history_long
WHERE EVENT_NAME LIKE 'wait/io/file/sql/data'
AND OBJECT_SCHEMA = 'your_database_name'
AND OBJECT_NAME = 'your_table_name'
GROUP BY OBJECT_NAME
ORDER BY total_wait_time DESC
LIMIT 10;

将 your_database_name 和 your_table_name 替换为实际的数据库名和表名。此查询将帮助你了解特定表的 I/O 等待情况。

优化 I/O 等待:

优化 SQL 查询: 确保查询使用了正确的索引，避免全表扫描。
调整 InnoDB 缓冲池大小: 增加 innodb_buffer_pool_size 的值，可以减少磁盘 I/O。
使用 SSD 存储: SSD 存储比传统机械硬盘具有更快的读写速度。
优化磁盘 I/O 调度: 可以使用 ionice 命令调整 I/O 优先级。

2. CPU 等待事件

CPU 等待事件通常与线程等待 CPU 资源相关。常见的 CPU 等待事件包括：

wait/synch/mutex/sql/TC_LOG_MMAP: 等待获取事务协调器的日志映射互斥锁。
wait/synch/mutex/sql/LOCK_thd_data: 等待获取线程数据的互斥锁。
wait/synch/cond/sql/COND_thread_count: 等待线程数量达到某个阈值。

示例：查询 CPU 等待事件

SELECT
    EVENT_NAME,
    COUNT(*) AS event_count,
    SUM(TIMER_WAIT) AS total_wait_time,
    AVG(TIMER_WAIT) AS avg_wait_time
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'wait/synch%'
ORDER BY total_wait_time DESC
LIMIT 10;

这个查询会显示同步相关的等待事件，其中可能包含 CPU 等待。

示例：分析长时间运行的查询导致的 CPU 等待

SELECT
    processlist_id,
    SUM(timer_wait) AS total_wait_time
FROM performance_schema.events_waits_history_long
WHERE event_name LIKE 'wait/synch%'
GROUP BY processlist_id
ORDER BY total_wait_time DESC
LIMIT 10;

-- 查找对应的 SQL 语句
SELECT * FROM information_schema.PROCESSLIST WHERE ID = <processlist_id>;

将 <processlist_id> 替换为上一个查询结果中的 processlist_id。这将帮助你找到导致 CPU 等待的 SQL 语句。

优化 CPU 等待:

优化 SQL 查询: 避免复杂的 SQL 查询，减少 CPU 消耗。
增加 CPU 核心数: 增加服务器的 CPU 核心数，可以提高并发处理能力。
优化线程池配置: 合理配置线程池大小，避免线程过多或过少。
使用缓存: 使用查询缓存或应用程序缓存，可以减少 CPU 消耗。

3. Lock 等待事件

Lock 等待事件通常与线程等待获取锁资源相关。常见的 Lock 等待事件包括：

wait/lock/table/sql/handler: 等待获取表锁。
wait/lock/metadata/sql/mdl: 等待获取元数据锁 (MDL)。
wait/synch/mutex/innodb/lock_sys_mutex: 等待获取 InnoDB 锁系统互斥锁。
wait/lock/innodb/row_lock: 等待获取 InnoDB 行锁。

示例：查询 Lock 等待事件

SELECT
    EVENT_NAME,
    COUNT(*) AS event_count,
    SUM(TIMER_WAIT) AS total_wait_time,
    AVG(TIMER_WAIT) AS avg_wait_time
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'wait/lock%'
ORDER BY total_wait_time DESC
LIMIT 10;

这个查询会显示 Lock 等待事件的统计信息。

示例：分析 InnoDB 行锁等待

SELECT
    OBJECT_NAME,
    COUNT(*) AS event_count,
    SUM(TIMER_WAIT) AS total_wait_time,
    AVG(TIMER_WAIT) AS avg_wait_time
FROM performance_schema.events_waits_history_long
WHERE EVENT_NAME LIKE 'wait/lock/innodb/row_lock'
AND OBJECT_SCHEMA = 'your_database_name'
GROUP BY OBJECT_NAME
ORDER BY total_wait_time DESC
LIMIT 10;

将 your_database_name 替换为实际的数据库名。此查询将帮助你了解哪些表存在严重的行锁竞争。

优化 Lock 等待:

优化事务设计: 尽量缩短事务的执行时间，避免长时间持有锁。
减少锁的粒度: 避免使用表锁，尽量使用行锁。
使用乐观锁: 在某些情况下，可以使用乐观锁来避免锁竞争。
避免死锁: 确保事务按照相同的顺序访问资源，避免死锁发生。
分析死锁日志: MySQL 会将死锁信息记录到错误日志中，可以分析死锁日志来找出死锁原因。SHOW ENGINE INNODB STATUS; 可以显示最新的死锁信息。

4. 其他等待事件

除了 I/O、CPU 和 Lock 等待事件，events_waits 表还记录了其他类型的等待事件，例如：

Memory 等待事件: 等待分配内存。
Network 等待事件: 等待网络 I/O。
Plugin 等待事件: 等待插件执行。

我们可以根据 EVENT_NAME 字段来区分不同的事件类型，并根据实际情况进行分析和优化。

四、`events_waits` 表的不同变体

Performance Schema 提供了 events_waits 表的不同变体，用于满足不同的分析需求：

events_waits_current: 包含当前正在发生的等待事件。
events_waits_history: 包含最近发生的等待事件的历史记录 (默认大小有限制)。
events_waits_history_long: 包含更长时间的等待事件的历史记录（可能需要手动配置）。
events_waits_summary_global_by_event_name: 按事件名称汇总的全局等待事件统计信息。
events_waits_summary_by_thread_by_event_name: 按线程和事件名称汇总的等待事件统计信息。
events_waits_summary_by_account_by_event_name: 按账户和事件名称汇总的等待事件统计信息。
events_waits_summary_by_host_by_event_name: 按主机和事件名称汇总的等待事件统计信息。
events_waits_summary_by_user_by_event_name: 按用户和事件名称汇总的等待事件统计信息。

选择合适的表变体可以提高查询效率，并更好地满足分析需求。

五、使用视图简化查询

为了简化 events_waits 表的查询，我们可以创建自定义视图。例如，我们可以创建一个视图来显示 I/O 等待事件的统计信息：

CREATE VIEW io_wait_summary AS
SELECT
    OBJECT_SCHEMA AS db,
    OBJECT_NAME AS table_name,
    COUNT(*) AS wait_count,
    SUM(TIMER_WAIT) AS total_wait_time,
    AVG(TIMER_WAIT) AS avg_wait_time
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'wait/io%'
GROUP BY OBJECT_SCHEMA, OBJECT_NAME
ORDER BY total_wait_time DESC;

SELECT * FROM io_wait_summary LIMIT 10;

通过创建视图，我们可以将复杂的查询逻辑封装起来，并简化后续的分析工作。

六、调整 Performance Schema 配置

Performance Schema 的配置会影响其性能开销和数据收集的粒度。可以通过修改 setup_instruments 和 setup_consumers 表来调整 Performance Schema 的配置。

setup_instruments: 用于配置要收集的事件类型。
setup_consumers: 用于配置事件数据的存储方式。

例如，我们可以禁用某些不感兴趣的事件类型，以减少 Performance Schema 的开销：

UPDATE performance_schema.setup_instruments SET enabled = 'NO' WHERE name LIKE '%idle%';

注意: 修改 Performance Schema 的配置需要谨慎，并根据实际需要进行调整。不当的配置可能会导致性能下降或数据丢失。

七、结合其他 Performance Schema 表进行分析

events_waits 表可以与其他 Performance Schema 表结合使用，以进行更深入的性能分析。例如，可以将 events_waits 表与 events_statements_history_long 表结合使用，以分析导致等待事件的 SQL 语句。

SELECT
    ews.EVENT_NAME,
    ess.SQL_TEXT,
    ews.TIMER_WAIT
FROM performance_schema.events_waits_history_long ews
JOIN performance_schema.events_statements_history_long ess
ON ews.THREAD_ID = ess.THREAD_ID
AND ews.EVENT_ID = ess.EVENT_ID
WHERE ews.EVENT_NAME LIKE 'wait/lock/innodb/row_lock'
ORDER BY ews.TIMER_WAIT DESC
LIMIT 10;

这个查询会显示导致 InnoDB 行锁等待的 SQL 语句。通过分析这些 SQL 语句，我们可以找出性能瓶颈所在，并采取相应的优化措施。

八、基于等待事件进行更高效的数据库优化

使用 events_waits 表进行分析，可以帮助我们识别数据库的性能瓶颈，并采取相应的优化措施。关键点在于：

明确性能目标： 确定需要优化的性能指标，例如响应时间、吞吐量等。
收集数据： 使用 Performance Schema 收集等待事件数据。
分析数据： 分析等待事件数据，找出性能瓶颈所在。
制定优化方案： 根据性能瓶颈，制定相应的优化方案。
实施优化方案： 实施优化方案，并监控性能指标。
持续优化： 持续监控性能指标，并根据实际情况进行调整。

等待事件分析是数据库性能优化的重要工具

我们学习了如何利用MySQL 的 events_waits 表来分析 I/O、CPU、Lock 等不同类型的等待事件。通过分析这些事件，我们可以了解 MySQL 服务器的性能瓶颈，并采取相应的优化措施，从而提升数据库的整体性能。

MySQL 性能分析：利用 events_waits 表深入理解等待事件

一、MySQL Performance Schema 简介

二、events_waits 表结构详解

三、分析不同类型的等待事件

1. I/O 等待事件

2. CPU 等待事件

3. Lock 等待事件

4. 其他等待事件

四、events_waits 表的不同变体

五、 使用视图简化查询

六、 调整 Performance Schema 配置

七、 结合其他 Performance Schema 表进行分析

八、 基于等待事件进行更高效的数据库优化