服务器IO瓶颈对MySQL性能的影响

【背景】

之前我们碰到一些MySQL的性能问题，比如服务器日志备份时可能会导致慢查询增多，一句简单的select或insert语句可能执行几秒，IO负载较高的服务器更容易出现并发线程数升高，CPU上升等问题。

最近学习了MySQL InnoDB IO相关的部分内核原理，可以帮我们了解服务器IO瓶颈对MySQL性能的影响，下面以MySQL5.7.23的源码为例

【原理】

1、InnoDB实现了同步IO和异步IO两种文件读写方式

(1、)对于读操作，通常用户线程触发的数据请求都是同步读，其他后台线程触发的是异步读。

(2、)对于写操作，InnoDB是WAL模式，先写日志，延迟写数据页；

redo log的写操作大部分是异步写，主要在下面场景下触发

<1、>redo log buffer空间不足时；

<2、>当参数innodb_flush_log_at_trx_commit设置为1时，每次事务提交都会做一次fsync，相当于是同步写；

<3、>master线程每秒做一次redo fsync；

<4、>checkpoint

<5、>实例shutdown时

<.6、>binlog切换时

 Page cleaner线程负责脏页的刷新操作，其中double write buffer的写磁盘是同步写，数据文件的写入是异步写。

 

2、同步读写操作通常由用户线程来完成，下面先分析同步读

当用户线程执行一句SQL时，如果请求的数据页不在buffer pool中，就需要将文件中的数据页加载到buffer pool中，

从函数buf_read_page可以看到这里是同步读操作，如果IO有瓶颈，响应延迟，那么该线程就会被阻塞。

从函数buf_page_init_for_read可以看到，在读数据页时会加X锁

这时如果有其他用户线程请求相同的数据页时，从函数buf_wait_for_read看到，尝试获取X锁，就会处于阻塞状态。

当服务器IO成为瓶颈，发生上面的问题时，就会出现SQL执行变慢

问题进一步恶化，大量慢查询，运行中的线程处于等待状态，占用了Innodb线程（innodb_thread_concurrency我们的配置大部分是0或64，实际上通常是CPU的逻辑核数40）

对于并发较高的系统，会导致其他大量的线程处于等待队列中，并发线程过高又会导致上下文切换频繁，CPU上升。

 

3、一个同步写的例子

前面做过一个测试，执行500W条insert语句

用source执行insert脚本，TPS大约在每秒700，后面并行同时执行3个insert脚本，TPS达到每秒2000左右，IO %util已经接近100%

由于此时参数innodb_flush_log_at_trx_commit设置为1时，每次事务提交都会做一次fsync，相当于是同步写，IO已达到瓶颈，TPS处理能力无法提高。

当将参数innodb_flush_log_at_trx_commit临时调整为2，改为后台进程进行异步写，并行执行8个insert脚本，TPS达到每秒约1W左右，IO %util约在8%。

实现逻辑可以关注log_write_up_to函数

 

【应用场景】

1、当服务器IO出现瓶颈，会导致MySQL性能大幅下降，因此建议尽可能的利用服务器内存资源，将实例的innodb_buffer_pool_size设置为物理内存的70%左右；

2、合理的拆分，尽可能的让一个实例的热点数据都可以缓存在innodb buffer pool中

3、对于某些场景下执行脚本，或初始化数据时，可以将innodb_flush_log_at_trx_commit临时设置为2，能大幅提升导入性能。

参考资料：

http://mysql.taobao.org/monthly/2017/03/01/

http://mysql.taobao.org/monthly/2016/02/02/

http://mysql.taobao.org/monthly/2017/07/10/