MySQL性能瓶颈排查定位实例详解
本文实例讲述了MySQL性能瓶颈排查定位的方法。分享给大家供大家参考，具体如下：
导读
从一个现场说起，全程解析如何定位性能瓶颈。
排查过程
收到线上某业务后端的MySQL实例负载比较高的告警信息，于是登入服务器检查确认。
1.首先我们进行OS层面的检查确认
登入服务器后，我们的目的是首先要确认当前到底是哪些进程引起的负载高，以及这些进程卡在什么地方，瓶颈是什么。
通常来说，服务器上最容易成为瓶颈的是磁盘I/O子系统，因为它的读写速度通常是最慢的。即便是现在的PCIeSSD，其随机I/O读写速度也是不如内存来得快。当然了，引起磁盘I/O慢得原因也有多种，需要确认哪种引起的。
第一步，我们一般先看整体负载如何，负载高的话，肯定所有的进程跑起来都慢。
可以执行指令w或者sar-q1来查看负载数据，例如：
[yejr@imysql.com:~]#w 11:52:58up702days,56min,1user,loadaverage:7.20,6.70,6.47 USERTTYFROMLOGIN@IDLEJCPUPCPUWHAT rootpts/01.xx.xx.xx11:510.00s0.03s0.00sw
或者sar-q的观察结果：
[yejr@imysql.com:~]#sar-q1 Linux2.6.32-431.el6.x86_64(yejr.imysql.com)01/13/2016_x86_64_(24CPU) 02:51:18PMrunq-szplist-szldavg-1ldavg-5ldavg-15blocked 02:51:19PM423056.416.987.123 02:51:20PM223016.416.987.124 02:51:21PM023006.416.987.125 02:51:22PM623016.416.987.128 02:51:23PM222906.416.987.128
loadaverage大意表示当前CPU中有多少任务在排队等待，等待越多说明负载越高，跑数据库的服务器上，一般load值超过5的话，已经算是比较高的了。
引起load高的原因也可能有多种：
某些进程/服务消耗更多CPU资源(服务响应更多请求或存在某些应用瓶颈)；
发生比较严重的swap(可用物理内存不足)；
发生比较严重的中断(因为SSD或网络的原因发生中断)；
磁盘I/O比较慢(会导致CPU一直等待磁盘I/O请求)；
这时我们可以执行下面的命令来判断到底瓶颈在哪个子系统：
[yejr@imysql.com:~]#top top-11:53:04up702days,56min,1user,loadaverage:7.18,6.70,6.47 Tasks:576total,1running,575sleeping,0stopped,0zombie 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 Mem:49374024ktotal,32018844kused,17355180kfree,115416kbuffers Swap:16777208ktotal,117612kused,16659596kfree,5689020kcached PIDUSERPRNIVIRTRESSHRS%CPU%MEMTIME+COMMAND 14165mysql2008822m3.1g4672S162.36.689839:59mysqld 40610mysql20025.6g14g8336S121.731.5282809:08mysqld 49023mysql20016.9g5.1g4772S4.610.834940:09mysqld
很明显是前面两个mysqld进程导致整体负载较高。
而且，从Cpu(s)这行的统计结果也能看的出来，%us和%wa的值较高，表示当前比较大的瓶颈可能是在用户进程消耗的CPU以及磁盘I/O等待上。
我们先分析下磁盘I/O的情况。
执行sar-d确认磁盘I/O是否真的较大：
[yejr@imysql.com:~]#sar-d1 Linux2.6.32-431.el6.x86_64(yejr.imysql.com)01/13/2016_x86_64_(24CPU) 11:54:32AMdev8-05338.00162784.001394.0030.765.240.980.19100.00 11:54:33AMdev8-05134.00148032.0032365.0035.146.931.340.19100.10 11:54:34AMdev8-05233.00161376.00996.0031.039.771.880.19100.00 11:54:35AMdev8-04566.00139232.001166.0030.755.371.180.22100.00 11:54:36AMdev8-04665.00145920.00630.0031.415.941.270.21100.00 11:54:37AMdev8-04994.00156544.00546.0031.467.071.420.20100.00
再利用iotop确认到底哪些进程消耗的磁盘I/O资源最多：
[yejr@imysql.com:~]#iotop TotalDISKREAD:60.38M/s|TotalDISKWRITE:640.34K/s TIDPRIOUSERDISKREADDISKWRITESWAPINIO>COMMAND 16397be/4mysql8.92M/s0.00B/s0.00%94.77%mysqld--basedir=/usr/local/m~og_3320/mysql.sock--port=3320 7295be/4mysql10.98M/s0.00B/s0.00%93.59%mysqld--basedir=/usr/local/m~og_3320/mysql.sock--port=3320 14295be/4mysql10.50M/s0.00B/s0.00%93.57%mysqld--basedir=/usr/local/m~og_3320/mysql.sock--port=3320 14288be/4mysql14.30M/s0.00B/s0.00%91.86%mysqld--basedir=/usr/local/m~og_3320/mysql.sock--port=3320 14292be/4mysql14.37M/s0.00B/s0.00%91.23%mysqld--basedir=/usr/local/m~og_3320/mysql.sock--port=3320
可以看到，端口号是3320的实例消耗的磁盘I/O资源比较多，那就看看这个实例里都有什么查询在跑吧。
2.MySQL层面检查确认
首先看下当前都有哪些查询在运行：
[yejr@imysql.com(db)]>mysqladminpr|grep-vSleep +----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+ |Id|User|Host|db|Command|Time|State|Info| +----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+ |25|x|10.x:8519|db|Query|68|Sendingdata|selectmax(Fvideoid)from(selectFvideoidfromtwhereFvideoid>404612orderbyFvideoid)t1| |26|x|10.x:8520|db|Query|65|Sendingdata|selectmax(Fvideoid)from(selectFvideoidfromtwhereFvideoid>484915orderbyFvideoid)t1| |28|x|10.x:8522|db|Query|130|Sendingdata|selectmax(Fvideoid)from(selectFvideoidfromtwhereFvideoid>404641orderbyFvideoid)t1| |27|x|10.x:8521|db|Query|167|Sendingdata|selectmax(Fvideoid)from(selectFvideoidfromtwhereFvideoid>324157orderbyFvideoid)t1| |36|x|10.x:8727|db|Query|174|Sendingdata|selectmax(Fvideoid)from(selectFvideoidfromtwhereFvideoid>324346orderbyFvideoid)t1| +----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+
可以看到有不少慢查询还未完成，从slowquerylog中也能发现，这类SQL发生的频率很高。
这是一个非常低效的SQL写法，导致需要对整个主键进行扫描，但实际上只需要取得一个最大值而已，从slowquerylog中可看到：
Rows_sent:1 Rows_examined:5502460
每次都要扫描500多万行数据，却只为读取一个最大值，效率非常低。
经过分析，这个SQL稍做简单改造即可在个位数毫秒级内完成，原先则是需要150-180秒才能完成，提升了N次方。
改造的方法是：对查询结果做一次倒序排序，取得第一条记录即可。而原先的做法是对结果正序排序，取最后一条记录，汗啊。。。
写在最后，小结
在这个例子中，产生瓶颈的原因比较好定位，SQL优化也不难，实际线上环境中，通常有以下几种常见的原因导致负载较高：
一次请求读写的数据量太大，导致磁盘I/O读写值较大，例如一个SQL里要读取或更新几万行数据甚至更多，这种最好是想办法减少一次读写的数据量；
SQL查询中没有适当的索引可以用来完成条件过滤、排序(ORDERBY)、分组(GROUPBY)、数据聚合(MIN/MAX/COUNT/AVG等)，添加索引或者进行SQL改写吧；
瞬间突发有大量请求，这种一般只要能扛过峰值就好，保险起见还是要适当提高服务器的配置，万一峰值抗不过去就可能发生雪崩效应；
因为某些定时任务引起的负载升高，比如做数据统计分析和备份，这种对CPU、内存、磁盘I/O消耗都很大，最好放在独立的slave服务器上执行；
服务器自身的节能策略发现负载较低时会让CPU降频，当发现负载升高时再自动升频，但通常不是那么及时，结果导致CPU性能不足，抗不过突发的请求；
使用raid卡的时候，通常配备BBU(cache模块的备用电池)，早期一般采用锂电池技术，需要定期充放电(DELL服务器90天一次，IBM是30天)，我们可以通过监控在下一次充放电的时间前在业务低谷时提前对其进行放电，不过新一代服务器大多采用电容式电池，也就不存在这个问题了。
文件系统采用ext4甚至ext3，而不是xfs，在高I/O压力时，很可能导致%util已经跑到100%了，但iops却无法再提升，换成xfs一般可获得大幅提升；
内核的ioscheduler策略采用cfq而非deadline或noop，可以在线直接调整，也可获得大幅提升。
更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》、《MySQL数据库锁相关技巧汇总》及《MySQL常用函数大汇总》
希望本文所述对大家MySQL数据库计有所帮助。