[摘要]下面小编就为大家带来一篇innodb_flush_method取值方法(实例讲解)。小编觉得挺不错的，现在就分享给大家，也给大家做个参考。一起跟随小编过来看看吧innodb_flush_method...
下面小编就为大家带来一篇innodb_flush_method取值方法(实例讲解)。小编觉得挺不错的，现在就分享给大家，也给大家做个参考。一起跟随小编过来看看吧
innodb_flush_method的几个典型取值
fsync: InnoDB uses the fsync() system call to flush both the data and log files. fsync is the default setting.
O_DSYNC: InnoDB uses O_SYNC to open and flush the log files, and fsync() to flush the data files. InnoDB does not use O_DSYNC directly because there have been problems with it on many varieties of Unix.
O_DIRECT: InnoDB uses O_DIRECT (or directio() on Solaris) to open the data files, and uses fsync() to flush both the data and log files. This option is available on some GNU/Linux versions,FreeBSD, and Solaris.
如何取值，mysql官方文档是这么建议的
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
也就是说，具体的取值跟硬件配置和工作负载相关，最好做一次压测来决定。不过通常来说，linux环境下具有raid控制器和write-back写策略，o_direct是比较好的选择；如果存储介质是SAN，那么使用默认fsync或者osync或许更好一些。
通常来说，貌似绝大部分人都取值o_direct，底层有raid卡，读写策略设置为write-back。在使用sysbench压测oltp类型时，我发现o_direct确实比fsync性能优秀一些，看来适用于大部分场景，但是最近碰到一个这样的sql，客户反馈很慢，而在相同内存的情况下，它自己搭建的云主机执行相对快很多，后来我发现主要就是innodb_flush_method的设置值不同带来的巨大性能差异。
测试场景1
innodb_flush_method为默认值，即fsync，缓存池512M，表数据量1.2G，排除缓存池影响，稳定后的结果
mysql> show variables like '%innodb_flush_me%';
+---------------------+-------+
关键词：案例详细说明mysql中innodb_flush_method办法