MySQL性能问题诊断方法和常用工具

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG数据库运维（如安装迁移，性能优化、故障应急处理等）
公众号：老苏畅谈运维
欢迎关注本人公众号，更多精彩与您分享。

MySQL运行慢，出现性能问题，一般可以从三个方向来进行排查解决：

• 系统整体资源使用情况
• MySQL 内部运行的压力
• SQL语句的改写

首先从服务器的角度，我们从巡检的脚本角度入手，服务器的资源就那么几种，把服务器的资源全都排查一下就可以了，看资源是否存在瓶颈。

其次MySQL 本身提供了很多命令来观察 MySQL 自身的各类状态，从上往下检一般能检到 SQL 的问题或者服务器的问题。

最后如果实在搞不定，需求方一定要按照数据库容易接受的方式去改写 SQL，这个成本会下降的非常快，这个是常规的 MySQL 慢的诊断思路。

接下来我们详细展开说说，如何具体排查。

一、服务器排查常用工具

1、 机器的负载情况

$uptime
23:51:26 up 21:31, 1 user, load average: 30.02, 26.43, 19.02

例如，如果您被要求检查有问题的服务器，而 1 分钟的值远低于 15 分钟的值，那么您可能登录得太晚而错过了问题。
在上面的示例中，负载平均值显示最近增加，1 分钟值达到 30，而 15 分钟值达到 19。这么大的数字意味着很多东西：可能是 CPU 资源紧张；使用top、vmstat 或 mpstat 进一步确认。

2、 top命令

top - 00:15:40 up 21:56,  1 user,  load average: 31.09, 29.87, 29.92
Tasks: 871 total,   1 running, 868 sleeping,   0 stopped,   2 zombie
%Cpu(s): 96.8 us,  0.4 sy,  0.0 ni,  2.7 id,  0.1 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem:  25190241+total, 24921688 used, 22698073+free,    60448 buffers
KiB Swap:        0 total,        0 used,        0 free.   554208 cached MemPID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND20248 root      20   0  0.227t 0.012t  18748 S  3090  5.2  29812:58 java4213 root      20   0 2722544  64640  44232 S  23.5  0.0 233:35.37 mesos-slave66128 titancl+  20   0   24344   2332   1172 R   1.0  0.0   0:00.07 top5235 root      20   0 38.227g 547004  49996 S   0.7  0.2   2:02.74 java4299 root      20   0 20.015g 2.682g  16836 S   0.3  1.1  33:14.42 java1 root      20   0   33620   2920   1496 S   0.0  0.0   0:03.82 init2 root      20   0       0      0      0 S   0.0  0.0   0:00.02 kthreadd3 root      20   0       0      0      0 S   0.0  0.0   0:05.35 ksoftirqd/05 root       0 -20       0      0      0 S   0.0  0.0   0:00.00 kworker/0:0H6 root      20   0       0      0      0 S   0.0  0.0   0:06.94 kworker/u256:08 root      20   0       0      0      0 S   0.0  0.0   2:38.05 rcu_schedCtrl-S to pause, Ctrl-Q to continue

上面可以看到CPU使用率较高，达到96.8%

3、vmstat命令

$ vmstat 1
procs ---------memory---------- ---swap-- -----io---- -system-- ------cpu-----r  b swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
34  0    0 200889792  73708 591828    0    0     0     5    6   10 96  1  3  0  0
32  0    0 200889920  73708 591860    0    0     0   592 13284 4282 98  1  1  0  0
32  0    0 200890112  73708 591860    0    0     0     0 9501 2154 99  1  0  0  0
32  0    0 200889568  73712 591856    0    0     0    48 11900 2459 99  0  0  0  0
32  0    0 200890208  73712 591860    0    0     0     0 15898 4840 98  1  1  0  0

要检查的列：
r：在 CPU 上运行并等待轮换的进程数。这为确定 CPU 饱和度提供了比负载平均值更好的信号，因为它不包括 I/O。解释：大于 CPU 计数的“r”值是饱和。
free：以千字节为单位的可用内存。如果要数的位数太多，则您有足够的可用内存。包含在命令 7 中的“free -m”命令更好地解释了空闲内存的状态。
si, so：换入和换出。如果这些不为零，则说明您内存不足。
us, sy, id, wa, st：这些是 CPU 时间的细分，平均跨所有 CPU。它们是用户时间、系统时间（内核）、空闲、等待 I/O 和被盗时间（由其他来宾或 Xen，来宾自己的隔离驱动程序域）。

cpu system 使用率超过20%，需要引起注意，可能内核处理 I/O 效率低下。

4、mpstat命令

