【原創】MySQL效能最佳化-I/O相關配置參數

標籤：安全   file   key   系統資料表   磁碟   mit   需要   寫入   系統   

　　本文介紹InnoDB和MyISAM兩種儲存引擎的I/O相關參數配置。

　　1.InnoDB  I/O相關配置

　　Innodb是一種事務型的儲存引擎，為了減少提交事務時產生的io開銷，innodb採用了寫日誌的方式，也就是在事務提交的時候會先寫入交易記錄中 ，而不是每次都把修改或者資料重新整理到資料檔案中，這樣做是為了提高io的效能，因為事務的修改，使資料和索引檔案通常都會映射到資料表空間隨機的位置，所以重新整理資料變更到資料檔案會產生大量隨機io，而記錄日誌是順序io，一旦交易記錄安全的寫到磁碟中，資料就算是持久化了，即使變更還沒有寫入到資料檔案，如果這時候伺服器宕機了，這樣的情況我們還是可以使用交易記錄來恢複已經提交的事務。以下是一些InnoDB儲存引擎相關的配置參數：

　　Innodb_log_file_size  ： 控制單個交易記錄檔的大小（如果業務繁忙可以設定大一些，一般可以記錄業務一個小時產生事務的日誌）；

　　Innodb_log_files_in_group  ： 控制交易記錄檔的個數 ；

　　交易記錄並不是每次提交都直接寫入記錄檔中的，而是先寫入到交易記錄的緩衝區，然後再重新整理到磁碟中。Innodb_log_buffer_size控制交易記錄緩衝區的大小，通常不需要把日誌緩衝區設定得非常大，因為一般情況下至少一秒鐘，就會進行一次交易記錄緩衝的重新整理，所以這個緩衝區只要可以儲存至少一秒鐘的事務就足夠了，當然稍微大一些也沒有什麼壞處，通常來說32M~128M這樣的配置就可以了。

　　所以 ， 交易記錄的總大小 = Innodb_log_files_in_group * Innodb_log_file_size 。

　　Innodb_flush_log_at_trx_commit 將log重新整理到磁碟的配置，有三種選項 ：

　　　　（1）0:每秒鐘進行一次log寫入作業系統cache，並flush log到磁碟（在事務提交時候並不會做任何事情，這個設定在mysql崩潰時會至少丟失一秒鐘的事務）；

　　　　（2）1[預設]:在每次事務提交執行log寫入cache，並fulsh log到磁碟（這是最安全的設定，不會丟失任何事務，但是效能並不理想） ；

　　　　（3）2[建議]：每次事務提交，執行log資料寫入到cache，每秒鐘執行一次flush log到磁碟。（完成事務持久化分為兩步 ：首先從mysql日誌緩衝區重新整理到作業系統的cache，然後從作業系統的cache重新整理到磁碟）；

　　2.MyISAM I/O相關配置

　　MyISAM也是常用的儲存引擎，MySQL的相關係統表就是使用MyISAM儲存引擎，MyISAM的I/O相關參數配置如下：

　　　　delay_key_write  : OFF 每次寫操作後重新整理鍵緩衝中的髒塊到磁碟；

　　　　　　　　　　　　　 ON 只對在鍵表時指定了delay_key_write選項的表使用延遲重新整理；

　　　　　　　　　　　　    ALL 對所有的MyISAM表都使用延遲鍵寫入；

【原創】MySQL效能最佳化-I/O相關配置參數