MySQL如何监控磁盘IO MySQL磁盘IO瓶颈的排查与优化-mysql教程-PHP中文网

mysql磁盘i/o成为瓶颈的核心原因是数据访问频繁超出内存承载能力，导致大量读写操作依赖磁盘，尤其在随机i/o、索引缺失、缓冲池过小、高并发写入、配置不当及硬件性能不足等多因素叠加下，i/o响应延迟升高，系统吞吐下降；2. 精准定位需结合操作系统工具：iostat -xdk 1用于监控%util、await、avgqu-sz等指标判断磁盘负载，vmstat 1通过wa、bi、bo观察cpu等待i/o和系统读写情况，sar提供历史趋势分析，确认i/o是否为性能瓶颈；3. mysql内部通过show engine innodb status查看“file i/o”中的pending aio reads/writes和“buffer pool”中脏页比例，结合information_schema.innodb_metrics查询innodb_buffer_pool_reads等计数器，以及performance_schema.file_summary_by_event_name分析文件级i/o等待，可深入识别i/o压力来源；4. 优化策略包括：硬件上升级ssd/nvme、配置raid 10并启用写缓存；mysql配置上合理设置innodb_buffer_pool_size、增大redo log、调整innodb_flush_log_at_trx_commit、配置innodb_io_capacity和innodb_flush_method为o_direct；sql层面通过索引优化、查询重写、批量操作减少无效i/o；操作系统层面选用xfs文件系统、挂载时使用noatime、禁用或调低swappiness以减少不必要的磁盘交互。

MySQL如何监控磁盘IO MySQL磁盘IO瓶颈的排查与优化

MySQL的磁盘I/O是数据库性能的关键瓶颈之一。要监控和排查它，我们通常会结合操作系统层面的工具（如

iostat

登录后复制

、

vmstat

登录后复制

）和MySQL内部的指标（如

SHOW ENGINE INNODB STATUS

登录后复制

、

performance_schema

登录后复制

）来全面分析。优化则需要从硬件、MySQL配置、SQL语句及操作系统设置等多维度入手。

解决方案

监控MySQL磁盘I/O，首先要从系统层面入手，观察整体I/O负载。我个人比较依赖

iostat

登录后复制

和

vmstat

登录后复制

这两个工具。

iostat -xdk 1

登录后复制

：这个命令能实时显示每个磁盘设备的I/O统计信息，我主要关注

%util

登录后复制

（磁盘利用率）、

await

登录后复制

（平均I/O等待时间）和

avgqu-sz

登录后复制

（平均队列长度）。如果

%util

登录后复制

长时间接近100%，或者

await

登录后复制

和

avgqu-sz

登录后复制

很高，那磁盘很可能就是瓶颈了。

vmstat 1

登录后复制

：这个命令则能提供更全面的系统概览，包括CPU、内存、进程和I/O。在I/O部分，我会看

bi

登录后复制

（每秒从块设备读取的块数）和

bo

登录后复制

（每秒写入到块设备的块数），以及

wa

登录后复制

（等待I/O的CPU百分比）。

wa

登录后复制

高通常意味着CPU在等待磁盘操作完成。

接着，我会深入到MySQL内部，查看其I/O相关的状态。

SHOW ENGINE INNODB STATUS\G

登录后复制

：这里面的“FILE I/O”部分非常重要，它会显示InnoDB正在进行的I/O请求数量，比如“pending aio reads”和“pending aio writes”。如果这些值持续很高，说明InnoDB有大量I/O操作在排队。另外，“BUFFER POOL AND MEMORY”里的“Dirty Pages”比例也需要关注，如果脏页过多且长时间不被刷写，会给I/O带来很大压力。

SELECT * FROM information_schema.innodb_metrics WHERE NAME LIKE '%io%';

登录后复制

：这个视图提供了更细粒度的InnoDB I/O计数器，可以帮助我们了解读写操作的具体次数和类型。

