實例詳解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環境下有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> 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)

結果比較：

兩者執行計劃一摸一樣，性能卻差距很大。在

資料庫第一次啟動時的查詢結果也差距很大，o_direct也差很多（測試結果略）。不是很懂為啥這種情況下多了一層操作系統緩存，讀取效率就高了很多，生產環境設定一定要以壓測結果為準，實際效果為準，不能盲目信任經驗值。

改進措施：

不改變innodb_flush_method的情況下，其實這條sql還可以進一步優化，透過加入組合

索引(account_id,outcome,income)，使得走覆蓋索引掃描，可大幅減少回應時間

【相關推薦】

##1. 

Mysql免費影片教學

2. 

MySQL中新增使用者權限的實例詳解

#3. 

MySQL修改密碼和存取限制的實例詳解

#4. 

以正規表示式取代資料庫中的內容的實例詳 解

5. 

php將圖片儲存mysql中的實例詳解

以上是實例詳解mysql中innodb_flush_method方法的詳細內容。更多資訊請關注PHP中文網其他相關文章！