$ mpstat -P ALL 1
Linux 3.13.0-49-generic (titanclusters-xxxxx)  07/14/2015  _x86_64_ (32 CPU)07:38:49 PM  CPU   %usr  %nice   %sys %iowait   %irq  %soft  %steal  %guest  %gnice  %idle
07:38:50 PM  all  98.47   0.00   0.75    0.00   0.00   0.00    0.00    0.00    0.00   0.78
07:38:50 PM    0  96.04   0.00   2.97    0.00   0.00   0.00    0.00    0.00    0.00   0.99
07:38:50 PM    1  97.00   0.00   1.00    0.00   0.00   0.00    0.00    0.00    0.00   2.00
07:38:50 PM    2  98.00   0.00   1.00    0.00   0.00   0.00    0.00    0.00    0.00   1.00
07:38:50 PM    3  96.97   0.00   0.00    0.00   0.00   0.00    0.00    0.00    0.00   3.03
[...]

此命令打印每个 CPU 的 CPU 时间细分，可用于检查不平衡。单个热 CPU 可以作为单线程应用程序的证据。

5、pidstat命令

$ pidstat 1
Linux 3.13.0-49-generic (titanclusters-xxxxx)  07/14/2015    _x86_64_    (32 CPU)07:41:02 PM   UID       PID    %usr %system  %guest    %CPU   CPU  Command
07:41:03 PM     0         9    0.00    0.94    0.00    0.94     1  rcuos/0
07:41:03 PM     0      4214    5.66    5.66    0.00   11.32    15  mesos-slave
07:41:03 PM     0      4354    0.94    0.94    0.00    1.89     8  java
07:41:03 PM     0      6521 1596.23    1.89    0.00 1598.11    27  java
07:41:03 PM     0      6564 1571.70    7.55    0.00 1579.25    28  java
07:41:03 PM 60004     60154    0.94    4.72    0.00    5.66     9  pidstat07:41:03 PM   UID       PID    %usr %system  %guest    %CPU   CPU  Command
07:41:04 PM     0      4214    6.00    2.00    0.00    8.00    15  mesos-slave
07:41:04 PM     0      6521 1590.00    1.00    0.00 1591.00    27  java
07:41:04 PM     0      6564 1573.00   10.00    0.00 1583.00    28  java
07:41:04 PM   108      6718    1.00    0.00    0.00    1.00     0  snmp-pass
07:41:04 PM 60004     60154    1.00    4.00    0.00    5.00     9  pidstat

Pidstat 有点像 top 的每个进程摘要，但打印滚动摘要而不是清除屏幕，这对于观察一段时间内的模式很有用。
上面的示例标识了两个负责消耗 CPU 的 java 进程。%CPU 列是所有 CPU 的总数；1591% 表明 java 进程消耗了将近 16 个 CPU。

6、iostat命令

$ iostat -xz 1
Linux 3.13.0-49-generic (titanclusters-xxxxx)  07/14/2015  _x86_64_ (32 CPU)avg-cpu:  %user   %nice %system %iowait  %steal   %idle73.96    0.00    3.73    0.03    0.06   22.21Device:   rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
xvda        0.00     0.23    0.21    0.18     4.52     2.08    34.37     0.00    9.98   13.80    5.42   2.44   0.09
xvdb        0.01     0.00    1.02    8.94   127.97   598.53   145.79     0.00    0.43    1.78    0.28   0.25   0.25
xvdc        0.01     0.00    1.02    8.86   127.79   595.94   146.50     0.00    0.45    1.82    0.30   0.27   0.26
dm-0        0.00     0.00    0.69    2.32    10.47    31.69    28.01     0.01    3.23    0.71    3.98   0.13   0.04
dm-1        0.00     0.00    0.00    0.94     0.01     3.78     8.00     0.33  345.84    0.04  346.81   0.01   0.00
dm-2        0.00     0.00    0.09    0.07     1.35     0.36    22.50     0.00    2.55    0.23    5.62   1.78   0.03
^C

r/s, w/s, rkB/s, wkB/s：这些是每秒传送到设备的读取、写入、读取千字节和写入千字节。使用这些来表征工作负载。性能问题可能仅仅是由于施加了过多的负载。
await：I/O 的平均时间（以毫秒为单位）。这是应用程序遭受的时间，因为它包括排队时间和服务时间。大于预期的平均时间可能是设备饱和或设备问题的指标。
avgqu-sz：向设备发出的平均请求数。大于 1 的值可能是饱和的证据（尽管设备通常可以并行处理请求，尤其是前端多个后端磁盘的虚拟设备。）
%util：设备利用率。这确实是一个繁忙百分比，显示设备每秒工作的时间。大于 60% 的值通常会导致性能不佳（应在await中看到），尽管这取决于设备。接近 100% 的值通常表示饱和。

