innodb_flush_method取值方法(实例讲解)
innodb_flush_method的几个典型取值
fsync:InnoDBusesthefsync()systemcalltoflushboththedataandlogfiles.fsyncisthedefaultsetting. O_DSYNC:InnoDBusesO_SYNCtoopenandflushthelogfiles,andfsync()toflushthedatafiles.InnoDBdoesnotuseO_DSYNCdirectlybecausetherehavebeenproblemswithitonmanyvarietiesofUnix. O_DIRECT:InnoDBusesO_DIRECT(ordirectio()onSolaris)toopenthedatafiles,andusesfsync()toflushboththedataandlogfiles.ThisoptionisavailableonsomeGNU/Linuxversions,FreeBSD,andSolaris.
如何取值，mysql官方文档是这么建议的
Howeachsettingsaffectsperformancedependsonhardwareconfigurationandworkload.Benchmark yourparticularconfigurationtodecidewhichsettingtouse,orwhethertokeepthedefaultsetting. ExaminetheInnodb_data_fsyncsstatusvariabletoseetheoverallnumberoffsync()callsfor eachsetting.Themixofreadandwriteoperationsinyourworkloadcanaffecthowasettingperforms. Forexample,onasystemwithahardwareRAIDcontrollerandbattery-backedwritecache,O_DIRECT canhelptoavoiddoublebufferingbetweentheInnoDBbufferpoolandtheoperatingsystem'sfile systemcache.OnsomesystemswhereInnoDBdataandlogfilesarelocatedonaSAN,thedefault valueorO_DSYNCmightbefasterforaread-heavyworkloadwithmostlySELECTstatements.Always testthisparameterwithhardwareandworkloadthatreflectyourproductionenvironment
也就是说，具体的取值跟硬件配置和工作负载相关，最好做一次压测来决定。不过通常来说，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>showvariableslike'%innodb_flush_me%'; +---------------------+-------+ |Variable_name|Value| +---------------------+-------+ |innodb_flush_method|| +---------------------+-------+ 1rowinset(0.00sec) mysql>SELECTsql_no_cacheSUM(outcome)-SUM(income)FROMjournalwhereaccount_id='1c6ab4e7-main'; +--------------------------+ |SUM(outcome)-SUM(income)| +--------------------------+ |-191010.51| +--------------------------+ 1rowinset(1.22sec) mysql>SELECTsql_no_cacheSUM(outcome)-SUM(income)FROMjournalwhereaccount_id='1c6ab4e7-main'; +--------------------------+ |SUM(outcome)-SUM(income)| +--------------------------+ |-191010.51| +--------------------------+ 1rowinset(1.22sec) mysql>explainSELECTsql_no_cacheSUM(outcome)-SUM(income)FROMjournalwhereaccount_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|Usingindexcondition| +----+-------------+---------+------+---------------+------------+---------+-------+--------+-----------------------+ 1rowinset(0.03sec)
测试场景2
innodb_flush_method改为o_direct，排除缓存池影响，稳定后的结果
mysql>showvariableslike'%innodb_flush_me%'; +---------------------+----------+ |Variable_name|Value| +---------------------+----------+ |innodb_flush_method|O_DIRECT| +---------------------+----------+ 1rowinset(0.00sec) mysql>SELECTsql_no_cacheSUM(outcome)-SUM(income)FROMjournalwhereaccount_id='1c6ab4e7-main'; +--------------------------+ |SUM(outcome)-SUM(income)| +--------------------------+ |-191010.51| +--------------------------+ 1rowinset(3.22sec) mysql>SELECTsql_no_cacheSUM(outcome)-SUM(income)FROMjournalwhereaccount_id='1c6ab4e7-main'; +--------------------------+ |SUM(outcome)-SUM(income)| +--------------------------+ |-191010.51| +--------------------------+ 1rowinset(3.02sec) mysql>explainSELECTsql_no_cacheSUM(outcome)-SUM(income)FROMjournalwhereaccount_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|Usingindexcondition| +----+-------------+---------+------+---------------+------------+---------+-------+--------+-----------------------+ 1rowinset(0.00sec)
结果比较：
两者执行计划一摸一样，性能却差距很大。在数据库第一次启动时的查询结果也差距很大，o_direct也差很多（测试结果略）。不是很懂为啥这种情况下多了一层操作系统缓存，读取效率就高了很多，生产环境设置一定要以压测结果为准，实际效果为准，不能盲目信任经验值。
改进措施：
不改变innodb_flush_method的情况下，其实这条sql还可以进一步优化，通过添加组合索引(account_id,outcome,income)，使得走覆盖索引扫描，可大大地减少响应时间
以上这篇innodb_flush_method取值方法(实例讲解)就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持毛票票。