MySQL高负载排查方法最佳实践（15/16）

高负载排查方法

CPU占用率过高问题排查

使用mpstat查看cpu使用情况。

# mpstat 是一款 CPU 性能指标实时展示工具
# 能展示每个 CPU 核的资源视情况，同时还能将资源使用情况进行汇总展示
# 如果CPU0 的 %idle 已经为 0 ，说明此核已经非常繁忙

# 打印所有cpu核的情况（ALL）,每隔1s打印一次，共打印5次
mpstat -P ALL 1 5

从上面可以看到，服务器有 2 个 CPU（分别为0、1），每个 CPU 核的资源使用情况，也能很清晰的展示。

当服务器有特别多的 CPU 核，有时候看 CPU 汇总信息，发现 CPU 的 idle 很高，但是上面跑的应用程序却很慢，这个时候就需要看看每个核的资源试用情况了。

重点关注指标：

* %iowait：代表在监控的时间间隔内，等待硬盘 I/O 的时间，如果此值比较高，说明 IO 非常繁忙
* %idle：代表在监控的时间间隔内，CPU 闲置时间所占用的时间百分比，此值越大，代表 CPU 越空闲

使用pidstat查看消耗cpu资源的进程。

# 参数-u表明监控cpu使用情况
# 参数2表示每隔2s输出一次，会循环输出

pidstat -u 2

Linux 3.10.0-957.el7.x86_64 (mysql)     12/30/2020      _x86_64_        (1 CPU)

04:57:41 PM   UID       PID    %usr %system  %guest    %CPU   CPU  Command
04:57:43 PM  2001     10733    0.00   91.44    0.00   91.44     0  mysqld
04:57:43 PM  2001     12635    0.00    0.53    0.00    0.53     0  pidstat

从上面的结果可以看到，导致 CPU0 资源被耗尽的元凶是 mysqld 进程，占用91.44%，需要定位具体线程。

pidstat 是一个非常强大的性能指标监控工具，建议一定要掌握，此工具可以用来监控全部或指定进程的 CPU、mem、线程，IO 的资源的使用情况。

# 参数-d表示监控进程对磁盘的使用情况
pidstat -d 2

Linux 3.10.0-957.el7.x86_64 (mysql) 12/30/2020 _x86_64_ (1 CPU)

03:57:47 PM UID PID kB_rd/s kB_wr/s kB_ccwr/s Command
03:57:49 PM 2001 10733 139.13 0.00 0.00 mysqld

03:57:49 PM UID PID kB_rd/s kB_wr/s kB_ccwr/s Command
03:57:51 PM 2001 10733 34034.78 0.00 0.00 mysqld

可以查看指定进程的线程 CPU 资源使用情况。

# 参数 -t，可以获取指定进程的线程的 CPU 使用情况
pidstat -t 2 -p 10733

使用 pidstat 查看消耗 CPU 资源的线程

pidstat -t 2 -p 10733

Linux 3.10.0-957.el7.x86_64 (mysql)     12/30/2020      _x86_64_        (1 CPU)

05:00:53 PM   UID      TGID       TID    %usr %system  %guest    %CPU   CPU  Command
05:00:55 PM  2001     10733         -    0.00   91.30    0.00   91.30     0  mysqld
05:00:55 PM  2001         -     10733    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10734    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10735    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10736    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10737    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10738    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10739    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10740    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10741    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10742    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10743    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10744    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10745    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10747    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10748    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10749    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10750    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10751    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10752    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10753    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10754    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10755    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10756    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10757    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10758    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10762    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10763    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10764    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10766    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10796    0.00    0.00    0.00    0.00     0  |__mysqld
05:00:55 PM  2001         -     10850   15.22   75.54    0.00   90.76     0  |__mysqld

可以很清楚看到是 10850 线程消耗了 CPU 资源，剩下的就根据操作系统的里线程 ID，去 MySQL 数据库里排查，此线程正在执行的操作。

MySQL中查询线程对应的具体SQL

