mysql innodb_log_file_size 和innodb_log_buffer_size參數
今天主要研究下關于
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,事務日志都刷新到了磁盤永久保存了；
