选择合适的innodb_log_file_size

核心提示：Choosing proper innodb_log_file_size选择合适的innodb_log_file_sizeIf you’re doing significant amount of writes to Innodb tables decent size of innodb_log_file_

Choosing proper innodb_log_file_size

选择合适的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.