MYSQL数据库服务磁盘IO高问题分析与优化

比眉伴天荒 2022-05-31 14:10 315阅读 0赞

#     #

压力测试过程中，如果因为资源使用瓶颈等问题引发最直接性能问题是业务交易响应时间偏大，TPS逐渐降低等。而问题定位分析通常情况下，最优先排查的是监控服务器资源利用率，例如先用TOP 或者nmon等查看CPU、内存使用情况，然后在排查IO问题，例如网络IO、磁盘IO的问题。 如果是磁盘IO问题，一般问题是SQL语法问题、MYSQL参数配置问题、服务器自身硬件瓶颈导致IOPS吞吐率问题。

今天主要是讲解MYSQL 参数配置不合理导致在高并发下磁盘IO问题，而MYSQL整体监控优化方案后面会整理《如何轻量化MYSQL服务性能监控》文章出来。

1、 打开日志跟踪引起的磁盘IO问题  
例如：MySQL的日志包括错误日志（ErrorLog），更新日志（UpdateLog），二进制日志（Binlog），查询日志（QueryLog），慢查询日志（SlowQueryLog）等，正常情况下，在生产系统或者压力测试环境中很少有系统会时时打开查询日志。因为查询日志打开之后会将MySQL中执行的每一条Query都记录到日志中，会该系统带来比较大的IO负担，而带来的实际效益却并不是非常大.

2、 SQL写法问题引起磁盘IO高  
例如：曾经在做某一个项目时，在看到数据库磁盘IO使用率偏高，前端查询业务交易loadrunner显示事物响应时间偏长，通过监控工具抓取对应SQL，通过计划分析，发现该SQL 中使用distinct 又多表关联且是大表、然后使用order by，最终显示10笔数据，而在产生中间过程数据进行筛选时，使用的是临时表，并把数据放入临时表中，内存刚好设置不大，于是放到磁盘中导致IO偏高。  
备注：MySQL在执行SQL查询时可能会用到临时表，临时表存储，MySQL会先创建内存临时表，但内存临时表超过配置指定的值后，MySQL会将内存临时表导出到磁盘临时表；

3、 MYSQL参数配置问题  
MYSQL默认配置性能低下，只能通过并发下尝试调整参数配置来逐步优化数据库性能，2017年底根据公司要求配合帮助某一家银行业务系统做性能测试，因为测试环境硬件资源有限，我跟公司申请了几台过时的笔记本，然后根据生产环境软件版本等配置要求，进行模拟搭建性能测试环境，基础软件包含：MYSQL5.6 、centos7.2、tomcat7、 JDK1.7、redis。使用的是联想L421 笔记本当MYSQL数据库服务器、L440当tomcat应用服务器，压力测试工具loadrunner、并发用户100，压力测试业务场景：用户登录退出、相关票据信息查询、电子汇票交易流程等，在压力测试过程中发现部分交易在50用户并发时，数据库磁盘I0使用率都偏高，特别是写操作一直很高，例如测试登录退出交易，经监控数据库磁盘IO率一直偏高，如下案例分析讲解：

### 优化前 ###

压力测试时，数据库磁盘IO使用率大于75%，响应时间1.6秒，通过NMON监控到的数据库资源使用情况，如下图一与图二：  
图一：  
[![1.png][]][1.png]  
图二  
[![2.png][]][2.png]

### 优化后 ###

数据库服务器资源使用率：  
图三  
[![3.png][]][3.png]  
图四  
[![4.png][]][4.png]

### 优化内容 ###

通过优化innndb等影响IO、内存的一些参数后，性能问题明显解决，优化参数内容，例如：innodb\_write\_io\_threads、 innodb\_read\_io\_threads、  
innodb\_flush\_log\_at\_trx\_commi等InnoDB 引擎优化IO 子系统参数配置若干。

[1.png]: /images/20220531/868f925f619b4177b8abe203f57b4952.png
[2.png]: /images/20220531/72e4bcf1c35543b4806ac73fa6dee539.png
[3.png]: /images/20220531/4fc49597634344d0a6b049b1c0b546ea.png
[4.png]: /images/20220531/f738d653592b4bbda4b4ce24d189a150.png