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/' target='_blank'>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)

测试场景2

innodb_flush_method改为o_direct，排除缓存池影响，稳定后的结果

mysql> show variables like '%innodb_flush_me%';+---------------------+----------+| Variable_name    | Value  |+---------------------+----------+| innodb_flush_method | O_DIRECT |+---------------------+----------+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 (3.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 (3.02 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.00 sec)

结果比较：

两者执行计划一摸一样，性能却差距很大。在数据库第一次启动时的查询结果也差距很大，o_direct也差很多（测试结果略）。不是很懂为啥这种情况下多了一层操作系统缓存，读取效率就高了很多，生产环境设置一定要以压测结果为准，实际效果为准，不能盲目信任经验值。

改进措施：

不改变innodb_flush_method的情况下，其实这条sql还可以进一步优化，通过添加组合索引(account_id,outcome,income)，使得走覆盖索引扫描，可大大地减少响应时间

以上这篇innodb_flush_method取值方法(实例讲解)就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持脚本之家。