MySql语句性能问题定位--从sql语句到磁盘IO检查

一、背景

    本文只针对IO导致MySql性能问题的定位，其他如CPU、MySql参数配置、程序自身等问题需要进一步补充。原因某条sql建表语句运行了15秒 

二、步聚

Step1:

开启profiling

SET profiling =1;

关闭

SET profiling =off;

找到运行慢的sql语句ID

show profiles;

查看sql语句CPU/IO等耗时具体的量化数据

show profile CPU,SWAPS,BLOCK IO,MEMORY,CONTEXT SWITCHES,IPC,PAGE FAULTS,SOURCE for query 39;

结论：

从上图可见CPU耗时不多，反而IO操作占了大部分的耗时。下面让我们来找出服务器的哪些进程在占用IO资源。

Step2:

查看服务器linux IO：

iostat -x 1

找出哪些进程在疯狂的进行IO操作:

iotop

结论：

发现磁盘的读写都很高，而且svctm与await相差很大。

1，await的值远高于svctm的值，则表示I/O队列等待太长，系统上运行的应用程序将变慢。

2，%util项的值也是衡量磁盘I/O的一个重要指标，%util接近100%，表示磁盘产生的I/O请求太多，I/O系统已经满负荷的在工作，该磁盘可能存在瓶颈。

所以，确实是磁盘高IO操作，导致sql性能问题。而且是mysql本身IO特别高。

Setp3:

使用pt-ioprofile，查出哪些文件的IO操作高。命令如下，

下载连接：https://www.percona.com/doc/percona-toolkit/2.2/installation.html  

yum install  percona-toolkit-2.2.17-1.noarch.rpm

pt-ioprofile --profile-pid=44937 --cell=sizes

总结：

从上图可以看出，数据库中，某些表的读写操作是造成IO一直很高的主要原因，并严重影响了sql的查询性能。

定位到具体某个表、文件之后，就可以采取相应措施了。可以更换磁盘设备，使用更快的SSD磁盘，结合程序读写规则，选择合适的磁盘文件系统类型，或者从程序本身进行优化。