如果存储设备是面向许多后端磁盘的逻辑磁盘设备，那么 100% 利用率可能只是意味着 100% 的时间正在处理某些 I/O，但是，后端磁盘可能远未饱和，并且可能能够处理更多的工作。
请记住，性能不佳的磁盘 I/O 不一定是应用程序问题。许多技术通常用于异步执行 I/O，因此应用程序不会直接阻塞和遭受延迟（例如，读取的预读和写入的缓冲）。

7、查看内存使用

$ free -mtotal       used       free     shared    buffers     cached
Mem:        245998      24545     221453         83         59        541
-/+ buffers/cache:      23944     222053
Swap:            0          0          0

buffers：用于缓冲区缓存，用于块设备 I/O。
cached：用于页面缓存，由文件系统使用。
检查这些大小是否接近于零，这会导致更高的磁盘 I/O（使用 iostat 确认）和更差的性能。上面的例子看起来不错，每个都有很多兆字节。

8、查看网络带宽使用

$ sar -n DEV 1
Linux 3.13.0-49-generic (titanclusters-xxxxx)  07/14/2015     _x86_64_    (32 CPU)12:16:48 AM     IFACE   rxpck/s   txpck/s    rxkB/s    txkB/s   rxcmp/s   txcmp/s  rxmcst/s   %ifutil
12:16:49 AM      eth0  18763.00   5032.00  20686.42    478.30      0.00      0.00      0.00      0.00
12:16:49 AM        lo     14.00     14.00      1.36      1.36      0.00      0.00      0.00      0.00
12:16:49 AM   docker0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.0012:16:49 AM     IFACE   rxpck/s   txpck/s    rxkB/s    txkB/s   rxcmp/s   txcmp/s  rxmcst/s   %ifutil
12:16:50 AM      eth0  19763.00   5101.00  21999.10    482.56      0.00      0.00      0.00      0.00
12:16:50 AM        lo     20.00     20.00      3.25      3.25      0.00      0.00      0.00      0.00
12:16:50 AM   docker0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
^C

此工具检查网络接口吞吐量：rxkB/s 和 txkB/s，作为工作量的衡量标准，并检查是否已达到任何限制。在上面的示例中，eth0 接收达到 22 Mbytes/s，即 176 Mbits/sec（远低于 1 Gbit/sec 的限制）。

9、查看TCP使用情况

$ sar -n TCP,ETCP 1
Linux 3.13.0-49-generic (titanclusters-xxxxx)  07/14/2015    _x86_64_    (32 CPU)12:17:19 AM  active/s passive/s    iseg/s    oseg/s
12:17:20 AM      1.00      0.00  10233.00  18846.0012:17:19 AM  atmptf/s  estres/s retrans/s isegerr/s   orsts/s
12:17:20 AM      0.00      0.00      0.00      0.00      0.0012:17:20 AM  active/s passive/s    iseg/s    oseg/s
12:17:21 AM      1.00      0.00   8359.00   6039.0012:17:20 AM  atmptf/s  estres/s retrans/s isegerr/s   orsts/s
12:17:21 AM      0.00      0.00      0.00      0.00      0.00
^C

一些关键 TCP 指标的总结视图。这些包括：

active/s：每秒本地发起的 TCP 连接数（例如，通过 connect()）。
Passive/s：每秒远程发起的 TCP 连接数（例如，通过 accept()）。
retrans/s：每秒 TCP 重传次数。

10、查看系统日志

$ dmesg | tail
[1880957.563150] perl invoked oom-killer: gfp_mask=0x280da, order=0, oom_score_adj=0
[...]
[1880957.563400] Out of memory: Kill process 18694 (perl) score 246 or sacrifice child
[1880957.563408] Killed process 18694 (perl) total-vm:1972392kB, anon-rss:1953348kB, file-rss:0kB
[2320864.954447] TCP: Possible SYN flooding on port 7001. Dropping request.  Check SNMP counters.

这将查看最后10条系统消息（如果有）。查找可能导致性能问题的错误。上面的示例包括 oom-killer 和 TCP 丢弃请求。不要错过这一步！dmesg 总是值得检查的。

二、MySQL内部压力排查方法

第一步是 Processlist，看一下进程都在跑什么SQL，哪个 SQL 压力不太正常；
第二步是 explain，查看有问题SQL的执行计划；
第三步要做 Profilling，如果这个 SQL 能再执行一次的话, 就做一个 Profilling，看这个SQL消耗最多的再哪里。