-- 在 MySQL 5.7 版本，官方增加了一个视图，专门记录操作系统线程和数据库进程之间关联视图。
-- 前面定位的最消耗 IO 和 CPU 的线程 ID 为 10850：
select PROCESSLIST_ID,
THREAD_OS_ID,
PROCESSLIST_USER,
PROCESSLIST_HOST,
PROCESSLIST_DB,
PROCESSLIST_COMMAND,
PROCESSLIST_INFO 
from performance_schema.threads 
where THREAD_OS_ID=10850;

磁盘IO占用率过高排查

使用 iostat 查看磁盘 IO 的读写情况。

# 每隔1s输出一次
iostat -mxt 1

# 每隔1s输出一次，共输出2次
iostat -mxt 1 2

如果看到 %util 比较高，说明磁盘设备繁忙，需要定位到底是那个操作系统进程导致的大量读操作。

iostat 是一款用于监控系统设备的 IO 负载情况的工具，在运维工作中会被高频使用的工具之一。

# 常用参数介绍

* -m：输出结果以兆为单位显示
* -t：输出结果中显示时间戳
* -x：显示和 IO 相关的扩展数据，包括每个设备的读写、合并读、合并写、读写等待情况

# 输出指标介绍

* rrqm/s：每秒合并读的条目
* wrqm/s：每秒合并写的条目
* r/s：每秒 read I/O 设备次数
* w/s：每秒 write I/O 设备次数
* rMB/s: 每秒读取 M 字节数
* wMB/s：每秒写 M 字节数
* avgrq-sz：每次设备 I/O 操作平均数据大小
* avgqu-sz：I/O 队列平均长度
* await：每次设备 I/O 操作的平均等待时间，单位毫秒
* r_await：每次读 I/O 操作的平均等待时间，单位毫秒
* w_await：每次写 I/O 操作的平均等待时间，单位毫秒
* svctm：每次设备 I/O 操作的平均服务时间，单位毫秒
* %util：每秒中有百分之多少的时间用于 I/O 操作，此值越高，代表着磁盘越繁忙

使用 iotop 定位最消耗 IO 资源的元凶

iotop -p 10733

10850 线程大量读操作，导致 IO 资源被消耗殆尽。

iotop 是一款用于监视磁盘设备 I/O 使用状况的工具，可监测到具体的线程使用的磁盘 IO 的信息，功能也是非常强大，可惜需要 root 权限，普通用户没有执行权限，所以一般会用 pidstat 工具来替代。

参数介绍：

-P：只显示进程 IO 读写情况 
-p：监控指定进程的 IO 读写情况 
-u：指定用户进程的 IO 读写情况

1. 查看指定用户的的进程
iotop -u mysql -P

2. 查看指定用户的进程和线程 IO 读写情况
iotop -u mysql

3. 查看指定进程的线程 IO 读写情况
iotop -p 10733

根据线程定位具体SQL。

select PROCESSLIST_ID,
THREAD_OS_ID,
PROCESSLIST_USER,
PROCESSLIST_HOST,
PROCESSLIST_DB,
PROCESSLIST_COMMAND,
PROCESSLIST_INFO 
from performance_schema.threads 
where THREAD_OS_ID=10850;

其它运维工具

1. vmstat 工具（Virtual Meomory Statistics 虚拟内存统计）：

可以对 Linux 操作系统的虚拟内存、进程、IO 读写情况、CPU 活动等展示概要监控信息，缺点是无法对某个进程进行深入分析。

# 不带任何参数，每秒打印一条记录，总共打印 10 次
# 缺省次数10，则会一直打印。
vmstat 1 10

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
2 0 2816 86252 0 552252 0 0 1303 3494 259 307 6 4 90 1 0
0 0 2816 86228 0 552252 0 0 0 0 53 110 0 0 100 0 0
0 0 2816 86228 0 552252 0 0 0 0 64 123 0 1 99 0 0
0 0 2816 86228 0 552252 0 0 0 0 53 106 0 0 100 0 0
0 0 2816 86228 0 552252 0 0 0 0 53 107 0 0 100 0 0
0 0 2816 86228 0 552252 0 0 0 0 58 112 0 0 100 0 0
0 0 2816 86228 0 552252 0 0 0 0 57 110 0 1 99 0 0
0 0 2816 86228 0 552252 0 0 0 0 57 115 0 0 100 0 0
0 0 2816 86228 0 552252 0 0 0 0 55 109 0 0 100 0 0
0 0 2816 86228 0 552252 0 0 0 0 58 114 0 0 100 0 0

