MySQL的`Performance Schema`：如何利用它监控`I/O`子系统的性能，如`wait/io/file`？ - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

好的，开始今天的讲座。

今天的主题是MySQL Performance Schema如何监控I/O子系统的性能，特别是如何利用wait/io/file instrumentation。Performance Schema是MySQL中一个强大的性能监控工具，它提供了对服务器内部操作的深入可见性，包括I/O操作。理解和利用Performance Schema对于诊断和优化MySQL服务器的性能至关重要。

Performance Schema简介

Performance Schema并非默认启用，你需要检查并启用它。可以通过以下SQL语句检查和启用：

-- 检查performance_schema是否启用
SHOW GLOBAL VARIABLES LIKE 'performance_schema';

-- 启用performance_schema (如果未启用)
-- 需要重启MySQL服务器才能生效
SET GLOBAL performance_schema = ON;

Performance Schema通过instrumentation来收集性能数据。Instrumentation是指在代码的关键点插入探针，用于记录特定事件的发生和持续时间。这些数据被存储在Performance Schema的表中，我们可以通过查询这些表来分析服务器的性能。

I/O Instrumentation：wait/io/file

wait/io/file instrumentation专注于监控文件I/O操作。它记录了线程在等待文件I/O完成时所花费的时间，以及相关的文件信息。这对于识别瓶颈以及确定哪些文件I/O操作是性能瓶颈非常有帮助。

重要的Performance Schema表

与wait/io/file instrumentation相关的几个关键表包括：

events_waits_summary_global_by_event_name: 汇总了所有事件的等待时间，按照事件名称分组。
events_waits_summary_by_thread_by_event_name: 汇总了每个线程的等待时间，按照事件名称分组。
events_waits_current: 显示当前正在等待的事件。
file_summary_by_event_name: 汇总了文件I/O操作，按照事件名称分组。
file_summary_by_instance: 汇总了文件I/O操作，按照文件实例分组。

启用wait/io/file Instrumentation

默认情况下，某些instrumentation可能未启用。我们需要确保wait/io/file instrumentation已经启用。可以通过以下方式检查和启用：

-- 检查wait/io/file instrumentation是否启用
SELECT NAME, ENABLED, TIMED FROM setup_instruments WHERE NAME LIKE 'wait/io/file%';

-- 启用wait/io/file instrumentation (如果未启用)
UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE 'wait/io/file%';

-- 检查wait/io/file consumers是否启用
SELECT NAME, ENABLED FROM setup_consumers WHERE NAME LIKE '%wait/io/file%';

-- 启用wait/io/file consumers (如果未启用)
UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%wait/io/file%';

监控文件I/O性能

现在，让我们看一些实际的查询，以监控文件I/O性能。

1. 找出总等待时间最长的文件I/O事件:

这个查询可以帮助我们识别哪些类型的I/O事件消耗了最多的时间。

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

这个查询的结果会显示不同类型的wait/io/file事件，它们的事件数量，总等待时间和平均等待时间。timer_wait 的单位是皮秒（picoseconds），需要转换为更友好的单位，例如毫秒(milliseconds)或秒(seconds)。

2. 找出等待时间最长的线程:

这个查询可以帮助我们确定哪些线程在等待文件I/O上花费了最多的时间。

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

--  获取线程信息
SELECT * FROM performance_schema.threads WHERE thread_id IN (SELECT thread_id FROM (SELECT thread_id FROM performance_schema.events_waits_summary_by_thread_by_event_name WHERE event_name LIKE 'wait/io/file%' GROUP BY thread_id ORDER BY SUM(timer_wait) DESC LIMIT 10) AS sub);

processlist_id 对应于 SHOW PROCESSLIST 命令中的 Id 列，可以用来进一步分析该线程的执行情况。

3. 找出最繁忙的文件:

这个查询可以帮助我们确定哪些文件正在被频繁地读取或写入。

SELECT
    file_name,
    event_name,
    COUNT(*) AS event_count,
    SUM(timer_wait) AS total_wait_time,
    SUM(timer_wait) / COUNT(*) AS avg_wait_time
FROM performance_schema.file_summary_by_instance
WHERE event_name LIKE 'wait/io/file%'
GROUP BY file_name, event_name
ORDER BY total_wait_time DESC
LIMIT 10;

file_name 列显示了文件的路径，event_name 列显示了I/O事件的类型（例如，wait/io/file/sql/MYI 表示等待MYI文件的I/O操作）。

4. 查看当前正在等待I/O的线程:

这个查询可以实时查看哪些线程正在等待I/O操作。

SELECT
    thread_id,
    event_name,
    object_name,
    object_instance_begin
FROM performance_schema.events_waits_current
WHERE event_name LIKE 'wait/io/file%';

