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) |
测试场景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取值方法(实例讲解)就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持脚本之家。
点击查看更多内容
为 TA 点赞
评论
共同学习，写下你的评论
评论加载中...
作者其他优质文章
正在加载中
感谢您的支持，我会继续努力的～
扫码打赏，你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫，即可进行扫码打赏哦