[问题]
有台 MySQL 5.6.21 的数据库实例以写入为主, IO %util 接近 100%
写入 IOPS 很高
[分析过程]
1, 通过 iotop 工具可以看到当前 IO 消耗最高的 MySQL 线程
2, 查看线程 49342 的堆栈, 可以看到正在进行 redo log 的刷新, 对应的是 9 号文件
3,9 号文件对应的是 redo log 的第一个文件
为什么 MySQL 进程会频繁的刷新 redo log 文件, 要结合 redolog 的刷盘策略来分析, 关键是 innodb_flush_log_at_trx_commit 参数,
默认是 1, 最安全, 但在写压力大的情况下, 也会带来较大的性能影响, 每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file, 并且 flush(刷到磁盘) 中去.
结合这个集群的写入场景来看, 大部分都是小事务的写入, 每次事务提交都会触发刷盘动作, 这种场景下通过增大 innodb_log_buffer_size 和 innodb_log_file_size 的优化效果不明显
[优化方案]
1, 应用层面, 对于写压力大的系统, 可以将单条的 insert 语句优化为小批量的 insert 语句, 这样事务 commit 的次数减少, redo log 刷盘减少, 性能理论上会有提升
2,MySQL 层面, 对于日志类型的系统, 如果允许宕机的情况下少量数据丢失, 可以将 innodb_flush_log_at_trx_commit 参数调整为 2,
当设置为 2 时, 则在事务提交时只做 write 操作, 只保证写到系统的 page cache, 因此实例 crash 不会丢失事务, 但宕机则可能丢失事务
在这台服务器上测试, 将参数调整为 2 时, IO 的请求从 200M/S 降到约 10M/S 压力会减少 10 倍以上
3, 系统层面, 更换性能更佳的磁盘
来源: https://www.cnblogs.com/wangdong/p/9814988.html