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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

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

25541478146213.jpg


2.2、查看内存—free

free -m

69121478146213.jpg


free相关命令

37061478146213.png

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

vmstat -S m 1

24971478146214.jpg

从上面可以看出,CPUI/O的压力都不算小

输出结果说明:

vmstat 命令的解释:

1procs

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

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

2memory

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

free   空闲的物理内存的大小

buff  多少块正在被用作缓冲  

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

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

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

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

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

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

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

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

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

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

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

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

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

6CPU

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


20211478146214.jpg

输出项说明:

CPUall 表示统计信息为所有 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状态

26991478146214.jpg

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

26621478146214.png

3、查看MySQL在干嘛

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

show processlist;show full processlist

状态一、Sending data

mysql> show processlist\G

11361478146214.png

从以上可以看出是长时间的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

77471478146214.jpg

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

原因:

(1)     DDL被阻塞,进而阻塞他后续SQL

(2)     DDL之前的SQL长时间未结束

解决方法:

(1)     提高每个SQL的效率

(2)     干掉长时间运行的SQL

(3)     DDL放在半夜等低谷时段

(4)     采用pt-osc执行DDL

状态三、Sleep

84171478146214.png

从以上可以看出: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、用更好的设务

  1CPU更快更多核

  2)内存更快更大

  3)用更快的I/O设备

  4)用更好的网络设备

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

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

2)采用noop/deading io scheduler



本文转自 corasql 51CTO博客,原文链接:http://blog.51cto.com/corasql/1906155,如需转载请自行联系原作者

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
15天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
49 3
Mysql(4)—数据库索引
|
3天前
|
存储 关系型数据库 MySQL
MySQL vs. PostgreSQL:选择适合你的开源数据库
在众多开源数据库中,MySQL和PostgreSQL无疑是最受欢迎的两个。它们都有着强大的功能、广泛的社区支持和丰富的生态系统。然而,它们在设计理念、性能特点、功能特性等方面存在着显著的差异。本文将从这三个方面对MySQL和PostgreSQL进行比较,以帮助您选择更适合您需求的开源数据库。
16 4
|
9天前
|
存储 关系型数据库 MySQL
如何在MySQL中创建数据库?
【10月更文挑战第16天】如何在MySQL中创建数据库?
|
13天前
|
SQL Oracle 关系型数据库
安装最新 MySQL 8.0 数据库(教学用)
安装最新 MySQL 8.0 数据库(教学用)
73 4
|
11天前
|
存储 SQL 关系型数据库
【入门级教程】MySQL:从零开始的数据库之旅
本教程面向零基础用户,采用通俗易懂的语言和丰富的示例,帮助你快速掌握MySQL的基础知识和操作技巧。内容涵盖SQL语言基础(SELECT、INSERT、UPDATE、DELETE等常用语句)、使用索引提高查询效率、存储过程等。适合学生、开发者及数据库爱好者。
27 0
【入门级教程】MySQL:从零开始的数据库之旅
|
14天前
|
存储 关系型数据库 MySQL
PACS系统 中 dicom 文件在mysql 8.0 数据库中的 存储和读取(pydicom 库使用)
PACS系统 中 dicom 文件在mysql 8.0 数据库中的 存储和读取(pydicom 库使用)
16 2
|
16天前
|
关系型数据库 MySQL Java
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
这篇文章是关于如何使用Django框架配置MySQL数据库,创建模型实例,并自动或手动创建数据库表,以及对这些表进行操作的详细教程。
47 0
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
|
2天前
|
SQL 关系型数据库 数据库连接
"Nacos 2.1.0版本数据库配置写入难题破解攻略:一步步教你排查连接、权限和配置问题,重启服务轻松解决!"
【10月更文挑战第23天】在使用Nacos 2.1.0版本时,可能会遇到无法将配置信息写入数据库的问题。本文将引导你逐步解决这一问题,包括检查数据库连接、用户权限、Nacos配置文件,并提供示例代码和详细步骤。通过这些方法,你可以有效解决配置写入失败的问题。
7 0
|
8天前
|
存储 监控 关系型数据库
MySQL并发控制与管理:优化数据库性能的关键
【10月更文挑战第17天】MySQL并发控制与管理:优化数据库性能的关键
23 0
|
8天前
|
存储 SQL 关系型数据库
MySQL Workbench支持哪些数据库引擎
【10月更文挑战第17天】MySQL Workbench支持哪些数据库引擎
8 0