There, you state “Log file sizes can’t be left at the default 5MB for any real workload, but they often don’t need to be as big as you might think, either.”

Has that changed with 5.5 dramatically?

While applying your above code, we are getting something like: 0.00543022 (the number is always 0.00n every time we ran the above code). When examining the formula, I see that you are subtracting the “Log Sequence Number” from the “Last Checkpoint”, and getting the difference in megabytes.

My question is, why are you using the “Log Sequence Number” in your formula and not the “Pages Flushed Up to”, which seems to be the async point as it is taking place before “Log Sequence Number”? Also, why is the “Log Sequence Number” always identical to “Log Flushed Up To” (both seem to be the sync point).

Finally, what is an ideal value returned by the above formula? What is too close and what is too far? Is a few megs of difference comfortable?

So that’s what I tried to explain with this post.
To understand if you need to increase innodb_log_file_size – you need to see if Checkpoint Age is close to Async point.
You can check it with “mysql -e ‘show engine innodb status\G’ | awk ‘ BEGIN { } /Log sequence number/ {st=$4 } /Last checkpoint at/ { ed=$4; print (st-ed)/1024/1024 } ‘”
or better way to look at graphs like in this post – as it gives your historic information.
If Checkpoint Age is getting close to Async point – I would consider to increase innodb_log_file_size, say by 20%.
Then repeat again, but make sure you are happy with your crash recovery time.

We mostly use our MySQL database servers for shared hosting of many, small, databases (for customer websites that require a MySQL database, like WordPress, Joomla and Drupal. They often don’t exceed a few hundred MB’s in size, some are “large”, between 900 MB and 2 GB.

Doing the log file size calculation, an innodb_log_file_size = 256M would be more than enough to store an hour worth of data. Is there any reason – performance wise – to set this higher, to 1, 2 or even more GB’s?

I think 60 minutes recommendation is arbitrary and simply just wrong.
To show the example, this chart show log writes per hour.

You can see in some hours there is over 90GB writes into logs – but that absolutely does not mean we need to have 90GB of log space.

And if we look into Uncheckpointed bytes

There is barely 9Gb there.
So the most likely current total 30GB log size is overkill and just 12-15GB would be enough.

Typically you will see performance improvements from increasing log files to be based on the law of diminishing returns with first increases giving you good performance boost and when later much less so.

The actual gains depend a lot of workload – updates hitting same database pages over and over tends to allow to eliminate writes with large log files helping a lot as well as IO performance – slow storage will find additional IO caused by smaller logs more taxing.

Also for most workloads you will have some sort of natural maximum log space which will be used – based on innodb_io_capacity and other factors – increasing logs beyond this value will not produce any gains.

I think the good way to set the optimal log files is to look at the Checkpoint Flushing IO as Vadim shows – while increasing log files allows you to substantially reduce such IO it is good to do so… keeping crash recovery times in mind.

Vadim, given current InnoDB optimizations, is this still a valid point in calculating a good InnoDB logfile size?

Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group

Any comment on this? What was the value of such in your benchmarks? (Of course, it is hard to get that from the benchmark because it is either steady state or off.)

In my experience, I have rarely encountered a setting for log_file_size of 2GB or more. Only one case (out of several dozen) did that formula recommend more than 2GB.

I think one good thing to point out is innodb_log_file_size matters for heavy write workloads. In a lot of writes are going on chances are your might not be pushing your Innodb Max Checkpoint age and log size might not be limiting for your workload. PMM has a graph for that 🙂