MYSQL数据库办事磁盘IO高问题剖析与优化

凡凡和波波

压力测试过程当中，假如由于资源使用瓶颈等问题诱发最间接机能问题是业务买卖响应时间偏大，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.3优化内容
经过优化innndb等影响IO、内存的一些参数后，机能问题显著解决，优化参数内容，例如：innodb_write_io_threads、 innodb_read_io_threads、
innodb_flush_log_at_trx_co妹妹i等InnoDB引擎优化IO子零碎参数配置若干。