實例詳解mysql中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.

How?each?settings?affects?performance?depends?on?hardware?configuration?and?workload.?Benchmark  your?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?for  each?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_DIRECT  can?help?to?avoid?double?buffering?between?the?InnoDB?buffer?pool?and?the?operating?system's?file  system?cache.?On?some?systems?where?InnoDB?data?and?log?files?are?located?on?a?SAN,?the?default  value?or?O_DSYNC?might?be?faster?for?a?read-heavy?workload?with?mostly?SELECT?statements.?Always  test?this?parameter?with?hardware?and?workload?that?reflect?your?production?environment

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)

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)