FAQ系列 | 是什么导致MySQL数据库服务器磁盘I/O高?

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

0、导读

有个MySQL服务器的磁盘I/O总有过高报警,怎么回事?

1、问题

我的朋友小明,TA有个MySQL服务器最近总是报告磁盘I/O非常高,想着我这有免费的不用白不用的企业技术服务(TA自己这么想的),就找我帮忙给把把脉。

作为一个经验丰富(踩坑不断)的DBA,出现这种问题,一般来说,磁盘I/O很高无非是下面几个原因引起:

  1. 磁盘子系统设备性能差,或采用ext2/ext3之类文件系统,或采用cfq之类的io scheduler,所以IOPS提上不去;

  2. SQL效率不高,比如没有索引,或者一次性读取大量数据,所以需要更多的I/O;

  3. 可用内存太小,内存中能缓存/缓冲的数据不多,所以需要更多的I/O。

方法论已有,接下来就是动手开始排查了。

2、排查

先看磁盘I/O设备,是由十几块SSD组成的RAID 10阵列,按理说I/O性能应该不至于太差,看iops和%util的数据也确实如此。640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=

再来看下文件系统、io scheduler的因素,发现采用xfs文件系统,而且io scheduler用的是noop,看来也不是这个原因。而且看了下iostat的数据,发现iops也不算低,说明I/O能力还是可以的。640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=

再来看看当前的processlist,以及slow query log,也没发现当前有特别明显的slow query,所以也不是这个原因了。640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=

现在只剩下内存不足这个因素了,看了下服务器物理内存是64G,用系统命令 free看了下,发现大部分都在cached,而free的也不多。观察InnoDB相关的配置以及status,看能不能找到端倪。

首先,看下 innodb-buffer-pool-size 分配了多少:640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=

嗯,分配了18G,好像不是太多啊~

再看一下 innodb status:640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=

重点关注下几个wait值,再看下show engine innodb结果:640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=

关注下unpurge列表大小,看起来还是比较大的(有111万)。

更为诡异的是,在已经停掉SLAVE IO & SQL线程后,发现redo log还在一直增长...

第一次看640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=

停掉SLAVE线程后过阵子再看gif;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAA

看到这里,有经验的DBA应该基本上能想明白了,主要是因为 innodb buffer pool 太小,导致了下面几个后果:

  1. dirty page 和 data page 之间相互“排挤抢占”,所以会出现 Innodb_buffer_pool_wait_free 事件;

  2. redo log 也没办法及时刷新到磁盘中,所以在SLAVE线程停掉后,能看到LSN还在持续增长;

  3. 同时我们也看到unpurge的列表也积攒到很大(111万),这导致了ibdata1文件涨到了146G之大,不过这个可能也是因为有某些事务长时间未提交。


还有,不知道大家注意到没,Innodb_row_lock_current_waits 的值竟然是 18446744073709551615(想想bigint多大),显然不可能啊。事实上,这种情况已经碰到过几次了,明明当前没有行锁,这个 status 值却不小,查了一下官方bug库,竟然只报告了一例,bug id是#71520。

3、解决

既然知道原因,问题解决起来也就快了,我们主要做了下面几个调整:

  • 调大innodb-buffer-pool-size,原则上不超过物理内存的70%,所以设置为40G;

  • 调大innodb-purge-thread,原来是1,调整成4;

  • 调大innodb_io_capacity和innodb_io_capacity_max,值分别为2万和2.5万;


文章转自老叶茶馆公众号,原文链接:https://mp.weixin.qq.com/s/sAGFo-h1GCBhad1r1cEWTg
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
SQL 缓存 固态存储
FAQ系列 | 是什么导致MySQL数据库服务器磁盘I/O高?
FAQ系列 | 是什么导致MySQL数据库服务器磁盘I/O高?
131 0
FAQ系列 | 是什么导致MySQL数据库服务器磁盘I/O高?
|
监控 关系型数据库 MySQL
FAQ系列 磁盘空间满了之后MySQL会怎样
FAQ系列 | 磁盘空间满了之后MySQL会怎样
200 0
|
存储 缓存 关系型数据库
MySQL的磁盘I/O是什么意思?底层原理是什么?
MySQL的磁盘I/O是什么意思?底层原理是什么?
487 0
|
弹性计算 关系型数据库 MySQL
ECS服务器部署MySQL数据库无法显示数据库版本的问题
MySQL数据库无法显示数据库版本 Error:GPG check FAILED
224 1
ECS服务器部署MySQL数据库无法显示数据库版本的问题
阿里云新服务器(没有任何数据)在安装宝塔前挂载数据磁盘教程
本文中的磁盘/dev/vdb为笔者测试服务器上的命名,在您的服务器中可能是/dev/xdb、/dev/sdb、/dev/xvdb等等
575 0
阿里云新服务器(没有任何数据)在安装宝塔前挂载数据磁盘教程
|
前端开发 架构师 Java
领域驱动设计DDD从入门到代码实践
在本文中,作者将借鉴《实现领域驱动设计》的做法,介绍领域驱动设计的基本概念的同时,用一个虚拟的公司和一个虚拟的项目,把领域驱动设计进行落地实践。
12928 9
领域驱动设计DDD从入门到代码实践
|
SQL 存储 Oracle
SQL优化2020最全干货总结---MySQL
BATJTMD等大厂的面试难度越来越高,但无论从大厂还是到小公司,一直未变的一个重点就是对SQL优化经验的考察。一提到数据库,先“说一说你对SQL优化的见解吧?”。
29681 2
|
监控 关系型数据库 MySQL
FAQ系列 | 磁盘空间满了之后MySQL会怎样
FAQ系列 | 磁盘空间满了之后MySQL会怎样
|
存储 缓存 NoSQL
springboot之使用redistemplate优雅地操作redis
概述 本文内容主要 关于spring-redis 关于redis的key设计 redis的基本数据结构 介绍redis与springboot的整合 sringboot中的redistemplate的使用 之前看了很多博客,大都都只是粗略的介绍,这里想要记录的全面一些,也算是一个学习的过程 首发于...
9323 0
|
监控 关系型数据库 数据库
PostgreSQL 10.1 手册_部分 III. 服务器管理_第 29 章 监控磁盘使用_29.2. 磁盘满失败
29.2. 磁盘满失败 一个数据库管理员最重要的磁盘监控任务就是确保磁盘不会写满。一个写满了的数据磁盘可能不会导致数据的崩溃,但它肯定会让系统变得不可用。如果保存 WAL 文件的磁盘变满,会发生数据库服务器致命错误并且可能发生关闭。
1341 0