通过这些工具和指标的组合，我们就能初步判断磁盘I/O是否是当前性能问题的根源。

为什么MySQL的磁盘I/O会成为瓶颈？

谈到MySQL的磁盘I/O瓶颈，这几乎是数据库系统绕不开的一个话题。我经常遇到这样的情况：应用响应变慢，CPU使用率却不高，内存也看似充裕，但系统就是卡顿。这时候，直觉就会告诉我，多半是I/O在作祟。

其核心原因在于，数据库的本质就是数据的存储和检索。当数据量变得庞大，或者访问模式变得复杂时，内存（RAM）就无法完全容纳所有需要操作的数据。这时，MySQL就不得不频繁地与磁盘进行交互，将数据从磁盘读取到内存，或者将内存中的修改写回磁盘。

具体来说，有几个常见的原因会导致I/O成为瓶颈：

随机I/O的特性：关系型数据库，尤其是OLTP（在线事务处理）场景，其读写模式往往是高度随机的。比如，通过主键或索引查询一条记录，数据可能分散在磁盘的不同物理位置。这种随机读写对传统机械硬盘来说是性能杀手，因为磁头需要频繁寻道。即使是SSD，高并发的随机写也会带来不小的压力。
索引缺失或设计不当：这是最常见的问题之一。如果查询没有合适的索引，或者索引失效，MySQL就不得不进行全表扫描。这意味着需要读取大量不必要的数据页，直接导致磁盘I/O激增。
缓冲池（Buffer Pool）过小：InnoDB的缓冲池是其最重要的内存区域，用于缓存表数据和索引。如果缓冲池太小，无法容纳“热”数据，那么每次查询都可能需要从磁盘读取数据，导致大量的物理I/O。
高并发写入：大量的INSERT、UPDATE、DELETE操作会产生大量的日志（redo log, undo log），并导致数据页的修改。这些修改最终都需要刷写回磁盘，尤其是redo log的同步写入（
```
innodb_flush_log_at_trx_commit=1
```
登录后复制
时）会产生频繁的I/O操作。
操作系统和文件系统配置：底层的操作系统调度策略、文件系统的选择（ext4 vs XFS）、以及挂载选项（如
```
noatime
```
登录后复制
）都会直接影响磁盘I/O的性能。不合适的配置可能导致额外的开销。
硬件限制：最直接的原因就是磁盘本身的速度。使用老旧的机械硬盘、低速的RAID卡、或者没有正确配置的存储系统，都可能成为瓶颈。

我发现，很多时候问题不是单一的，而是多种因素交织在一起，共同把I/O推向极限。因此，排查时需要有全局观。

如何利用操作系统工具精准定位磁盘I/O问题？

当MySQL性能出现问题，我通常会先从操作系统层面入手，因为这能给我一个宏观的视图，快速判断问题是出在磁盘、CPU还是内存。精准定位磁盘I/O问题，我主要依赖以下几个工具：

iostat

登录后复制

：磁盘活动的详细报告

iostat

登录后复制

是我排查I/O问题时的首选工具。我通常会用

iostat -xdk 1

登录后复制

来实时监控。

```
-x
```
登录后复制
：显示扩展统计信息。
```
-d
```
登录后复制
：显示设备利用率报告。
```
-k
```
登录后复制
：以KB/s为单位显示速率。
```
1
```
登录后复制
：每秒刷新一次。

这个命令会输出每个磁盘设备（如

sda

登录后复制

sdb

登录后复制

）的详细信息。我关注的几个关键指标是：

