今天主要研究下關於mysql的redo log（事務日誌）的相關引數的設定問題，其中主要涉及到了三個引數的問題，

一：innodb_log_file_size ：該引數決定著mysql事務日誌檔案（ib_logfile0）的大小；

設定的太小：當一個日誌檔案寫滿後，innodb會自動切換到另外一個日誌檔案，而且會觸發資料庫的檢查點（Checkpoint），這會導致innodb快取髒頁的小批次重新整理，會明顯降低innodb的效能。由於日誌切換更頻繁，也就直接導致更多的BUFFER FLUSH，由於日誌切換的時候是不能BUFFER FLUSH的， BUFFER寫不下去，導致沒有多餘的buffer 寫redo， 那麼整個MYSQL就HANG住，還有一種情況是如果有一個大的事務，把所有的日誌檔案寫滿了，還沒有寫完，這樣就會導致日誌不能切換（因為例項恢復還需要，不能被迴圈複寫）這樣mysql就hang住了。可以根據檔案修改時間來判斷日誌檔案的旋轉頻率，旋轉頻率太頻繁，說明日誌檔案太小了。

設定的太大：設定很大以後減少了checkpoint，並且由於redo log是順序I/O，大大提高了I/O效能。但是如果資料庫意外出現了問題，比如意外當機，那麼需要重放日誌並且恢復已經提交的事務（也就是例項恢復中的前滾, 利用redo從演變化來恢復buffer cache中的資料），如果日誌很大，那麼將會導致恢復時間很長。甚至到我們不能接受的程度。

如果對 Innodb 資料表有大量的寫入操作，那麼選擇合適的 innodb_log_file_size 值對提升MySQL效能很重要，

如何去設定合適的innodb_log_file_size 大小呢？

一般來說，日誌檔案的全部大小，應該足夠容納伺服器一個小時的活動內容。

具體依據如下：我經常設定為 64-512MB

首先在業務高峰期，計算出1分鐘寫入的redo量，然後評估出一個小時的redo量；

MariaDB [(none)]> pager grep Log ##使用page之後，執行的命令只顯示 Log開頭的

PAGER set to 'grep Log'

MariaDB [(none)]> show engine innodb status\G select sleep(60); show engine innodb status\G;

Log sequence number 4578059050533

Log flushed up to 1149269980149

1 row in set (0.00 sec)

1 row in set (1 min 0.00 sec)

Log sequence number 4578062739081

Log flushed up to 1149270019005

1 row in set (0.00 sec)

MariaDB [(none)]> nopager

PAGER set to stdout

MariaDB [(none)]> select (4578062739081-4578059050533)/1024/1024 as MB;

+------------+

| MB |

+------------+

| 3.51767349 |

+------------+

1 row in set (0.00 sec)

注意Log sequence number，這是寫入事務日誌的總位元組數。所以，現在你可以看到每分鐘有多少MB日誌寫入（這裡的技術適用於所有版本的MySQL，在5.0及更高版本，你可以從SHOW GLOBAL STATUS的輸出看Innodb_os_log_written的值） 。

透過計算後得到每分鐘有3.5M的日誌寫入。

根據經驗法則。通常我們設定redo log size足夠大，能夠容納1個小時的日誌寫入量。

1小時日誌寫入量=3.5M * 60=210M，由於預設有兩個日誌重做日誌檔案ib_logfile0和ib_logfile1。在日誌組中的每個重做日誌檔案的大小一致，並以迴圈的方式寫入。innodb儲存引擎先寫重做日誌檔案0，當達到檔案的最後時，會切換到重做日誌1，並checkpoint。以此迴圈。

所以我們可以大約設定innodb_log_file_size=110M。注意：在innodb1.2.x版本之前，重做日誌檔案總的大小不得大於等於4G，而1.2.x版本將該限制擴大到了521G。

二：innodb_log_files_in_group

該引數控制日誌檔案數。預設值為2。mysql 事務日誌檔案是迴圈覆寫的。

需要注意的是：innodb_log_files_in_group是靜態的變數，需要以“乾淨”的方式更改並重新啟動，否則mysql啟動不起來。也就是說如果想把原來是2的修改成3，這樣的話你需要先關閉mysql服務，把原來的ib_logfile0和ib_logfile1檔案刪掉，然後啟動mysql，否則報錯如下所示：

直接修改my.cnf將該引數改為3的時候

重啟mysql，報錯，innodb引擎無法掛載

110124 14:06:23 InnoDB: Log file ./ib_logfile2 did not exist: new to be created

110124 14:06:23 [ERROR] Plugin 'InnoDB' init function returned error.

110124 14:06:23 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

三：innodb_log_buffer_size

該引數確保有足夠大的日誌緩衝區來儲存髒資料在被寫入到日誌檔案之前。

對於比較小的innodb_buffer_pool_size，建議是設定一樣大。 但是，對於比較大的innodb_buffer_pool_size，不建議這麼設定，這會存在一個潛在的問題，那就是當mysql掛掉時，恢復資料需要很久，造成大量的停機時間，當我們調整innodb_buffer_pool_size大小時，innodb_log_buffer_size和innodb_log_file_size也應該做出相應的調整。

四：innodb_log_group_home_dir

在事務被提交併寫入到表空間磁碟檔案上之前，事務日誌儲存在InnoDB的redo日誌檔案裡。這些日誌位於innodb_log_group_home_dir引數設定的目錄中，通常我們把這個目錄設定與innodb_data_home_dir變數相同。為了獲得最佳效能，建議分離innodb_data_home_dir和innodb_log_group_home_dir到單獨的物理儲存陣列上，這樣可以保證IO資源不起衝突，利於伺服器處理大量高併發連線。

小結：mysql的事務日誌相關的引數，基本介紹完畢了，影響日誌重新整理的效能的引數innodb_flush_log_at_trx_commit

具體分析innodb_flush_log_at_trx_commit=N的意義：

innodb_flush_log_at_trx_commit=0，每次commit時,事務日誌寫進了innodb log buffer ,然後每秒Log Thread 會將事務日誌從innodb log buffer重新整理到ib_ogfile(也就重新整理到了磁碟）。當innodb_flush_log_at_trx_commit設定為0，mysqld程式的崩潰會導致上一秒鐘所有事務資料的丟失，這是因為每次commit，事務日誌只是寫進了innodb log buffer 中，然後是每秒才將innodb log buffer 中的事務日誌重新整理到磁碟永久儲存，所以mysqld程式的崩潰時，innodb log buffer可能會有一秒的日誌沒有重新整理出來，但是在這種情況下，MySQL效能最好；

innodb_flush_log_at_trx_commit=2，每次commit時，事務日誌寫進了innodb log buffer，並同時接著寫進os cache, 也就是說每次commit，事務日誌寫進了os cache中， 然後每秒從os cache重新整理到ib_logfile(也就是重新整理到了磁碟）。當innodb_flush_log_at_trx_commit設定為2，只有在作業系統崩潰或者系統掉電的情況下，上一秒鐘所有事務資料才可能丟失，因為每次commit，事務日誌已經進入了os cache,所以mysqld崩潰，事務日誌是不會丟失的；

當innodb_flush_log_at_trx_commit設定為1，這是最安全的設定，同時由於頻繁的io操作，導致效率是最差的，這時候不管是mysqld，還是作業系統崩潰，都不會丟資料，這是因為每次commit,事務日誌都重新整理到了磁碟永久儲存了；

mysql innodb_log_file_size 和innodb_log_buffer_size引數

相關文章