MYSQL-IO过高解决方案

约定不等于承诺〃 2022-10-10 12:46 237阅读 0赞

**1、日志产生的性能影响:**

由于日志的记录带来的直接性能损耗就是数据库系统中最为昂贵的IO资源。MySQL的日志包括错误日志（ErrorLog），更新日志（UpdateLog），二进制日志（Binlog），查询日志（QueryLog），慢查询日志（SlowQueryLog）等。当然，更新日志是老版本的MySQL才有的，目前已经被二进制日志替代。

在默认情况下，系统仅仅打开错误日志，关闭了其他所有日志，以达到尽可能减少IO损耗提高系统性能的目的。但是在一般稍微重要一点的实际应用场景中，都至少需要打开二进制日志，因为这是MySQL很多存储引擎进行增量备份的基础，也是MySQL实现复制的基本条件。有时候为了进一步的性能优化，定位执行较慢的SQL语句，很多系统也会打开慢查询日志来记录执行时间超过特定数值（由我们自行设置）的SQL语句。

一般情况下，在生产系统中很少有系统会打开查询日志。因为查询日志打开之后会将MySQL中执行的每一条Query都记录到日志中，会该系统带来比较大的IO负担，而带来的实际效益却并不是非常大。一般只有在开发测试环境中，为了定位某些功能具体使用了哪些SQL语句的时候，才会在短时间段内打开该日志来做相应的分析。所以，在MySQL系统中，会对性能产生影响的MySQL日志（不包括各存储引擎自己的日志）主要就是Binlog了。

**2、 SQL写法问题引起磁盘IO高(可能会是服务当中其他任务，或者执行到某个接口的SQL导致IO突然飙升)**

例如：曾经在做某一个项目时，在看到数据库磁盘IO使用率偏高，前端查询业务交易loadrunner显示事物响应时间偏长，通过监控工具抓取对应SQL，通过计划分析，发现该SQL 中使用distinct 又多表关联且是大表、然后使用order by，最终显示10笔数据，而在产生中间过程数据进行筛选时，使用的是临时表，并把数据放入临时表中，内存刚好设置不大，于是放到磁盘中导致IO偏高。

备注：MySQL在执行SQL查询时可能会用到临时表，临时表存储，MySQL会先创建内存临时表，但内存临时表超过配置指定的值后，MySQL会将内存临时表导出到磁盘临时表；

**3、mysql内执行如下指令：**

set global sync\_binlog=500;

当每进行500次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog\_cache中的数据强制写入磁盘。

set global innodb\_flush\_log\_at\_trx\_commit=2;

默认值1代表每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电池供电缓存（Battery backed up cache）时。设置为2代表不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值设置为2只会在整个操作系统宕机时才可能丢数据。

优化内容

通过优化innndb等影响IO、内存的一些参数后，性能问题明显解决，优化参数内容，例如：innodb\_write\_io\_threads、 innodb\_read\_io\_threads、

innodb\_flush\_log\_at\_trx\_commi等InnoDB 引擎优化IO 子系统参数配置若干。