MySQL 参数 io高_MYSQL5.6 IO参数浅析

在mysql数据库中，目前主流使用的引擎为INNODB， 在INNODB中，有几个参数涉及到了调用linux的读写函数

innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit参数确定日志文件何时写盘、flush。有3个值：

0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。

1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。

2：每次事务提交时mysql都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。

innodb_flush_method

innodb_flush_method参数专门用于定义innodb数据文件及redo log的打开、读写模式。

该参数有三个值：fsync(默认)，O_DSYNC，O_DIRECT，其中

fsync(默认值)：调用fsync()去刷数据文件与redo log的buffer

O_DSYNC：innodb会使用O_SYNC方式打开和刷写redo log,使用fsync()刷写数据文件

O_DIRECT：innodb使用O_DIRECT打开数据文件，使用fsync()刷写数据文件跟redo log

在mysql架构中，mysql的数据库buffer pool以及数据库日志 log buffer两块虚拟空间会根据innodb_flush_method进行不同的刷盘操作

在fsync下，buffer pool和log buffer都会通过fsync, 将存在Page Cache中的脏页进行刷盘，成功后返回。其中由于设置了innodb_flush_log_at_trx_commit=1， 因此对于log buffer来说每个事务都会调用一次fsync()函数。至于表空间的buffer pool何时调用fsync函数，则依赖于mysql上配置的脏页刷盘策略。

在fsync下，存在一个比较致命的问题则是，mysql在用户态有自己的buffer pool，假设为4GB，相关的内存页上的数据在进行储存时会经过内核态的Page Cache，即从用户态Buffer copy至内核态Page Cache。这种情况下就大致等于有了2份数据库的buffer，同时占据了一部分Page Cache作为数据库使用。

在O_DSYNC下，buffer pool仍然会通过fsync的方式落盘，但是log buffer则是会调用同步IO：O_DSYNC，必须等到IO落盘并返回成功后才结束。

在innodb_flush_log_at_trx_commit=1情况下，每次事务日志都需要同步落盘，由于同步IO是一个阻塞IO，因此在大多数场景下，该值的配置性能是三者中最差的。

在O_DIRECT下，数据库会通过direct方式打开文件，直接绕过了内核态的PageCache， 由于数据库自己有用户态的buffer，因此数据可以直接加载至用户态的buffer中(即mysql的Buffer Pool), 避免了PageCache用户buffer之间的copy操作。而日志文件仍然会采用fsync方式进行同步。

相对于fsync来说，传统的读一般会进行预读(read ahead), 会增加一定的IO操作，而在随机读过程中，预读的命中率一般并不高，而direct方式的读则不会有预读的IO消耗。因此在随机读较多的场景下，O_DIRECT的策略会略优于fsync策略。

从上面三种策略来看，O_DSYNC相对来说性能是最差的，mysql在很多情况下，还是要依托BINLOG的IO效率， 而在fsync和O_DIRECT模式下，日志的log buffer同样都是用fsync函数去调用的，因此总体来看这两种模式性能上差距不会太大，但是fsync模式会更占用内存空间。