An alternative setting is
O_DIRECT_NO_FSYNC
: it uses theO_DIRECT
flag during flushing I/O, but skips thefsync()
system call afterwards. This setting is suitable for some types of filesystems but not others. For example, it is not suitable for XFS. If you are not sure whether the filesystem you use requires anfsync()
, for example to preserve all file metadata, useO_DIRECT
instead.
lock log -> mutexcopy last redo log block to a new blockwrite redo log buffer to diskunlock log -> mutexfsync redo log files
Results for all_direct are better at low-concurrency and O_DIRECT is faster for 4 or more concurrent connections.
1 2 4 8 16 32 64 128
1952 2777 3530 3755 3829 3741 3760 3803 all_direct
1608 2479 3507 4541 4550 4644 4698 4581 O_DIRECT
Mark Callaghan只是给出了测试的结果,但是并没有说明问题的原因。根据Mark的测试环境,我想这就是因为Group Commit效率问题所致。Percona版本提供了ALL_O_DIRECT来设置重做日志的打开标识,但是根据我的研究,这是没有必要的,用户也不需要过于迷信该参数。
MySQL 5.6开始InnoDB可以将重做日志文件组设置为最大512G,之前的限制为4G。这对SSD和写入密集型应用会带来明显的帮助。但是重做日志buffered I/O的问题是会导致使用过多的操作系统缓存,这也是为什么Mark会想到使用O_DIRECT的方式来打开重做日志。
既然不能在InnoDB内部处理该问题(至少目前),我想可以通过操作系统提供的接口来刷新操作系统中缓存的数据,从而减少内存过度的使用问题。例如:
sysctl -w vm.drop_caches = 3
-- EOF --