```
r/s
```
登录后复制
(reads per second): 每秒完成的读请求数。
```
w/s
```
登录后复制
(writes per second): 每秒完成的写请求数。
```
rkB/s
```
登录后复制
(read KBytes per second): 每秒读取的KB数。
```
wkB/s
```
登录后复制
(write KBytes per second): 每秒写入的KB数。
```
await
```
登录后复制
(average wait time): 每个I/O请求的平均等待时间（包括队列时间和实际服务时间），单位是毫秒。这个值如果持续很高（比如几十甚至上百毫秒），就表明I/O响应很慢。
```
svctm
```
登录后复制
(average service time): 每个I/O请求的平均服务时间，单位是毫秒。这个值通常比较小，如果它和
```
await
```
登录后复制
相差很大，说明请求在队列中等待时间很长。
```
%util
```
登录后复制
(percentage of utilization): 磁盘的利用率。如果这个值长时间接近100%，意味着磁盘已经满负荷工作，成为了瓶颈。即使队列里还有很多请求，也无法及时处理。

通过

iostat

登录后复制

，我可以很快看到哪个磁盘设备最忙，它的读写模式是怎样的（是读多写少，还是读写均衡），以及I/O请求的响应速度如何。

vmstat

登录后复制

：系统资源的全面快照

vmstat

登录后复制

提供的是一个更全面的系统视图，包括进程、内存、交换、I/O和CPU活动。我常用

vmstat 1

登录后复制

来持续观察。

```
bi
```
登录后复制
(blocks in): 每秒从块设备读取的块数（通常是1KB/块）。
```
bo
```
登录后复制
(blocks out): 每秒写入到块设备的块数。
```
wa
```
登录后复制
(wait): CPU等待I/O完成的百分比。这是一个非常重要的指标。如果
```
wa
```
登录后复制
持续很高，比如20%以上，那就强烈暗示系统正在被磁盘I/O拖累，CPU有空闲但无事可做，因为它在等数据。

vmstat

登录后复制

能让我快速判断I/O是否是CPU瓶颈的根源，或者仅仅是I/O繁忙但CPU仍在高效工作。

sar

登录后复制

：历史数据和更细致的报告

sar

登录后复制

（System Activity Reporter）是一个强大的工具，可以收集、报告或保存系统活动信息。它能够提供历史数据，对于分析长时间的趋势和周期性问题非常有用。

```
sar -d 1
```
登录后复制
：显示每个设备的磁盘活动。
```
sar -b 1
```
登录后复制
：显示I/O和传输速率。

虽然我日常更多用

iostat

登录后复制

和

vmstat

登录后复制

做实时诊断，但

sar

登录后复制

在事后分析和趋势分析时是不可或缺的。例如，我可以回溯到某个性能问题发生的时间点，查看那时的I/O状况。

盘古大模型

华为云推出的一系列高性能人工智能大模型

查看详情

这些操作系统工具的强大之处在于，它们不依赖于MySQL本身的运行状态，能提供一个独立、客观的系统层面的I/O视图。如果系统层面的I/O已经很高，那么无论MySQL内部如何优化，都很难突破物理限制。

MySQL内部I/O指标有哪些，又该如何解读？

除了操作系统层面的监控，深入MySQL内部查看I/O相关的指标同样关键。这就像医生不仅要看病人的心跳血压，还要分析血液报告一样。MySQL内部的I/O指标能告诉我们InnoDB存储引擎在I/O层面具体做了什么，以及它当前面临的压力。

SHOW ENGINE INNODB STATUS

登录后复制

：InnoDB的“体检报告”

这是我最常用的InnoDB状态报告，虽然信息量巨大，但其中有几个部分与I/O密切相关：

```
FILE I/O
```
登录后复制
段：
- ```
pending aio reads
```
  登录后复制
  和
```
pending aio writes
```
  登录后复制
  ：这些表示当前正在等待完成的异步I/O请求数量。如果这些值持续很高，说明I/O子系统处理不过来，有大量请求在排队。
- ```
log file syncs
```
  登录后复制
  ：日志文件同步的次数。这个值如果非常高，可能意味着
```
innodb_flush_log_at_trx_commit
```
  登录后复制
  设置过于保守（例如设为1），导致每次事务提交都强制刷写redo log到磁盘，产生大量同步I/O。
