mysql innodb_log_file_size 和innodb_log_buffer_size參數(shù)
今天主要研究下關(guān)于
mysql的redo log（事務(wù)日志）的相關(guān)參數(shù)的設(shè)置問題，其中主要涉及到了三個參數(shù)的問題，
一：innodb_log_file_size ：該參數(shù)決定著mysql事務(wù)日志文件（ib_logfile0）的大小；
設(shè)置的太小：當(dāng)一個日志文件寫滿后，innodb會自動切換到另外一個日志文件，而且會觸發(fā)數(shù)據(jù)庫的檢查點（Checkpoint），這會導(dǎo)致innodb緩存臟頁的小批量刷新，會明顯降低innodb的性能。由于日志切換更頻繁，也就直接導(dǎo)致更多的BUFFER FLUSH，由于日志切換的時候是不能BUFFER FLUSH的， BUFFER寫不下去，導(dǎo)致沒有多余的buffer 寫redo， 那么整個MYSQL就HANG住，還有一種情況是如果有一個大的事務(wù)，把所有的日志文件寫滿了，還沒有寫完，這樣就會導(dǎo)致日志不能切換（因為實例恢復(fù)還需要，不能被循環(huán)復(fù)寫）這樣mysql就hang住了?？梢愿鶕?jù)文件修改時間來判斷日志文件的旋轉(zhuǎn)頻率，旋轉(zhuǎn)頻率太頻繁，說明日志文件太小了。
設(shè)置的太大：設(shè)置很大以后減少了checkpoint，并且由于redo log是順序I/O，大大提高了I/O性能。但是如果數(shù)據(jù)庫意外出現(xiàn)了問題，比如意外宕機，那么需要重放日志并且恢復(fù)已經(jīng)提交的事務(wù)（也就是實例恢復(fù)中的前滾, 利用redo從演變化來恢復(fù)buffer cache中的數(shù)據(jù)），如果日志很大，那么將會導(dǎo)致恢復(fù)時間很長。甚至到我們不能接受的程度。
如果對 Innodb 數(shù)據(jù)表有大量的寫入操作，那么選擇合適的 innodb_log_file_size 值對提升MySQL性能很重要，
如何去設(shè)置合適的innodb_log_file_size 大小呢？
一般來說，日志文件的全部大小，應(yīng)該足夠容納服務(wù)器一個小時的活動內(nèi)容。
具體依據(jù)如下：我經(jīng)常設(shè)置為 64-512MB
首先在業(yè)務(wù)高峰期，計算出1分鐘寫入的redo量，然后評估出一個小時的redo量；
MariaDB [(none)]> pager grep Log ##使用page之后，執(zhí)行的命令只顯示 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，這是寫入事務(wù)日志的總字節(jié)數(shù)。所以，現(xiàn)在你可以看到每分鐘有多少MB日志寫入（這里的技術(shù)適用于所有版本的MySQL，在5.0及更高版本，你可以從SHOW GLOBAL STATUS的輸出看Innodb_os_log_written的值） 。
通過計算后得到每分鐘有3.5M的日志寫入。
根據(jù)經(jīng)驗法則。通常我們設(shè)置redo log size足夠大，能夠容納1個小時的日志寫入量。
1小時日志寫入量=3.5M * 60=210M，由于默認有兩個日志重做日志文件ib_logfile0和ib_logfile1。在日志組中的每個重做日志文件的大小一致，并以循環(huán)的方式寫入。innodb存儲引擎先寫重做日志文件0，當(dāng)達到文件的最后時，會切換到重做日志1，并checkpoint。以此循環(huán)。
所以我們可以大約設(shè)置innodb_log_file_size=110M。注意：在innodb1.2.x版本之前，重做日志文件總的大小不得大于等于4G，而1.2.x版本將該限制擴大到了521G。
二：innodb_log_files_in_group
該參數(shù)控制日志文件數(shù)。默認值為2。mysql 事務(wù)日志文件是循環(huán)覆寫的。
需要注意的是：innodb_log_files_in_group是靜態(tài)的變量，需要以“干凈”的方式更改并重新啟動，否則mysql啟動不起來。也就是說如果想把原來是2的修改成3，這樣的話你需要先關(guān)閉mysql服務(wù)，把原來的ib_logfile0和ib_logfile1文件刪掉，然后啟動mysql，否則報錯如下所示：
直接修改my.cnf將該參數(shù)改為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
該參數(shù)確保有足夠大的日志緩沖區(qū)來保存臟數(shù)據(jù)在被寫入到日志文件之前。
對于比較小的innodb_buffer_pool_size，建議是設(shè)置一樣大。 但是，對于比較大的innodb_buffer_pool_size，不建議這么設(shè)置，這會存在一個潛在的問題，那就是當(dāng)mysql掛掉時，恢復(fù)數(shù)據(jù)需要很久，造成大量的停機時間，當(dāng)我們調(diào)整innodb_buffer_pool_size大小時，innodb_log_buffer_size和innodb_log_file_size也應(yīng)該做出相應(yīng)的調(diào)整。
四：innodb_log_group_home_dir
在事務(wù)被提交并寫入到表空間磁盤文件上之前，事務(wù)日志存儲在InnoDB的redo日志文件里。這些日志位于innodb_log_group_home_dir參數(shù)設(shè)置的目錄中，通常我們把這個目錄設(shè)置與innodb_data_home_dir變量相同。為了獲得最佳性能，建議分離innodb_data_home_dir和innodb_log_group_home_dir到單獨的物理存儲陣列上，這樣可以保證IO資源不起沖突，利于服務(wù)器處理大量高并發(fā)連接。
小結(jié)：mysql的事務(wù)日志相關(guān)的參數(shù)，基本介紹完畢了，影響日志刷新的性能的參數(shù)innodb_flush_log_at_trx_commit
具體分析innodb_flush_log_at_trx_commit=N的意義：
innodb_flush_log_at_trx_commit=0，每次commit時,事務(wù)日志寫進了innodb log buffer ,然后每秒Log Thread 會將事務(wù)日志從innodb log buffer刷新到ib_ogfile(也就刷新到了磁盤）。當(dāng)innodb_flush_log_at_trx_commit設(shè)置為0，mysqld進程的崩潰會導(dǎo)致上一秒鐘所有事務(wù)數(shù)據(jù)的丟失，這是因為每次commit，事務(wù)日志只是寫進了innodb log buffer 中，然后是每秒才將innodb log buffer 中的事務(wù)日志刷新到磁盤永久保存，所以mysqld進程的崩潰時，innodb log buffer可能會有一秒的日志沒有刷新出來，但是在這種情況下，MySQL性能最好；
innodb_flush_log_at_trx_commit=2，每次commit時，事務(wù)日志寫進了innodb log buffer，并同時接著寫進os cache, 也就是說每次commit，事務(wù)日志寫進了os cache中， 然后每秒從os cache刷新到ib_logfile(也就是刷新到了磁盤）。當(dāng)innodb_flush_log_at_trx_commit設(shè)置為2，只有在操作系統(tǒng)崩潰或者系統(tǒng)掉電的情況下，上一秒鐘所有事務(wù)數(shù)據(jù)才可能丟失，因為每次commit，事務(wù)日志已經(jīng)進入了os cache,所以mysqld崩潰，事務(wù)日志是不會丟失的；
當(dāng)innodb_flush_log_at_trx_commit設(shè)置為1，這是最安全的設(shè)置，同時由于頻繁的io操作，導(dǎo)致效率是最差的，這時候不管是mysqld，還是操作系統(tǒng)崩潰，都不會丟數(shù)據(jù)，這是因為每次commit,事務(wù)日志都刷新到了磁盤永久保存了；
