mysql飚满问题

有一台 mysql 5.7.1 的数据库实例以写入为主,IO 飙到接近100%,

1635421190

可以看到 写入 IOPS 很也高

1635421216

分析mysql飚满问题过程

  • 通过 iotop 工具可以看到当前 IO 消耗最高的 mysql 线程

1635421242

  • 查看线程49342的堆栈，可以看到正在进行redo log的刷新，对应的是9号文件

1635421283

  • 9号文件对应的是redo log的第一个文件

1635421300

　为什么 mysql 进程会频繁的刷新 redolog 文件,要结合 redolog 的刷盘策略来分析,关键是 innodb_flush_log_at_trx_commit 参数,默认是1最安全,但在写压力大的情况下,也会带来较大的性能影响,每次事务提交时 mysql 都会把 log buffer 的数据写入 log file 并且 flush (刷到磁盘)中去。

1635421410

　结合这个集群的写入场景来看,大部分都是小事务的写入,每次事务提交都会触发刷盘动作,这种场景下通过增大 innodb_log_buffer_size 和 innodb_log_file_size 的优化效果不明显。

1635421422

mysql飚满优化方案

  • 应用层面,对于写压力大的系统,可以将单条的insert语句优化为小批量的insert语句,这样事务commit的次数减少,redo log刷盘减少,性能理论上会有提升。
  • mysql 层面,对于日志类型的系统,如果允许宕机的情况下少量数据丢失,可以将 innodb_flush_log_at_trx_commit 参数调整为2,当设置为2时,则在事务提交时只做 write 操作,只保证写到系统的 page cache 因此实例 crash 不会丢失事务,但宕机则可能丢失事务,在这台服务器上测试,将参数调整为2时,IO的请求从 200M/S 降到约 10M/S 压力会减少10倍以上。

1635421678

  • 系统层面,可以更换性能更佳的磁盘