Choosing proper innodb_log_file_size

Choosing proper innodb_log_file_size

PREVIOUS POST
NEXT POST

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.

PREVIOUS POST
NEXT POST

Share this post

Comments (12)

  • Tim Reply

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

    July 3, 2006 at 12:00 am
  • MySQL Performance Blog » MySQL Crash Recovery Reply

    […] 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. […]

    July 30, 2006 at 11:09 am
  • Yuan WANG Reply

    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.

    August 8, 2006 at 6:45 am
  • peter Reply

    Hi you’re right. My typo. It should be “Where not commited” of course.

    August 8, 2006 at 6:51 am
  • Seth Reply

    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?

    July 24, 2007 at 1:17 pm
  • peter Reply

    It is combined size which is limited

    July 24, 2007 at 2:37 pm
  • How to calculate a good InnoDB log file size | MySQL Performance Blog Reply

    […] write a post a while ago about choosing a good InnoDB log file size.  Not to pick on Peter, but the post actually kind of talks about a lot of things and then doesn’t […]

    November 21, 2008 at 7:52 am
  • Is DRBD the right choice for me? | MySQL Performance Blog Reply

    […] InnoDB runs through crash recovery (1 minute – several hours). Peter wrote about this here. […]

    July 7, 2009 at 11:00 am
  • Increase innodb_log_file_size: the proper way « Things to Remember Reply

    […] Choosing proper innodb_log_file_size […]

    August 12, 2009 at 12:08 pm
  • john Reply

    what value you propose for a vps with lamp and 512 mb ram? or this doesn’t have to do with ram at all?

    November 1, 2011 at 6:11 pm
  • dave Reply

    @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.

    May 15, 2012 at 5:05 am

Leave a Reply