MySQL InnoDB效能調整的一點實踐

因為JavaEye網站的資料庫伺服器搬家的時候被託管商的工作人員狠狠摔了一下，所以硬碟整個掛掉了，我重新安裝資料庫伺服器的時候，順手下載了Percona patch過的MySQL5.0版本，使用MySQL自帶的heavy innodb配置檔案改了改，作為my.cnf啟動執行。資料庫伺服器的實體記憶體有6GB，其中有4GB可以被MySQL使用，my.cnf相關配置引數如下：

memlock
innodb_buffer_pool_size = 2G
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
#innodb_flush_method=fdatasync 預設設定

buffer pool越大越好，官方推薦使用實體記憶體的50%－80%；log_file_size也是越大越好，官方推薦log size加起來要達到buffer pool的25%－100%。使用memlock可以避免MySQL記憶體進入swap，這些都是預設的推薦配置了，沒有什麼可以質疑的地方。但是資料庫伺服器啟動以後，執行不太正常。表現出來的現象是：

1、作業系統記憶體Disk Cache使用了2.7GB
2、作業系統swap空間使用了200MB左右，一直不停進行swap in/swap out
3、CPU的IO Wait偏高，平均在10%以上

這個現象看起來非常怪異和矛盾。IO Wait偏高顯然是因為頻繁的使用swap進行記憶體換頁引起的，但問題是實體記憶體非常空閒，作業系統明明有2.7GB空閒實體記憶體做Disk Cache，怎麼不吐出來一點，非要去用swap呢？

想來想去只有一種可能性，就是MySQL存在非常巨大的，頻繁的檔案讀寫操作，迫使作業系統不得不分配了2.7GB的Disk Cache，從而造成了實體記憶體的不足，被迫使用swap。而可能造成巨大檔案讀寫操作的就是buffer pool的flush和log file的flush操作了。因此配置檔案做如下修改：

memlock
innodb_buffer_pool_size = 2G
innodb_log_file_size = 64M
innodb_log_files_in_group = 2
innodb_flush_method=O_DIRECT

減少log file size和數量，使用O_DIRECT。重啟以後，資料庫伺服器恢復正常。作業系統Disk Cache下降到900MB，Swap使用了200多MB，但是不再進行swap in/swap out操作，CPU的IO Wait下降到2-3%。

通過這次MySQL InnoDB的調優經歷，發現一些和MySQL官方推薦配置不符合的疑惑之處，值得思考和探索：

1、innodb_flush_method究竟應不應該使用O_DIRECT？

所有MySQL調優的建議都說，如果硬體沒有預讀功能，那麼使用O_DIRECT將極大降低InnoDB的效能，因為O_DIRECT跳過了作業系統的檔案系統Disk Cache，讓MySQL直接讀寫磁碟了。

但是在我的實踐中來看，如果不使用O_DIRECT，作業系統被迫開闢大量的Disk Cache用於innodb的讀寫快取，不但沒有提高讀寫效能，反而造成讀寫效能急劇下降。而且buffer pool的資料快取和作業系統Disk Cache快取造成了Double buffer的浪費，顯然從我這個實踐來看，浪費得非常厲害。

說O_DIRECT造成MySQL直接讀寫磁碟造成得效能下降問題，我覺得完全是杞人憂天。因為從JavaEye的資料庫監測來看，Innodb的buffer pool命中率非常高，有98%以上，真正的磁碟操作是微乎其微的。為了1%的磁碟操作能夠得到Disk Cache，而浪費了98%的double buffer記憶體空間，無論從效能上看，還是從記憶體資源的消耗來看，都是非常不明智的。

2、innodb_log_file_size究竟應該大一點，還是小一點？

所有MySQL調優建議都說，innodb_log_file_size要越大越好，避免無謂的buffer pool的flush操作。

但是在我的實踐中來看，innodb_log_file_size開得太大，會明顯增加innodb的log寫入操作，而且會造成作業系統需要更多的Disk Cache開銷。

因此從我的經驗來看，innodb_flush_method=O_DIRECT是必須的，而innodb_log_file_size也不宜太大。