實例詳解mysql中innodb_flush_method方法

下面小編就為大家帶來一篇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官方文檔是這么建議的

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

也就是說,具體的取值跟硬件配置和工作負載相關,最好做一次壓測來決定。不過通常來說,linux環境下具有raidmysql和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,mysql池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)

結果比較:

兩者執行計劃一摸一樣,性能卻差距很大。在數據庫第一次啟動時的mysql結果也差距很大,o_direct也差很多(測試結果略)。不是很懂為啥這種情況下多了一層操作系統緩存,讀取效率就高了很多,生產環境設置一定要以壓測結果為準,實際效果為準,不能盲目信任經驗值。

改進措施:

不改變innodb_flush_method的情況下,其實這條sql還可以進一步優化,通過添加組合mysql(account_id,outcome,income),使得走覆蓋索引掃描,可大大地減少響應時間

【相關推薦】

1.?mysql

2.?mysql

3.?mysql

4.?mysql

5.?mysql

? 版權聲明
THE END
喜歡就支持一下吧
點贊12 分享