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. Benchmarkyour 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 foreach 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_DIRECTcan help to avoid double buffering between the InnoDB buffer pool and the operating system's filesystem cache. On some systems where InnoDB data and log files are located on a SAN, the defaultvalue or O_DSYNC might be faster for a read-heavy workload with mostly SELECT statements. Alwaystest 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%';+---------------------+-------+| Variable_name | Value |+---------------------+-------+| innodb_flush_method | |+---------------------+-------+1 row in set (0.00 sec)mysql> SELECT sql_no_cache SUM(outcome)-SUM(income) FROM journal where account_id = '1c6ab4e7-main';+--------------------------+| SUM(outcome)-SUM(income) |+--------------------------+| -191010.51 |+--------------------------+1 row in set (1.22 sec)mysql> SELECT sql_no_cache SUM(outcome)-SUM(income) FROM journal where account_id = '1c6ab4e7-main';+--------------------------+| SUM(outcome)-SUM(income) |+--------------------------+| -191010.51 |+--------------------------+1 row in set (1.22 sec)mysql> explain SELECT sql_no_cache SUM(outcome)-SUM(income) FROM journal where account_id = '1c6ab4e7-main';+----+-------------+---------+------+---------------+------------+---------+-------+--------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+------+---------------+------------+---------+-------+--------+-----------------------+| 1 | SIMPLE | journal | ref | account_id | account_id | 62 | const | 161638 | Using index condition |+----+-------------+---------+------+---------------+------------+---------+-------+--------+-----------------------+1 row in set (0.03 sec) |
