以下文章来源于老叶茶馆 ,作者叶金荣
摘要:从一个现场说起,全程解析如何定位性能瓶颈。
收到线上某业务后端的MySQL实例负载比较高的告警信息,于是登入服务器检查确认。
登入服务器后,我们的目的是首先要确认当前到底是哪些进程引起的负载高,以及这些进程卡在什么地方,瓶颈是什么。
通常来说,服务器上最容易成为瓶颈的是磁盘I/O子系统,因为它的读写速度通常是最慢的。即便是现在的PCIe SSD,其随机I/O读写速度也是不如内存来得快。当然了,引起磁盘I/O慢得原因也有多种,需要确认哪种引起的。
第一步,我们一般先看整体负载如何,负载高的话,肯定所有的进程跑起来都慢。 可以执行指令 w 或者 sar -q 1 来查看负载数据,例如(横版查看):
或者 sar -q 的观察结果(横版查看):
load average大意表示当前CPU中有多少任务在排队等待,等待越多说明负载越高,跑数据库的服务器上,一般load值超过5的话,已经算是比较高的了。
引起load高的原因也可能有多种:
这时我们可以执行下面的命令来判断到底瓶颈在哪个子系统(横版查看):
很明显是前面两个mysqld进程导致整体负载较高。 而且,从 Cpu(s) 这行的统计结果也能看的出来,%us 和 %wa 的值较高,表示当前比较大的瓶颈可能是在用户进程消耗的CPU以及磁盘I/O等待上。 我们先分析下磁盘I/O的情况。
执行 sar -d 确认磁盘I/O是否真的较大(横版查看):
[yejr@imysql.com:~ ]# sar -d 1
Linux 2.6.32-431.el6.x86_64 (yejr.imysql.com) // _x86_64_ ( CPU)
:: AM dev8-0 5338.00 162784.00 1394.00 30.76 5.24 0.98 0.19 100.00
:: AM dev8-0 5134.00 148032.00 32365.00 35.14 6.93 1.34 0.19 100.10
:: AM dev8-0 5233.00 161376.00 996.00 31.03 9.77 1.88 0.19 100.00
:: AM dev8-0 4566.00 139232.00 1166.00 30.75 5.37 1.18 0.22 100.00
:: AM dev8-0 4665.00 145920.00 630.00 31.41 5.94 1.27 0.21 100.00
:: AM dev8-0 4994.00 156544.00 546.00 31.46 7.07 1.42 0.20 100.00
再利用 iotop 确认到底哪些进程消耗的磁盘I/O资源最多(横版查看):
[yejr@imysql.com:~ ]# iotop
Total DISK READ: 60.38 M/s | Total DISK WRITE: 640.34 K/s
TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND
be/4 mysql 8.92 M/s 0.00 B/s 0.00 % 94.77 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=
be/4 mysql 10.98 M/s 0.00 B/s 0.00 % 93.59 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=
be/4 mysql 10.50 M/s 0.00 B/s 0.00 % 93.57 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=
be/4 mysql 14.30 M/s 0.00 B/s 0.00 % 91.86 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=
be/4 mysql 14.37 M/s 0.00 B/s 0.00 % 91.23 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=
可以看到,端口号是3320的实例消耗的磁盘I/O资源比较多,那就看看这个实例里都有什么查询在跑吧。
首先看下当前都有哪些查询在运行(横版查看):
[yejr@imysql.com(db)]> mysqladmin pr|grep -v Sleep
+----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+
| Id |User| Host | db |Command|Time | State | Info |
+----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+
| | x | 10.x: | db | Query | | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid> order by Fvideoid) t1 |
| | x | 10.x: | db | Query | | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid> order by Fvideoid) t1 |
| | x | 10.x: | db | Query | | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid> order by Fvideoid) t1 |
| | x | 10.x: | db | Query | | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid> order by Fvideoid) t1 |
| | x | 10.x: | db | Query | | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid> order by Fvideoid) t1 |
+----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+
可以看到有不少慢查询还未完成,从slow query log中也能发现,这类SQL发生的频率很高。
这是一个非常低效的SQL写法,导致需要对整个主键进行扫描,但实际上只需要取得一个最大值而已,从slow query log中可看到:
每次都要扫描500多万行数据,却只为读取一个最大值,效率非常低。
经过分析,这个SQL稍做简单改造即可在个位数毫秒级内完成,原先则是需要150-180秒才能完成,提升了N次方。 改造的方法是:对查询结果做一次倒序排序,取得第一条记录即可。而原先的做法是对结果正序排序,取最后一条记录,汗啊。。。
在这个例子中,产生瓶颈的原因比较好定位,SQL优化也不难,实际线上环境中,通常有以下几种常见的原因导致负载较高: