优化系列 | 实例解析MySQL性能瓶颈排查定位

标签： 数据库 优化 | 发表时间：2016-01-13 21:26 | 作者：叶金荣
出处：http://imysql.com

导读

从一个现场说起，全程解析如何定位性能瓶颈。

排查过程

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

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

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

通常来说， 服务器上最容易成为瓶颈的是磁盘I/O子系统，因为它的读写速度通常是最慢的。即便是现在的PCIe SSD，其随机I/O读写速度也是不如内存来得快。当然了，引起磁盘I/O慢得原因也有多种，需要确认哪种引起的。

第一步，我们一般先看整体负载如何，负载高的话，肯定所有的进程跑起来都慢。
可以执行指令 w 或者 sar -q 1 来查看负载数据，例如：

   [[email protected]:~ ]# w
 11:52:58 up 702 days, 56 min,  1 user,  load average: 7.20, 6.70, 6.47
USER     TTY      FROM              LOGIN@   IDLE   JCPU   PCPU WHAT
root     pts/0    1.xx.xx.xx        11:51    0.00s  0.03s  0.00s w

或者 sar -q 的观察结果：

   [[email protected]:~ ]# sar -q 1
Linux 2.6.32-431.el6.x86_64 (yejr.imysql.com)     01/13/2016     _x86_64_    (24 CPU)
02:51:18 PM   runq-sz  plist-sz   ldavg-1   ldavg-5  ldavg-15   blocked
02:51:19 PM         4      2305      6.41      6.98      7.12         3
02:51:20 PM         2      2301      6.41      6.98      7.12         4
02:51:21 PM         0      2300      6.41      6.98      7.12         5
02:51:22 PM         6      2301      6.41      6.98      7.12         8
02:51:23 PM         2      2290      6.41      6.98      7.12         8

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

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

  1. 某些进程/服务消耗更多CPU资源（服务响应更多请求或存在某些应用瓶颈）；
  2. 发生比较严重的swap（可用物理内存不足）；
  3. 发生比较严重的中断（因为SSD或网络的原因发生中断）；
  4. 磁盘I/O比较慢（会导致CPU一直等待磁盘I/O请求）；

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

   [[email protected]:~ ]# top
top - 11:53:04 up 702 days, 56 min,  1 user,  load average: 7.18, 6.70, 6.47
Tasks: 576 total,   1 running, 575 sleeping,   0 stopped,   0 zombie
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:  49374024k total, 32018844k used, 17355180k free,   115416k buffers
Swap: 16777208k total,   117612k used, 16659596k free,  5689020k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
14165 mysql     20   0 8822m 3.1g 4672 S 162.3  6.6  89839:59 mysqld
40610 mysql     20   0 25.6g  14g 8336 S 121.7 31.5 282809:08 mysqld
49023 mysql     20   0 16.9g 5.1g 4772 S  4.6 10.8   34940:09 mysqld

很明显是前面两个mysqld进程导致整体负载较高。
而且，从 Cpu(s) 这行的统计结果也能看的出来， %us 和 %wa 的值较高，表示 当前比较大的瓶颈可能是在用户进程消耗的CPU以及磁盘I/O等待上。
我们先分析下磁盘I/O的情况。

执行 sar -d 确认磁盘I/O是否真的较大：

   [[email protected]:~ ]# sar -d 1
Linux 2.6.32-431.el6.x86_64 (yejr.imysql.com)     01/13/2016     _x86_64_    (24 CPU)
11:54:32 AM    dev8-0   5338.00 162784.00   1394.00     30.76      5.24      0.98      0.19    100.00
11:54:33 AM    dev8-0   5134.00 148032.00  32365.00     35.14      6.93      1.34      0.19    100.10
11:54:34 AM    dev8-0   5233.00 161376.00    996.00     31.03      9.77      1.88      0.19    100.00
11:54:35 AM    dev8-0   4566.00 139232.00   1166.00     30.75      5.37      1.18      0.22    100.00
11:54:36 AM    dev8-0   4665.00 145920.00    630.00     31.41      5.94      1.27      0.21    100.00
11:54:37 AM    dev8-0   4994.00 156544.00    546.00     31.46      7.07      1.42      0.20    100.00

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

   [[email protected]:~ ]# 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
16397 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=3320
 7295 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=3320
14295 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=3320
14288 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=3320
14292 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

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

