MySQL磁盘IO巨高排查全过程

0    56    1

👉 本文共约1938个字,系统预计阅读时间或需8分钟。

前言

是什么原因导致线上数据库服务器磁盘I/O的util和iowait持续飚高?

1. 问题描述

朋友小明的线上数据库突发严重告警,业务方反馈写入数据一直堵住,很多锁超时回滚了,不知道怎么回事,就找到我了。

不管3721,先采集现场的必要信息再说。

a. 系统负载,主要是磁盘I/O的负载数据

图片

该服务器的磁盘是由6块2T SSD硬盘组成的RAID-5阵列。从上面的截图来看,I/O %util已经基本跑满了,iowait也非常高,很明显磁盘I/O压力太大了。那就再查查什么原因导致的这么高压力。

b. 活跃事务列表

图片

可以看到,有几个活跃的事务代价很高,锁定了很多行。其中有两个因为太久超时被回滚了。

图片

再看一次活跃事务列表,发现有个事务锁定的行更多了,说明活跃业务SQL的效率不太好,需要进行优化。这个算是原因之一,先记下。

c. 查看InnoDB状态
执行 SHOW ENGINE INNODB STATUS\G 查看InnoDB状态,这里只展示了几个比较关键的地方:

**d. 查看MySQL的线程状态***

可以看到几个事务都处于updating状态。意思是正在扫描数据并准备更新,肉眼可见这些事务状态时,一般是因为系统负载比较高,所以事务执行起来慢;或者该事务正等待行锁释放。

2. 问题分析及优化工作

分析上面的各种现场信息,我们可以得到以下几点结论:

a. 磁盘I/O压力很大。先把阵列卡的cache策略改成WB,不过由于已经是SSD盘,这个作用并不大,只能申请更换成RAID-10阵列的新机器了,还需等待资源调配。

b. 需要优化活跃SQL,降低加锁代价

执行计划看起来虽然能用到索引,但效率还是不高。检查了下,发现a表的uid列竟然没加索引,我汗。。。

c. InnoDB的redo log checkpoint延迟比较大,有2249MB之巨。先检查redo log的设置:

这个问题就大了,redo log明显太小,等待被checkpoint的redo都超过2G了,那肯定要疯狂刷脏页,所以磁盘I/O的写入才那么高,I/O %util和iowait也很高。

建议把redo log size调整成4G、3组。

此外,也顺便检查了InnoDB其他几个重要选项

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

特别提醒
从MySQL 5.6版本起,修改redo log设置后,实例重启时会自动完成redo log的再次初始化,不过前提是要先干净关闭实例。因此建议在第一次关闭时,修改以下两个选项:

并且,再加上一个新选项,防止实例启动后,会有外部应用连接进来继续写数据:

在确保所有脏页(上面看到的Modified db pages为0)都刷盘完毕后,并且redo log也都checkpoint完毕(上面看到的Log sequence numberLast checkpoint at值相等),此时才能放心的修改 innodb_log_file_size 选项配置并重启。确认生效后再关闭 skip-networking 选项对业务提供服务。

经过一番优化调整后,再来看下服务器和数据库的负载。

图片

可以看到,服务器的磁盘I/O压力再也不会那么大了,数据库中也不会频繁出现大量行锁等待或回滚的事务了。

很明显,redo log checkpoint lag几乎没有了。

完美搞定!

写在最后

遇到数据库性能瓶颈,负载飚高这类问题,我们只需要根据一套完整的方法论 优化系列:实例解析MySQL性能瓶颈排查定位,根据现场的各种蛛丝马迹,逐一进行分析,基本上都是能找出来问题的原因的。本案其实并不难,就是按照这套方法来做的,最后连perf top都没用上就很快能大致确定问题原因了。

参考

https://mp.weixin.qq.com/s/5JKm3Se3UjIPkuV7G1gSBw

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

3 × 5 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部