MySQL数据库慢–排查问题总结(整理自《抽丝剥茧之MySQL疑难杂症排查》叶金荣)

1、常见瓶颈

  （1）SQL效率低

  （2）选项配置不当

  （3）访问题飙升

  （4）硬件性能低

  （5）其他进程抢资源

2、怎样确认是MySQL存在瓶颈

   top/free/vmstat/sar/mpstat确认

   —确认mysqld进程的CPU消耗占比

   —确认mysqld进程的CPU消耗是%user,还是%sys高

   —确认是否物理内存不够用了

   —确认是否有swap产生

   —确认CPU上是否有大量中断（或中断不均）

2.1、查看CPU—top

2.2、查看内存—free

free -m

free相关命令

2.3、查看IO、CPU、内存、交换分区、中断—vmstat

vmstat -S m 1

从上面可以看出，CPU和I/O的压力都不算小

输出结果说明：

对vmstat 命令的解释：

1）procs

r这一列显示了多少进程正在等待CPU

b列显示了多少进程正在不可中断地休眠（通常意味着它们在等待I/O，例如磁盘、网络、用户输入、等等）。

2）memory

swpd 虚拟内存已使用的大小(显示多少块被换出到了磁盘（页面交换）)，如果大于0，表示你的机器物理内存不足了，如果不是程序内存泄露的原因，那么你该升级内存了或者把耗内存的任务迁移到其他机器。

free   空闲的物理内存的大小

buff  多少块正在被用作缓冲  

cache 多少正在被用作操作系统的缓存

3）swap显示页面交换活动：每秒有多少块正在被换入（从磁盘）和换出（到磁盘）

si  每秒从磁盘读入虚拟内存的大小，如果这个值大于0，表示物理内存不够用或者内存泄露了，要查找耗内存进程解决掉。

so  每秒虚拟内存写入磁盘的大小，如果这个值大于0，同上。

一般情况下，si、so的值都为0，如果si、so的值长期不为0，则表示系统内存不足，需要考虑是否增加系统内存。

4）IO显示有多少块从块设备读取（bi）和写出（bo）

bi  块设备每秒接收的块数量，这里的块设备是指系统上所有的磁盘和其他块设备，默认块大小是1024byte，我本机上没什么IO操作，所以一直是0，但是我曾在处理拷贝大量数据(2-3T)的机器上看过可以达到140000/s，磁盘写入速度差不多140M每秒

bo 块设备每秒发送的块数量，例如我们读取文件，bo就要大于0。bi和bo一般都要接近0，不然就是IO过于频繁，需要调整。

这里设置的bi+bo参考值为1000，如果超过1000，而且wa值比较大，则表示系统磁盘IO性能瓶颈。

5）system显示了每秒中断（in）和上下文切换（cs）的数量

in 每秒CPU的中断次数，包括时间中断

cs 每秒上下文切换次数，例如我们调用系统函数，就要进行上下文切换，线程的切换，也要进程上下文切换，这个值要越小越好，太大了，要考虑调低线程或者进程的数目。系统调用也是，每次调用系统函数，我们的代码就会进入内核空间，导致上下文切换，这个是很耗资源，也要尽量避免频繁调用系统函数。上下文切换次数过多表示你的CPU大部分浪费在上下文切换，导致CPU干正经事的时间少了，CPU没有充分利用，是不可取的。

上面这两个值越大，会看到内核消耗的CPU时间就越多。

6）CPU

us 用户CPU时间。us的值比较高时，说明用户进程消耗的cpu时间多，但是如果长期超过50%的使用，那么我们就该考虑优化程序算法或其他措施了

sy 系统CPU时间，如果太高，表示系统调用时间长，例如是IO操作频繁。

sys的值过高时，说明系统内核消耗的cpu资源多，这个不是良性的表现，我们应该检查原因。

id  空闲 CPU时间，一般来说，id + us + sy = 100,一般我认为id是空闲CPU使用率，us是用户CPU使用率，sy是系统CPU使用率。

wa 等待IO CPU时间。

Wa过高时，说明io等待比较严重，这可能是由于磁盘大量随机访问造成的，也有可能是磁盘的带宽出现瓶颈。

st列一般不关注，虚拟机占用的时间百分比

2.4、查看CPU及IO–sar

查看CPU

sar -u 1

输出项说明：

CPU：all 表示统计信息为所有 CPU 的平均值。

%user：显示在用户级别(application)运行使用 CPU 总时间的百分比。

%nice：通过nice改变了进程调度优先级的进程，在用户模式下消耗的CPU时间的比例

%system：在核心级别(kernel)运行所使用 CPU 总时间的百分比。

%iowait：显示用于等待I/O操作占用 CPU 总时间的百分比。

%steal：管理程序(hypervisor)为另一个虚拟进程提供服务而等待虚拟 CPU 的百分比。

