Toss a coin to your DBA: Estimate MySQL Redo Log Size

Having a right REDO log size is fundamental for MySQL to run smoothly without struggling. An undersized REDO can even lead to errors in operations!

For example, this week, a friend of mine reported this error:

This is because MySQL was using the default value for the innodb_log_file_size, which is 48 Mb. So, for the particular quest to discover a good redo log size, there is a formula that can be used in the majority of the cases. Let’s take a look at the following commands:

The log sequence number is the total number of bytes written to the redo log. Using the sleep command, we can calculate the delta for that period. Using math, we can reach an estimated value:

I usually round up to the higher value so that the final number will be 56 Mb. As a rough rule of thumb, you can make the log big enough that it can hold at most an hour or so of logs. So this is the final value that needs to be added to my.cnf:

That’s it! See you next!

Leave a Reply