db_005

关于InnoDB bufferpool的flush method

1. 关于innodb_flush_method参数

• 参考：细看INNODB数据落盘

innodb_flush_method	Open log	Flush log	Open datafile	Flush data
Fdatasync(default, 5.1.24后改名fsnyc)		fsync()		fsync()
O_DSYNC	O_SYNC			fsync()
O_DIRECT		fsync()	O_DIRECT	fsync()
O_DIRECT_NO_FSYNC		fsync()	O_DIRECT
All_O_DIRECT(percona)	O_DIRECT	fsync()	O_DIRECT	fsync()

• 参考：MySQL文档中对于innodb_flush_method的说明

How each settings affects performance depends on hardware configuration and workload. Benchmark your particular configuration to decide which setting to use, or whether to keep the default setting. Examine the Innodb_data_fsyncs status variable to see the overall number of fsync() calls for each setting. The mix of read and write operations in your workload can affect how a setting performs. For example, on a system with a hardware RAID controller and battery-backed write cache, O_DIRECT can help to avoid double buffering between the InnoDB buffer pool and the operating system's file system cache. On some systems where InnoDB data and log files are located on a SAN, the default value or O_DSYNC might be faster for a read-heavy workload with mostly SELECT statements. Always test this parameter with hardware and workload that reflect your production environment.

这段说明，用哪种flush设置取决于具体的硬件配置和工作负载，比如在有硬件RAID控制器和电源备份写cache的情况下，用O_DIRECT可以帮助系统避免InnoDB和OS的两层buffer/cache；在一些在SAN中的InnoDB数据的情况下，读负载要更加繁重，SELECT操作更多一些，这时用默认的fsync()或者O_DSYNC会更快一些。

2. 关于VFS层的文件flag和函数

• 参考：细看INNODB数据落盘

	page cache	buffer cache	inode cache	dictory cache
O_DIRECT	write bypass	write bypass	write & no flush	write & no flush
O_DSYNC/fdatasync()	write & flush	write & flush	write & no flush	write & no flush
O_SYNC/fsync()	write & flush	write & flush	write & flush	write & flush

3. Buffer Pool Flushing

MySQL文档: Configuring InnoDB Buffer Pool Flushing

MySQL文档: Fine-tuning InnoDB Buffer Pool Flushing

innodb_max_dirty_pages_pct_lwm 和 innodb_max_dirty_pages_pct 这两个参数，可以决定脏页什么时候开始回写和脏页最大不超过多大的比例，默认值分别为0和75。 innodb_adaptive_flushing 参数默认为True，可以允许自适应的调整flush算法（？）

// 待续。。。

4. flush大概步骤

flush需要一定方式，也需要决定在什么时间开始。flush的方式就是第1节中innodb_flush_method参数所指定的；而找到合适的flush时机是有很多影响因素的，有很多参数影响这个“决策”，这步是第3节中介绍的参数指定的。 系统运行时，当一定的情况下，根据我们的配置，达到了一定时机，buffer pool开始被flush，flush也是按我们指定的方式进行的。

5. 其他参考

MySQL文档中关于磁盘IO优化的内容 Optimizing InnoDB Disk I/O

Linux fflush 与 fsync的区别

O_DIRECT vs. O_SYNC on Linux/FreeBSD

open()函数man page

MySQL InnoDB性能调整的一点实践

mysql redo日志与刷脏

innodb_flush_method 与 File I/O

Understanding MySQL’s InnoDB buffer pool

InnoDB O_DIRECT选项漫谈