案例詳細(xì)說(shuō)明mysql中innodb_flush_method方法

innodb_flush_method的幾個(gè)典型取值

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

也就是說(shuō)，具體的取值跟硬件配置和工作負(fù)載相關(guān)，最好做一次壓測(cè)來(lái)決定。不過(guò)通常來(lái)說(shuō)，linux環(huán)境下具有raid控制器和write-back寫(xiě)策略，o_direct是比較好的選擇；如果存儲(chǔ)介質(zhì)是SAN，那么使用默認(rèn)fsync或者osync或許更好一些。

通常來(lái)說(shuō)，貌似絕大部分人都取值o_direct，底層有raid卡，讀寫(xiě)策略設(shè)置為write-back。在使用sysbench壓測(cè)oltp類型時(shí)，我發(fā)現(xiàn)o_direct確實(shí)比f(wàn)sync性能優(yōu)秀一些，看來(lái)適用于大部分場(chǎng)景，但是最近碰到一個(gè)這樣的sql，客戶反饋很慢，而在相同內(nèi)存的情況下，它自己搭建的云主機(jī)執(zhí)行相對(duì)快很多，后來(lái)我發(fā)現(xiàn)主要就是innodb_flush_method的設(shè)置值不同帶來(lái)的巨大性能差異。

測(cè)試場(chǎng)景1

innodb_flush_method為默認(rèn)值，即fsync，緩存池512M，表數(shù)據(jù)量1.2G，排除緩存池影響，穩(wěn)定后的結(jié)果

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)

測(cè)試場(chǎng)景2

innodb_flush_method改為o_direct，排除緩存池影響，穩(wěn)定后的結(jié)果

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)

結(jié)果比較：

兩者執(zhí)行計(jì)劃一摸一樣，性能卻差距很大。在數(shù)據(jù)庫(kù)第一次啟動(dòng)時(shí)的查詢結(jié)果也差距很大，o_direct也差很多（測(cè)試結(jié)果略）。不是很懂為啥這種情況下多了一層操作系統(tǒng)緩存，讀取效率就高了很多，生產(chǎn)環(huán)境設(shè)置一定要以壓測(cè)結(jié)果為準(zhǔn)，實(shí)際效果為準(zhǔn)，不能盲目信任經(jīng)驗(yàn)值。

改進(jìn)措施：

不改變innodb_flush_method的情況下，其實(shí)這條sql還可以進(jìn)一步優(yōu)化，通過(guò)添加組合索引(account_id,outcome,income)，使得走覆蓋索引掃描，可大大地減少響應(yīng)時(shí)間

【相關(guān)推薦】

1. Mysql免費(fèi)視頻教程

2. MySQL中添加新用戶權(quán)限的實(shí)例詳解

3. MySQL修改密碼和訪問(wèn)限制的實(shí)例詳解

4. 用正則表達(dá)式替換數(shù)據(jù)庫(kù)中的內(nèi)容的實(shí)例詳 解

5. php將圖片儲(chǔ)存mysql中的實(shí)例詳解

以上就是實(shí)例詳解mysql中innodb_flush_method方法的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！