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) |
