innodb_flush_method取值方法(實例講解)
更新時間：2017年03月22日 08:33:43 投稿：jingxian
下面小編就為大家?guī)硪黄猧nnodb_flush_method取值方法(實例講解)。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧
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環(huán)境下具有raid控制器和write-back寫策略，o_direct是比較好的選擇；如果存儲介質是SAN，那么使用默認fsync或者osync或許更好一些。
通常來說，貌似絕大部分人都取值o_direct，底層有raid卡，讀寫策略設置為write-back。在使用sysbench壓測oltp類型時，我發(fā)現(xiàn)o_direct確實比fsync性能優(yōu)秀一些，看來適用于大部分場景，但是最近碰到一個這樣的sql，客戶反饋很慢，而在相同內存的情況下，它自己搭建的云主機執(zhí)行相對快很多，后來我發(fā)現(xiàn)主要就是innodb_flush_method的設置值不同帶來的巨大性能差異。
測試場景1
innodb_flush_method為默認值，即fsync，緩存池512M，表數(shù)據(jù)量1.2G，排除緩存池影響，穩(wěn)定后的結果
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，排除緩存池影響，穩(wěn)定后的結果
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)
結果比較：
兩者執(zhí)行計劃一摸一樣，性能卻差距很大。在數(shù)據(jù)庫第一次啟動時的查詢結果也差距很大，o_direct也差很多（測試結果略）。不是很懂為啥這種情況下多了一層操作系統(tǒng)緩存，讀取效率就高了很多，生產(chǎn)環(huán)境設置一定要以壓測結果為準，實際效果為準，不能盲目信任經(jīng)驗值。
改進措施：
不改變innodb_flush_method的情況下，其實這條sql還可以進一步優(yōu)化，通過添加組合索引(account_id,outcome,income)，使得走覆蓋索引掃描，可大大地減少響應時間
以上這篇innodb_flush_method取值方法(實例講解)就是小編分享給大家的全部內容了，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關文章
mysql中l(wèi)eft join設置條件在on與where時的用法區(qū)別分析
這篇文章主要介紹了mysql中l(wèi)eft join設置條件在on與where時的用法區(qū)別,結合實例形式分析了mysql中l(wèi)eft join設置條件在on與where時的相關用法區(qū)別與操作注意事項,需要的朋友可以參考下2020-02-02
這篇文章主要介紹了MySQL Binlog 日志處理工具對比分析的相關資料，幫助大家更好的理解和學習使用MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下2021-03-03
這篇文章主要介紹了MySQL樂觀鎖和悲觀鎖具體實現(xiàn)，文章圍繞主題展開詳細的內容戒殺，具有一定的參考價值，需要的小伙伴可以參考一下2022-09-09
MySQL數(shù)據(jù)庫通常包含關鍵的數(shù)據(jù),為確保這些數(shù)據(jù)的安全和完整,需要利用訪問控制和用戶管理的功能,下面這篇文章主要給大家介紹了關于MySQL數(shù)據(jù)庫必知必會之安全管理的相關資料,需要的朋友可以參考下2022-05-05
本篇文章是對MySQL設置當前時間為默認值的方法進行了詳細的分析介紹，需要的朋友參考下2013-06-06
這篇文章主要為大家詳細介紹了MySQL中樂觀鎖扣減庫存原理的相關知識，文中的示例代碼講解詳細，感興趣的小伙伴可以跟隨小編一起了解一下2023-04-04
本文主要介紹了MySQL允許遠程登錄的操作實現(xiàn),文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2025-02-02
這篇文章主要介紹了MySQL中的事務機制,通過實例介紹了大概的流程,需要的朋友可以參考下2015-04-04
mysqli相對于mysql有很多優(yōu)勢,mysqli連接數(shù)據(jù)庫和mysqli預處理prepare使用,不僅如此，mysqli更是支持多查詢特性2012-12-12
這篇文章主要介紹了MySQL CPU過高的排查方法,通過top命令查看服務器CPU資源使用情況,明確CPU占用率較高的是否是mysqld進程,文章通過圖文介紹的非常詳細,需要的朋友可以參考下2023-11-11