innodb_flush_method的幾個(gè)典型取值
| 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 |
也就是說，具體的取值跟硬件配置和工作負(fù)載相關(guān)，最好做一次壓測(cè)來決定。不過通常來說，linux環(huán)境下具有raid控制器和write-back寫策略，o_direct是比較好的選擇；如果存儲(chǔ)介質(zhì)是SAN，那么使用默認(rèn)fsync或者osync或許更好一些。
通常來說，貌似絕大部分人都取值o_direct，底層有raid卡，讀寫策略設(shè)置為write-back。在使用sysbench壓測(cè)oltp類型時(shí)，我發(fā)現(xiàn)o_direct確實(shí)比fsync性能優(yōu)秀一些，看來適用于大部分場(chǎng)景，但是最近碰到一個(gè)這樣的sql，客戶反饋很慢，而在相同內(nèi)存的情況下，它自己搭建的云主機(jī)執(zhí)行相對(duì)快很多，后來我發(fā)現(xiàn)主要就是innodb_flush_method的設(shè)置值不同帶來的巨大性能差異。
測(cè)試場(chǎng)景1
innodb_flush_method為默認(rèn)值，即fsync，緩存池512M，表數(shù)據(jù)量1.2G，排除緩存池影響，穩(wěn)定后的結(jié)果
