MySQL性能瓶颈排查定位

排查过程

收到线上某业务后端的MySQL实例负载比较高的告警信息，于是登入服务器检查确认。

1. 首先我们进行OS层面的检查确认

登入服务器后，我们的目的是首先要确认当前到底是哪些进程引起的负载高，以及这些进程卡在什么地方，瓶颈是什么。

通常来说，服务器上最容易成为瓶颈的是磁盘I/O子系统，因为它的读写速度通常是最慢的。当然了，引起磁盘I/O慢得原因也有多种，需要确认哪种引起的。

第一步，我们一般先看整体负载如何，负载高的话，肯定所有的进程跑起来都慢。

可以执行指令 w 或者 sar -q 1 来查看负载数据

或者 sar -q 的观察结果：

load average大意表示当前CPU中有多少任务在排队等待，等待越多说明负载越高，跑数据库的服务器上，一般load值超过5的话，已经算是比较高的了。

引起load高的原因也可能有多种：

某些进程/服务消耗更多CPU资源(服务响应更多请求或存在某些应用瓶颈)；

发生比较严重的swap(可用物理内存不足)；

发生比较严重的中断(因为SSD或网络的原因发生中断)；

磁盘I/O比较慢(会导致CPU一直等待磁盘I/O请求)；

这时我们可以执行下面的命令来判断到底瓶颈在哪个子系统：

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

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

 

2. MySQL层面检查确认

 

首先看下当前都有哪些查询在运行：

可以看到有不少慢查询还未完成，从slow query log中也能发现，这类SQL发生的频率很高。

这是一个非常低效的SQL写法，导致需要对整个主键进行扫描，但实际上只需要取得一个最大值而已，从slow query log中可看到：

Rows_sent: 1  Rows_examined: 5502460
每次都要扫描500多万行数据，却只为读取一个最大值，效率非常低。

经过分析，这个SQL稍做简单改造即可在个位数毫秒级内完成，原先则是需要150-180秒才能完成，提升了N次方。

改造的方法是：对查询结果做一次倒序排序，取得第一条记录即可。而原先的做法是对结果正序排序，取最后一条记录

 

小结：

通常有以下几种常见的原因导致负载较高：

 

一次请求读写的数据量太大，导致磁盘I/O读写值较大，例如一个SQL里要读取或更新几万行数据甚至更多，这种最好是想办法减少一次读写的数据量；

 

SQL查询中没有适当的索引可以用来完成条件过滤、排序(ORDER BY)、分组(GROUP BY)、数据聚合(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一般可获得大幅提升；

 

内核的io scheduler策略采用cfq而非deadline或noop，可以在线直接调整，也可获得大幅提升。