- ```
inserts/s
```
  登录后复制
  ,
```
updates/s
```
  登录后复制
  ,
```
deletes/s
```
  登录后复制
  ,
```
reads/s
```
  登录后复制
  ：这些是每秒的DML和读操作计数，可以间接反映I/O的来源。
```
BUFFER POOL AND MEMORY
```
登录后复制
段：
- ```
Dirty Pages
```
  登录后复制
  ：缓冲池中已被修改但尚未刷写到磁盘的页数。如果脏页比例过高（比如超过75%），并且长时间不下降，这意味着后台的I/O刷写速度跟不上写入速度，可能会导致前台写入操作被阻塞，等待脏页刷写完成。
- ```
Free pages
```
  登录后复制
  ：缓冲池中空闲页的数量。如果这个值很低，说明缓冲池可能不够大。
- ```
Buffer pool hit rate
```
  登录后复制
  ：缓冲池命中率。这个值越高越好，如果命中率低，说明大部分数据都需要从磁盘读取，I/O压力自然就大。

information_schema.innodb_metrics

登录后复制

：更细粒度的I/O计数器

这个视图提供了非常多的InnoDB内部指标，包括I/O相关的。你可以通过查询它来获取更具体的I/O事件统计：

SELECT
    NAME,
    COUNT
FROM
    information_schema.innodb_metrics
WHERE
    NAME LIKE '%io%' OR NAME LIKE '%read%' OR NAME LIKE '%write%';

登录后复制

这里面有很多有用的计数器，例如：

```
innodb_buffer_pool_reads
```
登录后复制
：表示从磁盘读取到缓冲池的页数。
```
innodb_buffer_pool_read_requests
```
登录后复制
：表示从缓冲池请求读取的页数。两者对比可以计算缓冲池命中率。
```
innodb_data_reads
```
登录后复制
和
```
innodb_data_writes
```
登录后复制
：表示InnoDB存储引擎进行的物理读写次数。
```
innodb_data_fsyncs
```
登录后复制
：文件同步操作的次数。

通过这些指标，我可以更精确地了解是哪种类型的I/O操作在消耗资源，比如是数据页的读写多，还是日志的同步多。

performance_schema

登录后复制

：I/O事件的性能剖析

performance_schema

登录后复制

是MySQL 5.5+版本中一个强大的诊断工具，可以追踪各种事件，包括文件I/O事件。通过它，我们可以知道哪些文件（数据文件、日志文件等）产生了最多的I/O，以及它们的平均等待时间。

SELECT
    FILE_NAME,
    EVENT_NAME,
    COUNT_STAR,
    SUM_TIMER_WAIT / 1000000000 AS total_wait_s,
    AVG_TIMER_WAIT / 1000000000 AS avg_wait_s
FROM
    performance_schema.file_summary_by_event_name
WHERE
    EVENT_NAME LIKE '%wait/io/file/innodb%'
ORDER BY
    total_wait_s DESC
LIMIT 10;

登录后复制

这个查询可以帮助我识别哪些InnoDB文件（比如数据文件、redo log文件、undo log文件）是I/O热点。如果某个文件的

total_wait_s

登录后复制

特别高，那就说明对这个文件的I/O操作是主要的瓶颈。

这些内部数据，就像是MySQL在“自言自语”，告诉我们它最痛的地方。结合操作系统层面的数据，我们就能形成一个全面的I/O瓶颈分析图谱。

针对MySQL磁盘I/O瓶颈的常见优化策略

一旦通过监控工具定位到MySQL磁盘I/O是瓶颈，接下来的任务就是着手优化。优化不是一蹴而就的，往往需要多方面配合，有时甚至要大胆尝试。我通常会从以下几个层面考虑：

1. 硬件层面：直接提升物理能力

这是最直接也最有效的手段。