2. MySQL层面检查确认

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

   [[email protected](db)]> mysqladmin pr|grep -v Sleep
+----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+
| Id |User| Host     | db |Command|Time | State        | Info                                                                                          |
+----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+
| 25 | x | 10.x:8519 | db | Query | 68  | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>404612 order by Fvideoid) t1 |
| 26 | x | 10.x:8520 | db | Query | 65  | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>484915 order by Fvideoid) t1 |
| 28 | x | 10.x:8522 | db | Query | 130 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>404641 order by Fvideoid) t1 |
| 27 | x | 10.x:8521 | db | Query | 167 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>324157 order by Fvideoid) t1 |
| 36 | x | 10.x:8727 | db | Query | 174 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>324346 order by Fvideoid) t1 |
+----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+

可以看到有不少慢查询还未完成，从slow query log中也能发现，这类SQL发生的频率很高。
这是一个非常低效的SQL写法，导致需要对整个主键进行扫描，但实际上只需要取得一个最大值而已，从slow query log中可看到：

   Rows_sent: 1  Rows_examined: 5502460

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

经过分析，这个SQL稍做简单改造即可在个位数毫秒级内完成，原先则是需要150-180秒才能完成，提升了N次方。
改造的方法是： 对查询结果做一次倒序排序，取得第一条记录即可。而原先的做法是对结果正序排序，取最后一条记录，汗啊。。。

写在最后，小结

在这个例子中，产生瓶颈的原因比较好定位，SQL优化也不难，实际线上环境中，通常有以下几种常见的原因导致负载较高：

  1. 一次请求读写的数据量太大，导致磁盘I/O读写值较大，例如一个SQL里要读取或更新几万行数据甚至更多，这种最好是想办法减少一次读写的数据量；
  2. SQL查询中没有适当的索引可以用来完成条件过滤、排序（ORDER BY）、分组（GROUP BY）、数据聚合（MIN/MAX/COUNT/AVG等），添加索引或者进行SQL改写吧；
  3. 瞬间突发有大量请求，这种一般只要能扛过峰值就好，保险起见还是要适当提高服务器的配置，万一峰值抗不过去就可能发生雪崩效应；
  4. 因为某些定时任务引起的负载升高，比如做数据统计分析和备份，这种对CPU、内存、磁盘I/O消耗都很大，最好放在独立的slave服务器上执行；
  5. 服务器自身的节能策略发现负载较低时会让CPU降频，当发现负载升高时再自动升频，但通常不是那么及时，结果导致CPU性能不足，抗不过突发的请求；
  6. 使用raid卡的时候，通常配备BBU（cache模块的备用电池），早期一般采用锂电池技术，需要定期充放电（DELL服务器90天一次，IBM是30天），我们可以通过监控在下一次充放电的时间前在业务低谷时提前对其进行放电，不过新一代服务器大多采用电容式电池，也就不存在这个问题了。
  7. 文件系统采用ext4甚至ext3，而不是xfs，在高I/O压力时，很可能导致%util已经跑到100%了，但iops却无法再提升，换成xfs一般可获得大幅提升；
  8. 内核的io scheduler策略采用cfq而非deadline或noop，可以在线直接调整，也可获得大幅提升。

 

关于MySQL的方方面面大家想了解什么，可以直接留言回复，我会从中选择一些热门话题进行分享。 同时希望大家多多 转发，多一些阅读量是老叶继续努力分享的绝佳助力，谢谢大家 :)

最后打个广告，运维圈人士专属铁观音茶叶微店上线了，访问： http://yejinrong.com 获得专属优惠

相关 [优化 系列 实例] 推荐：

优化系列 | 实例解析MySQL性能瓶颈排查定位

- - iMySQL
从一个现场说起，全程解析如何定位性能瓶颈. 收到线上某业务后端的MySQL实例负载比较高的告警信息，于是登入服务器检查确认. 首先我们进行OS层面的检查确认. 登入服务器后，我们的目的是首先要确认当前到底是哪些进程引起的负载高，以及这些进程卡在什么地方，瓶颈是什么. 通常来说， 服务器上最容易成为瓶颈的是磁盘I/O子系统，因为它的读写速度通常是最慢的.

系统性能优化系列