%idle：显示 CPU 空闲时间占用 CPU 总时间的百分比。

1. 若 %iowait 的值过高，表示硬盘存在I/O瓶颈

2. 若 %idle 的值高但系统响应慢时，有可能是 CPU 等待分配内存，此时应加大内存容量

3. 若 %idle 的值持续低于1，则系统的 CPU 处理能力相对较低，表明系统中最需要解决的资源是 CPU 。

查看IO状态

tps:每秒从物理磁盘I/O的次数.多个逻辑请求会被合并为一个I/O磁盘请求,一次传输的大小是不确定的.

rd_sec/s:每秒读扇区的次数.

wr_sec/s:每秒写扇区的次数.

avgrq-sz:平均每次设备I/O操作的数据大小(扇区).

avgqu-sz:磁盘请求队列的平均长度.

await:从请求磁盘操作到系统完成处理,每次请求的平均消耗时间,包括请求队列等待时间,单位是毫秒(1秒=1000毫秒).

svctm:系统处理每次请求的平均时间,不包括在请求队列中消耗的时间.

%util:I/O请求占CPU的百分比,比率越大,说明越饱和.

1. avgqu-sz 的值较低时，设备的利用率较高。

2. 当%util的值接近 1% 时，表示设备带宽已经占满。

2.5、查看中断情况

mpstat -P ALL -I SUM 1 100

3、查看MySQL在干嘛

3.1、显示哪些线程正在运行

show processlist;或show full processlist

状态一、Sending data

mysql> show processlist\G

从以上可以看出是长时间的sending data

Sending data:表示从引擎层读取数据返回给Server端的状态

长时间存在原因：

（1）     没适当的索引，查询效率低

（2）     读取大量数据，读取缓慢

（3）     系统负载高，读取缓慢

解决方法：

（1）     加上合适的索引

（2）     或者改写SQL，提高效率

（3）     增加LIMIT限制每次读取数据量

（4）     检查&升级I/O设备性能

状态二、Waiting for table metadata lock

show processlist;或show full processlist

从以上可以看出：长时间等待MDL锁

原因：

（1）     DDL被阻塞，进而阻塞他后续SQL

（2）     DDL之前的SQL长时间未结束

解决方法：

（1）     提高每个SQL的效率

（2）     干掉长时间运行的SQL

（3）     把DDL放在半夜等低谷时段

（4）     采用pt-osc执行DDL

状态三、Sleep

从以上可以看出：Sleep

看似无害，实则可能是大害虫

（1）     占用连接数

（2）     消耗内存未释放

（3）     可能有行锁（甚至是表锁）未释放

解决方法：

（1）     适当调低timeout

（2）     主动Kill超时不活跃连接

（3）     定期检查锁、锁等待

（4）     可以利用pt-kill工具

状态四：其他状态

(1)状态：Copy to tmp table

原因：

 1）执行alter table 修改表结构，需要生成临时表

 2）建议放在夜间低谷执行，或者用pt-osc

(2)Copying to tmp table [on disk]

 Creating tmp table

 常见于group by 没有索引的情况

 需要拷贝数据到临时表[内存/磁盘上]

 执行计划中会出现Using temporary关键字

 建议创建合适的索引，消除临时表

（3) Creating sort index

常见于order by 没有索引的情况

需要进行filesort排序

执行计划中会出现Using filesort关键字

建议创建排序索引

（4）其他状态

Waiting for global read lock

Waiting for query cache lock

Waiting for table level lock

Waiting for table metadata lock

3.2、查看锁– mysql锁排查过程

mysql> select * from information_schema.innodb_trx;

mysql> select * from information_schema.innodb_locks;

查看锁等待

mysql> select * from information_schema.innodb_lock_waits;

mysql> select * from sys.innodb_lock_waits; 

mysql锁排查过程

1）查看当前锁等待的情况

INNODB_TRX的锁情况：

mysql> SELECT  * FROM INNODB_TRX\G;

2）查看锁等待和持有锁的相互关系

mysql> SELECT * FROM INNODB_LOCK_WAITS\G;

3）查看锁等待的原因

mysql> SELECT * FROM INNODB_LOCKS\G;

3.3、查看Innodb的状态

show engine innodb status\G

查看MySQL线程状态

3.4、查看慢日志

4、如何预防

4.1、业务上线前

（1）提前消灭垃圾SQL,

（2）在开发或压测环境中

  调底long_query_time的值，甚至设为0

  开启log_queries_not_using_indexes

  分析slow query log，并消除潜在隐患SQL

4.2、用更好的设务

  （1）CPU更快更多核

  （2）内存更快更大

  （3）用更快的I/O设备

  （4）用更好的网络设备

4.3、磁盘文件系统及调度算法

(1)采用xfs/ext4文件系统

（2）采用noop/deading io scheduler