转自：mysql性能瓶颈-高IO定位分析
其实高IO也可能导致CPU高，因为磁盘I/O比较慢，会导致CPU一直等待磁盘I/O请求。分析数据库IO属于基本技能（毕竟大部分数据库调优到了极致，最后的瓶颈也可能会是IO，而且IO调优的难度会高一些）。


1、首先用万能的top命令查看进程

  1. [root@localhost ~]# top
  2. top - 11:53:04 up 702 days, 56 min,  1 user,  load average: 7.18, 6.70, 6.47
  3. Tasks: 576 total,   1 running, 575 sleeping,   0 stopped,   0 zombie
  4. Cpu(s):  7.7%us,  3.4%sy,  0.0%ni, 77.6%id, 11.0%wa,  0.0%hi,  0.3%si,  0.0%st
  5. Mem:  49374024k total, 32018844k used, 17355180k free,   115416k buffers
  6. Swap: 16777208k total,   117612k used, 16659596k free,  5689020k cached
  8. PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
  9. 14165 mysql     20   0 8822m 3.1g 4672 S 162.3  6.6  89839:59 mysqld
  10. 40610 mysql     20   0 25.6g  14g 8336 S 121.7 31.5 282809:08 mysqld
  11. 49023 mysql     20   0 16.9g 5.1g 4772 S  4.6 10.8   34940:09 mysqld

很明显是前面两个mysqld进程导致整体负载较高。

而且，从 Cpu(s) 这行的统计结果也能看的出来，%us 和 %wa 的值较高（us值整体不算高，但比sy高一点，不算高吧？），表示当前比较大的瓶颈可能是在用户进程消耗的CPU以及磁盘I/O等待上。


2、分析磁盘I/O的情况

执行 sar -d 1或（iostat -d -x -k 1）命令（每秒刷新） 确认磁盘I/O是否真的较大：

  1. [root@localhost ~]# sar -d 1
  2. Linux 2.6.32-431.el6.x86_64 (localhost.localdomain)     06/05/2020  _x86_64_        (8 CPU)
  3. 11:54:31 AM     DEV      tps    rd_sec/s    wr_sec/s    avgrq-sz   avgqu-sz  await     svctm   %util
  4. 11:54:32 AM    dev8-0   5338.00 162784.00   1394.00     30.76      5.24      0.98      0.19    100.00
  5. 11:54:33 AM    dev8-0   5134.00 148032.00  32365.00     35.14      6.93      1.34      0.19    100.10
  6. 11:54:34 AM    dev8-0   5233.00 161376.00    996.00     31.03      9.77      1.88      0.19    100.00
  7. 11:54:35 AM    dev8-0   4566.00 139232.00   1166.00     30.75      5.37      1.18      0.22    100.00
  8. 11:54:36 AM    dev8-0   4665.00 145920.00    630.00     31.41      5.94      1.27      0.21    100.00
  9. 11:54:37 AM    dev8-0   4994.00 156544.00    546.00     31.46      7.07      1.42      0.20    100.00

%util 达到或接近100%，说明产生的I/O请求太多，I/O系统已经满负荷。


3、再利用 iotop 确认到底哪些进程消耗的磁盘I/O资源最多：

  1. [root@localhost ~]# iotop
  2. Total DISK READ: 59.52 M/s | Total DISK WRITE: 598.63 K/s
  3. TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
  4. 16397 be/4 mysql       7.98 M/s    0.00 B/s  0.00 % 95.67 % mysqld --basedir=/usr/local/mysql5.7 --datadir=/usr/local/mysql5.7/data --port=3306
  5. 7295 be/4 mysql      9.95 M/s    0.00 B/s  0.00 % 93.72 % mysqld --basedir=/usr/local/mysql5.7 --datadir=/usr/local/mysql5.7/data --port=3306
  6. 14295 be/4 mysql      9.86 M/s    0.00 B/s  0.00 % 94.53 % mysqld --basedir=/usr/local/mysql5.7 --datadir=/usr/local/mysql5.7/data --port=3306
  7. 14288 be/4 mysql      13.38 M/s    0.00 B/s  0.00 % 92.21 % mysqld --basedir=/usr/local/mysql5.7 --datadir=/usr/local/mysql5.7/data --port=3306
  8. 14292 be/4 mysql      13.54 M/s    0.00 B/s  0.00 % 91.96 % mysqld --basedir=/usr/local/mysql5.7 --datadir=/usr/local/mysql5.7/data --port=3306

可以看到，端口号是3306的实例消耗的磁盘I/O资源比较多，那就看看这个实例里都有什么查询在跑。


4、可以用上面提到的SHOW PROCESSLIST方法，也可以用mysqladmin命令工具

我们需要看到当前都有哪些SQL在运行：
（以下用mysqladmin的方式，该命令mysql自带，可创建软链接方便调用，ln -s /usr/local/mysql/bin/mysqladmin /usr/bin）：

  1. [root@localhost ~]# mysqladmin -uroot -p123456 pr|grep -v Sleep
  2. +----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+
  3. | Id |User| Host     | db |Command|Time | State        | Info                                                                                          |
  4. +----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+
  5. | 25 |root| 172.16.1.133:45921 | db | Query | 68  | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>404612 order by Fvideoid) t1 |
  6. | 26 |root| 172.16.1.133:45923 | db | Query | 65  | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>484915 order by Fvideoid) t1 |
  7. | 28 |root| 172.16.1.133:45928 | db | Query | 130 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>404641 order by Fvideoid) t1 |
  8. | 27 |root| 172.16.1.133:45930 | db | Query | 167 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>324157 order by Fvideoid) t1 |
  9. | 36 |root| 172.16.1.133:45937 | db | Query | 174 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>324346 order by Fvideoid) t1 |
  10. +----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+

可以看到有不少慢查询还未完成，从slow query log中也能发现，这类SQL发生的频率很高。
这是一个非常低效的SQL写法，导致需要对整个主键进行扫描，但实际上只需要取得一个最大值而已，从slow query log中可看到：
Rows_sent: 1 Rows_examined: 5413058
每次都要扫描500多万行数据，却只为读取一个最大值，效率非常低。
经过分析，这个SQL稍做简单改造即可在个位数毫秒级内完成，提升了N次方。
改造的方法是：对查询结果做一次倒序排序，取得第一条记录即可。而原先的做法是对结果正序排序，取最后一条记录。
总结：mysql的IO分析思路挺简单，首先通过top关注%wa（指CPU等待磁盘写入完成的时间，平时为0，越高表示磁盘越忙）的波动是否较大；其次分析下磁盘I/O情况，并找到哪些进程占用IO资源最多；最后还是用SHOW PROCESSLIST或mysqladmin查看哪些语句的频繁调用在占用IO。