- - IT瘾-tuicool
之前组内一位大佬分享了一些关于系统性能优化方面的干货，这里我将它整理成文并且加入自己平时常用的一些工具和技巧. 由于关于系统性能优化涉及的内容非常多，我会分几篇文章来分享. 这次分享下 定位系统层面问题的常用方法. Throughout 吞吐量 （系统每秒钟可以处理的请求数）. Latency 延迟 （系统处理一个请求的延迟）.

[MySQL优化案例]系列 — 分页优化

- - 学习笔记
通常，我们会采用ORDER BY LIMIT start, offset 的方式来进行分页查询. 或者像下面这个不带任何条件的分页SQL：. 一般而言，分页SQL的耗时随着 start 值的增加而急剧增加，我们来看下面这2个不同起始值的分页SQL执行耗时：. 可以看到，随着分页数量的增加，SQL查询耗时也有数十倍增加，显然不科学.

[MySQL优化案例]系列 — slave延迟很大优化方法

- - MySQL中文网
备注：插图来自网络搜索，如果觉得不当还请及时告知 ：）. 一般而言，slave相对master延迟较大，其根本原因就是slave上的复制线程没办法真正做到并发. 简单说，在master上是并发模式（以InnoDB引擎为主）完成事务提交的，而在slave上，复制线程只有一个sql thread用于binlog的apply，所以难怪slave在高并发时会远落后master.

Windows 8实例教程系列 - 理解应用框架

- - 博客园_首页
Windows 操作系统之所以风靡世界，是因为其“易学易用”，从用户的角度出发，让数以万计的非IT人员使用计算机实现娱乐，工作等目的. Windows 8继承Windows桌面的优点，同时提供一种新的用户体验模式 － Windows store风格. 换句话说，Windows 8操作系统存在两种不同风格的应用.

【Twitter Storm系列】 Storm简单实例讲解

- - CSDN博客云计算推荐文章
实例来自书籍《Oreilly.Getting.Started.with.Storm.Aug.2012》. 先讲下我们这次所需涉及到的概念：Topology、Spout、Blot. Topology：Storm的运行单位，相当于Hadoop中的job，一个topology是spouts和bolts组成的图， 通过stream groupings将图中的spouts和bolts连接起来.

[MySQL优化案例]系列 — 优化InnoDB表BLOB列的存储效率

- - MySQL中文网
首先，介绍下关于InnoDB引擎存储格式的几个要点：. 1、InnoDB可以选择使用共享表空间或者是独立表空间方式， 建议使用独立表空间，便于管理、维护. 启用 innodb_file_per_table 选项，5.5以后可以在线动态修改生效，并且执行 ALTER TABLE xx ENGINE = InnoDB 将现有表转成独立表空间，早于5.5的版本，修改完这个选项后，需要重启才能生效；.

优化移动广告系列的五个步骤

- - Google 黑板报 - Google （谷歌）中国的博客网志，走近我们的产品、技术和文化
发表者：Katie Kellogg，AdWords团队 . 如今，人们似乎时时刻刻都在使用手机 —— 不管是买东西还是排队等候，甚至跟别人出去玩儿的时候也是机不离手. 移动用户不仅用手机发短信，还用手机搜索最新有关猫的视频，给老朋友发电子邮件，或者把自己最近吃过的美味上传到博客上，诸如此类. 手机已成为我们日常生活中不可缺少的一部分，而移动搜索业务也呈现不断上升趋势.

Java 代码优化过程的实例介绍

- - 博客 - 伯乐在线
来源: IBM developerworks. 简介： 通过笔者经历的一个项目实例，本文介绍了 Java 代码优化的过程，总结了优化 Java 程序的一些最佳实践，分析了进行优化的方法，并解释了性能提升的原因. 从多个角度分析导致性能低的原因，并逐个进行优化，最终使得程序的性能得到极大提升，代码的可读性、可扩展性更强.

Java Cache-EHCache系列之计算实例占用的内存大小（SizeOf引擎）

- - BlogJava-首页技术区
计算一个实例内存占用大小思路. 在Java中，除了基本类型，其他所有通过字段包含其他实例的关系都是引用关系，因而我们不能直接计算该实例占用的内存大小，而是要递归的计算其所有字段占用的内存大小的和. 在Java中，我们可以将所有这些通过字段引用简单的看成一种树状结构，这样就可以遍历这棵树，计算每个节点占用的内存大小，所有这些节点占用的内存大小的总和就当前实例占用的内存大小，遍历的算法有：先序遍历、中序遍历、后序遍历、层级遍历等.