下面小編就為大家帶來一篇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