升级到SSD/NVMe固态硬盘：相比传统机械硬盘，SSD和NVMe在随机读写性能上有着质的飞跃，这对于数据库的随机I/O特性至关重要。我见过很多案例，仅仅是更换了存储介质，性能就得到了显著提升。
RAID配置：如果使用RAID，确保是RAID 10。RAID 10提供了良好的读写性能和数据冗余，比RAID 5更适合高I/O负载的数据库。同时，要确保RAID控制器带有电池备份单元（BBU）或非易失性缓存，这能保证缓存数据的安全性，并允许开启写缓存以提升性能。

2. MySQL配置层面：优化InnoDB行为

调整MySQL的配置参数，可以显著影响I/O行为。

innodb_buffer_pool_size
登录后复制
：这是最重要的参数。将其设置为系统内存的50%-70%（如果服务器只运行MySQL），确保“热”数据和索引尽可能地驻留在内存中，减少磁盘I/O。
innodb_log_file_size
登录后复制
和
innodb_log_files_in_group
登录后复制
：这两个参数决定了redo log文件的大小和数量。增大它们可以减少redo log的切换和刷盘频率，从而减少I/O。但也要注意，过大的日志文件会增加崩溃恢复的时间。
innodb_flush_log_at_trx_commit
登录后复制
：这个参数影响事务提交时redo log的刷盘策略。
- ```
1
```
  登录后复制
  （默认值）：每次事务提交都强制刷盘，最安全，但I/O开销最大。
- ```
0
```
  登录后复制
  ：每秒刷盘一次，性能最好，但可能丢失1秒内的数据。
- ```
2
```
  登录后复制
  ：每次事务提交写入操作系统缓存，每秒刷盘一次，折中方案。根据对数据安全性和性能的需求进行权衡。
innodb_io_capacity
登录后复制
和
innodb_io_capacity_max
登录后复制
：这些参数告诉InnoDB后台I/O线程可以使用的I/O操作能力。根据实际磁盘的IOPS能力来设置，可以帮助InnoDB更合理地进行脏页刷写和日志同步。
innodb_flush_method
登录后复制
：这个参数决定InnoDB如何进行文件I/O。
- ```
O_DIRECT
```
  登录后复制
  ：绕过操作系统的文件系统缓存，直接写入磁盘。这通常是推荐的设置，可以避免双重缓存，提高I/O效率，尤其是在有RAID卡缓存的情况下。
- ```
fsync
```
  登录后复制
  ：使用操作系统的
```
fsync
```
  登录后复制
  调用进行同步。
innodb_read_io_threads
登录后复制
和
innodb_write_io_threads
登录后复制
：InnoDB的I/O线程数。对于高并发场景，适当增加这些线程数可以提高I/O并行度。

3. SQL优化层面：减少不必要的I/O

再快的磁盘，也架不住无效的I/O。

索引优化：确保所有查询都使用了合适的索引，避免全表扫描。对慢查询进行分析，添加或优化索引是减少I/O最有效的方法之一。
查询重写：优化复杂的SQL语句，减少JOIN操作、子查询等可能导致大量I/O的操作。
批量操作：将零散的INSERT/UPDATE/DELETE操作合并为批量操作，减少事务提交次数和日志刷盘次数。
减少大事务：长时间运行的大事务会锁定资源，产生大量undo log，并增加redo log的刷写压力。

4. 操作系统层面：提供更好的I/O环境

文件系统选择：XFS通常在高性能和并发I/O方面表现优于ext4，对于数据库负载是更好的选择。
挂载选项：在挂载文件系统时，使用
```
noatime
```
登录后复制
或
```
relatime
```
登录后复制
选项，可以避免每次读取文件时都更新文件的访问时间，减少不必要的写I/O。
禁用SWAP：如果内存充足，尽量避免使用SWAP分区。SWAP会导致大量的磁盘I/O，严重拖慢数据库性能。可以将
```
vm.swappiness
```
登录后复制
设置为一个较小的值（如1或0）。