【背景】

之前我们碰到一些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有瓶颈,响应延迟,那么该线程就会被阻塞。

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

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

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

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

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

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

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

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

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

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函数

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

【应用场景】

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/

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

11-07 12:31