object_name 列显示了正在访问的文件名，object_instance_begin 是文件实例的内存地址。

代码示例：将皮秒转换为毫秒或秒

Performance Schema中的时间单位是皮秒，我们需要将其转换为更易于理解的单位。以下是一些示例：

-- 将皮秒转换为毫秒
SELECT 123456789012345 / (1000000000000) AS milliseconds;

-- 将皮秒转换为秒
SELECT 123456789012345 / (1000000000000 * 1000) AS seconds;

案例分析：优化慢查询

假设我们发现查询执行缓慢，并且通过Performance Schema分析发现大量的I/O等待。以下是一个简化的案例分析：

识别慢查询: 使用慢查询日志或Performance Schema的events_statements_summary_global_by_digest表来识别执行时间长的查询。

SELECT
    DIGEST_TEXT,
    COUNT(*) AS exec_count,
    SUM(timer_wait) AS total_latency,
    AVG(timer_wait) AS avg_latency
FROM performance_schema.events_statements_summary_global_by_digest
ORDER BY total_latency DESC
LIMIT 10;

分析I/O等待: 对于慢查询，检查events_waits_summary_by_thread_by_event_name表，查看与执行慢查询的线程相关的I/O等待。

-- 假设你已经知道慢查询的线程ID (例如，通过SHOW PROCESSLIST)
SELECT
    event_name,
    SUM(timer_wait) AS total_wait_time
FROM performance_schema.events_waits_summary_by_thread_by_event_name
WHERE thread_id = <thread_id_of_slow_query>
AND event_name LIKE 'wait/io/file%'
GROUP BY event_name
ORDER BY total_wait_time DESC;

识别繁忙文件: 使用file_summary_by_instance表，确定哪些文件正在被频繁访问。

SELECT
    file_name,
    event_name,
    SUM(timer_wait) AS total_wait_time
FROM performance_schema.file_summary_by_instance
WHERE event_name LIKE 'wait/io/file%'
GROUP BY file_name, event_name
ORDER BY total_wait_time DESC
LIMIT 10;

优化措施: 基于分析结果，可以采取以下优化措施：
- 索引优化: 确保查询使用了正确的索引。如果查询缺少索引，MySQL可能需要进行全表扫描，导致大量的I/O操作。
- 查询重写: 优化查询语句，减少需要读取的数据量。
- 硬件升级: 如果I/O瓶颈是由于磁盘速度不足引起的，可以考虑升级到更快的存储设备（例如，SSD）。
- 参数调整: 调整MySQL的参数，例如innodb_buffer_pool_size，以提高缓存命中率，减少磁盘I/O。
- Schema优化: 优化数据库schema设计，例如，合理拆分表，减少单表数据量。

更深入的技巧

监控特定文件类型: 你可以使用LIKE 操作符来过滤特定类型的文件。例如，只监控InnoDB数据文件（.ibd）或日志文件。
结合其他Performance Schema instrumentation: wait/io/file instrumentation可以与其他instrumentation结合使用，以获得更全面的性能视图。例如，可以将I/O等待与锁等待（wait/lock%）结合起来分析，以确定锁竞争是否导致了I/O瓶颈。
使用监控工具: 可以使用各种MySQL监控工具（例如，Percona Monitoring and Management (PMM)，Prometheus + Grafana）来收集和可视化Performance Schema数据。这些工具可以帮助你更轻松地识别性能问题。
定期分析: Performance Schema数据应该定期分析，以便及时发现和解决性能问题。

注意事项

Performance Schema的开销: Performance Schema会带来一定的性能开销。虽然开销通常很小，但在生产环境中应该谨慎使用。确保只启用你需要的instrumentation。
数据清除: Performance Schema表中的数据是动态的，服务器重启后数据会丢失。如果需要长期保存性能数据，应该将数据定期导出到其他存储系统。
版本兼容性: Performance Schema的功能和表结构在不同的MySQL版本之间可能有所不同。在使用Performance Schema时，应该参考MySQL官方文档，了解特定版本的支持情况。
权限: 访问Performance Schema需要特定的权限。确保你具有足够的权限来查询Performance Schema表。

总结

Performance Schema的wait/io/file instrumentation是监控MySQL I/O子系统性能的强大工具。通过分析相关表中的数据，我们可以识别I/O瓶颈，确定哪些文件正在被频繁访问，以及哪些线程在等待I/O操作。基于这些信息，我们可以采取相应的优化措施，提高MySQL服务器的性能。

快速回顾几个关键点

Performance Schema 必须启用，并确认对应的 instrumentation 也启用。
多个表提供了不同维度的 I/O 监控数据，从全局到线程，再到文件实例。
分析数据时注意单位转换，并结合实际情况进行优化。

今天的讲座就到这里，感谢大家的参与！

发表回复 取消回复

发表回复取消回复