選擇合適的innodb_log_file_size
If you’re doing significant amount of writes to Innodb tables decent size of innodb_log_file_size is important for MySQL Performance. However setting it too large will increase recovery time, so in case of MySQL crash or power failure it may take long time before MySQL Server is Operational again.
如果對 Innodb 數據表有大量的寫入操作，那麼選擇合適的 innodb_log_file_size 值對提升MySQL性能很重要。然而設置太大了，就會增加恢復的時間，因此在MySQL崩潰或者突然斷電等情況會令MySQL服務器花很長時間來恢復。
So how to find the optimal combination ?
那麼，怎麼才能找到最佳的配置組合呢？
First let me explain what happens on recovery and why large innodb_log_file_size slows down recovery. During startup after crash Innodb scans log files to find log records which only have been applied in memory and do not exist in tablespace. Log records for modifications which did not make it to the tablespace are then applIEd. This is called redo phase of recovery. It can take pretty long time and this time depends on number of variables - how large are rows ? (smaller log records mean more records for same sized logs), how random were data modifications (random updates will need random IO to check if pages are up to date), number of unflushed pages in innodb buffer pool and its size as well as performance of IO subsystem. As there are so many factors, it is hard to come up with any general guidelines, something like 1GB per 10 minutes of recovery time - instead you would need to apply load which is typical for your application, crash MySQL in the middle and watch it to recover. Doing this several times you should be able to estimate how long recovery time take and adjust your logs apropriately. The good thing is - redo phase is close to be proportional to size of log files, so expect 1GB logs to take twice time to apply compared to 512MB logs.
首先，讓我先來解釋一下恢復時都發生了什麼事情以及為什麼設置 innodb_log_file_size 的值太大了會讓恢復過程變慢。Innodb 數據表崩潰再次啟動時，MySQL 會掃描日志文件來找到那個只應用到內存中並且不存在的表空間的日志記錄。那些沒有沒有放到表空間的修改日志記錄就要被加進去。這叫做重做相位恢復。這需要相當長時間，它取決於變量的值 -- 到底有多少行記錄？(日志記錄的值越小意味著同樣大小的日志裡可以存儲更多的記錄)，隨機數據修改的幾率有多高(隨機更新需要有更多的隨機IO來檢查內存頁是否更新)，innodb 緩沖池中未被刷新的內存頁數量並且它也是IO子系統的性能表現。由於有這麼多因素，就很難產生通用的准繩，例如每10分鐘恢復1GB數據的時長 -- 相反地，應該在典型的應用中來確定負載，在MySQL崩潰的過程中來監查它是怎麼恢復的。這麼做幾次之後，你就應該能大致估算恢復所需的時間了從而更恰當地調整日志大小。好事是 -- 重做相位和日志文件大小成正比，因此預計恢復1GB的日志所需的時間大致是512MB的2倍。
Redo phase is however only one of the phases of recovery. The other important one is undophase - after log file are applied and database is in “physically consistent” state, Innodb will need to roll back certain transactions which Where not commited, but changes from which already made it to the database. Unlike “redo” phase “undo” phase can’t be reduced by sizing your log files. Even more undo phase can be slower with small log files. Undo phase takes considerable time if tranactions are long - IE if you would delete 10000000 rows in the same transaction and crash in the middle recovery can take quite a long time. The only way you can reduce “undo” phase is size your transactions appropriately - so updates/inserts/deletes can be sized to affect limited number of rows.
然而重做相位是相位恢復的唯一方法。另一個重要的方法是撤銷相位 -- 當日志文件應用完之後並且數據庫處於 "物理一致性" 狀態時，Innodb 會回滾那些沒提交的事務，但是已經對數據庫所做的修改就不管了。不像 "重做" 相位，"撤銷" 相位不會因為日志尺寸變小而變快。甚至撤銷相位還可能因為日志較小而變慢。撤銷相位所耗時間因事務長短所致 -- 例如，如果需要在一個事務中刪除 10000000 行記錄，這個事務中途發生錯誤崩潰了，那麼恢復就需要花很長時間了。唯一能減少 "撤銷" 相位的方法是設置適當的日志大小值 -- 這樣的話，記錄更新/插入/刪除時就會被限定在有限的數量裡了。
Good thing about updo phase however is - it can be done in background as in MySQL 5.0. The rows affected by background rollback however might not be modifIEd until rollback is complete.
不過撤銷相位的好處是 -- 在MySQL 5.0中，它可以讓在後台來執行。後台回滾的記錄直至恢復完之後才能被修改。
One more thing to consider - how large log files do you need at all ? You could run benchmark with 1GB log filesand 2GB and see if there is any performance benefit. After certain size increasing log file size do not dramatically increase performance, however this again depends on configuration and workload.
另一個要考慮的事是 -- 到底需要多大的日志？可以運行基准測試來檢查 1GB 大小的日志相對 2GB 有什麼好處。日志文件增加到一定大小後未必會戲劇性地提高性能，然而這同樣依賴於配置以及MySQL的工作負載。
Note at this poing 4GB is maximum combined size allowed for innodb log files, which is however large enough limit for most onfigurations.
注意，這裡舉例中的 4GB 是 innodb 日志文件的最大值，不過它明顯比常用的配置大得多了。
