[转]记一次mysql占用大量写I/O的优化

admin 2019-03-19 AM 454℃ 0条

转自http://www.hongyanzi.top/1033.html

近两天收到性能监控报警,每到业务高峰期就出现服务器IO写超高,导致整个业务速度变慢

 

 

 

 

 

 

 

 

登陆服务器用iotop查看磁盘I/O发现占用较大的是mysql进程

进入mysql查看show full processlist,发现基本上每次io队列较高时都是在update时,以为是更新语句有问题,于是打开mysql慢查询日志,观察一段时间磁盘io仍然较高,但是发现并没有慢查询语句;

查找关于mysql IO问题优化资料,看到 sync_binlog和innodb_flush_log_at_trx_commit 两个参数,这两个参数是控制MySQL 磁盘写入策略以及数据安全性的关键参数

innodb_flush_log_at_trx_commit

如果innodb_flush_log_at_trx_commit设置为0,log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行.该模式下,在事务提交的时候,不会主动触发写入磁盘的操作。
如果innodb_flush_log_at_trx_commit设置为1,每次事务提交时MySQL都会把log buffer的数据写入log file,并且flush(刷到磁盘)中去.
如果innodb_flush_log_at_trx_commit设置为2,每次事务提交时MySQL都会把log buffer的数据写入log file.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。

注意:由于进程调度策略问题,这个“每秒执行一次 flush(刷到磁盘)操作”并不是保证100%的“每秒”。

sync_binlog

sync_binlog 的默认值是0,像操作系统刷其他文件的机制一样,MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。

当sync_binlog =N (N>0) ,MySQL 在每写 N次 二进制日志binary log时,会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

注:如果启用了autocommit,那么每一个语句statement就会有一次写操作;否则每个事务对应一个写操作。

 

在部署数据库服务器之初没有做太多优化,配置基本保持默认,所以在每次事务提交都对应一次写操作,导致写操作太过频繁。

修改sync_binlog和innodb_flush_log_at_trx_commit参数

set global sync_binlog=500;
set global innodb_flush_log_at_trx_commit=2;

修改完sync_binlog和innodb_flush_log_at_trx_commit两个参数后,观察一段时间发现CPU的IOwait明显降低

标签: none

非特殊说明,本博所有文章均为博主原创。