EmergencyEMERGENCY? Get 24/7 Help Now!

Choosing proper innodb_log_file_size

 | July 3, 2006 |  Posted In: Insight for DBAs


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.

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.

Redo phase is however only one of the phases of recovery. The other important one is undo phase – after log file are applied and database is in “physically consistent” state, Innodb will need to roll back certain transactions which were 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.

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.

One more thing to consider – how large log files do you need at all ? You could run benchmark with 1GB log files and 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.

Note at this poing 4GB is maximum combined size allowed for innodb log files, which is however large enough limit for most onfigurations.

Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.


  • Am I reading this correctly, I need to just keep crashing the database and watching recovery times to size the log correctly?

  • […] Innodb Recovery – Unless you have some hardware problems (99%) or found new Innodb bug (1%) Innodb recovery should be automatic and bring your database to consistent state. Depending on innodb_flush_lot_at_trx_commit setting you may lose few last committed transactions but it is it. It is Performance of this process which may cause the problems. As I already wrote innodb_log_file_size and innodb_buffer_pool_size affect recovery time significantly as well as your workload. I should also mention if you have innodb_file_per_table=1 your recovery speed will depend on number of Innodb tables you have, as well as many other operations, so beware. […]

  • I’m a litter confused with the following sentence. “Innodb will need to roll back certain transactions which were commited, but changes from which already made it to the database.”. Why rollback the transactions which were commited? If the a transaction is commited, according to the ACID property of transaction, it should not be rolled back.

  • […] 原文作者: Peter Zaitsev原文来源: http://www.mysqlperformanceblog.com/2006/07/03/choosing-proper-innodb_log_file_size/译者:叶金荣(Email:),转载请注明译者和出处,并且不能用于商业用途,违者必究。 […]

  • I am a little confused by this line:
    Note at this poing 4GB is maximum combined size allowed for innodb log files

    My question is what is combined to get this 4GB maximum? Is it innodb_log_files_in_group * innodb_log_file_size or is there another part of equation I am missing?

  • @john:
    A VPS is a tricky thing – you dont own the hardware, and maybe other people’s OSes and DBs are writing to the same physical disk at the same time as you because it is *V*PS. I have had past experience on VPS of being slowed down a lot due to some other user’s VM doing heavy activity on the disk. Also, my DB has also slowed down other user’s apps.

    IMO, you have to test it out on a copy / testing DB in peak time and off-peak hours for your location.
    VPS and shared hosting are unreliable in that context – tuning might work superb today, average tomorrow, and occasionally backfire too.

Leave a Reply


Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.