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.
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.
Hi you’re right. My typo. It should be “Where not commited” of course.
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?
It is combined size which is limited
Am I reading this correctly, I need to just keep crashing the database and watching recovery times to size the log correctly?
what value you propose for a vps with lamp and 512 mb ram? or this doesn’t have to do with ram at all?
@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.