Mysql磁盘IO过高的解决办法

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

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

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

由于我们的服务器近期IO过高,可以通过iostat查看具体那块盘IO过高

iostat -k -d -x 1 10

发现第二块盘%util持续100%,这块盘只提工了mysql服务,那么可以磁盘达到了IO性能瓶颈,我们尝试优化下mysql

通常我们可以尝试修改global sync_binlog变量的值,那么我们先去mysql中查看global sync_binlog变量设置的默认值

mysql -u root -p>
show variables like '%sync_binlog%'
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
1 row in set (0.01 sec)

看到了它的值是1,也就是说每次提交事务将binlog日志的缓存写入磁盘,影响了磁盘效率,这个值是可以优化的

set global sync_binlog=500;

我们将值优化到500,那么就是每500次事务才将binlog日志的缓存写入磁盘,这样就避免了缓存的频繁落盘

另外我们还需要看看innodb_flush_log_at_trx_commit变量

show variables like '%innodb_flush_log_at_trx_commit%'
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_timeout    | 1     |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
2 rows in set (0.00 sec)

设置为1,也就是说每次事务提交,都会将innodb日志缓存写入磁盘,对磁盘效率影响很大,将它设置为2,每次事务提交时mysql都会把log buffer的数据写入log file,但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作

set global innodb_flush_log_at_trx_commit=2

那么这样可以有效的降低磁盘IO,上述方式只是临时排查修改的变量解决,一旦mysql重启配置就会恢复,我们还需要把这个修改写入mysql配置中,这样mysql服务启动就会加载配置参数的值

最后我们可以再使用iotop命令查看下当前占用IO最高的进程

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://sulao.cn/post/727.html