重要指标：

* r：此指标代表正在运行的进程
* b：此指标代表被阻塞的进程，如果此指标大于 0，表示服务器非常繁忙
* swpd：此指标代表虚拟内存已使用的大小，如果大于 0，代表物理内存不足，需要排查是什么进程导致物理不足
* si：每秒从磁盘读入虚拟内存的大小，这个值大于 0，代表物理内存不足，需要排查是什么进程导致物理不足
* so：每秒虚拟内存写入磁盘的大小，这个值大于 0，代表物理内存不足，需要排查是什么进程导致物理不足
* us、sy、id：代表着服务器 CPU 的繁忙程度，如果 id 值越小，表示 CPU 越繁忙

2. top工具

top 是一款 Linux 系统下经常被用来做性能分析的工具，它可以实时动态的展示进程，线程的资源使用情况，在 top 工具展示监控指标里，运维人员最关心的是 cpu 和 mem 的使用情况。

# 使用 top 对 Linux 操作所有进程按照 CPU 使用率排序
top

top - 15:30:25 up 6:19, 4 users, load average: 0.04, 0.05, 0.05
Tasks: 122 total, 1 running, 121 sleeping, 0 stopped, 0 zombie
%Cpu(s): 0.0 us, 0.3 sy, 0.0 ni, 99.3 id, 0.0 wa, 0.0 hi, 0.3 si, 0.0 st
KiB Mem : 1233464 total, 86056 free, 595080 used, 552328 buff/cache
KiB Swap: 2097148 total, 2094332 free, 2816 used. 459292 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
12404 root 20 0 162120 2268 1548 R 0.7 0.2 0:00.05 top
9777 root 20 0 161372 4952 3496 S 0.3 0.4 0:03.28 sshd
10733 mysql 20 0 1303472 488904 6652 S 0.3 39.6 62:02.58 mysqld
12397 mysql 20 0 161988 2172 1556 S 0.3 0.2 0:00.16 top
12398 root 20 0 0 0 0 S 0.3 0.0 0:00.03 kworker/0:1
1 root 20 0 125452 3472 2148 S 0.0 0.3 0:02.90 systemd
2 root 20 0 0 0 0 S 0.0 0.0 0:00.01 kthreadd
3 root 20 0 0 0 0 S 0.0 0.0 0:48.02 ksoftirqd/0

top 不带任何参数的情况下，是按照 CPU 使用来排序的，可以敲击键盘“x”来，高亮显示排序指标列：

如果要切换排序指标列，可以用“shift+<”或者“shift+>”来调整，例如按照内存使用排序：

支持查看某进程的线程情况。

# 查找mysqld的pid号
ps -ef|grep -i mysqld|egrep -v "mysqld_safe|grep"

# 10733是找到的pid
top -H -p 10733

作者与版本更新计划

感谢您的阅读与支持！本文是《MySQL实战与优化》专栏中的一篇精选文章，该专栏共包含16篇文章，旨在为您提供实战中可直接应用的宝贵知识。

关注公众号【数舟】，获取作者最新动态，公众号后台回复【mysql2024】，即可免费领取这份包含16篇文章的完整的PDF专栏！

目前版本为v1.0，更新时间2024年4月10日。后续此文档更新与版本发布会同步到知识星球【数舟】中。

知识整理与创作不易，感谢大家理解与支持！

加入知识星球，您将获得更多独家内容、专栏更新以及与行业内专家和同行的互动交流机会。我们在知识星球等您，一起探索MySQL的深层次世界！

星球内目前包含300+精品文章，内容涵盖大数据、MySQL、运维、Python、调优、经验分享、数据分析等方向内容，会根据大家的学习需求更新更多方向的内容。

🔗 立即扫描下方二维码，加入知识星球，与行业精英共同成长，开启您的专属学习之旅！