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:
|
1 |
[ERROR] InnoDB: The total blob data length (12299456) is greater than 10% of the total redo log size (100663296). Please increase total redo log size. |
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:
|
1 2 3 4 5 6 7 8 9 10 |
mysql> pager grep sequence PAGER set to 'grep sequence' mysql> show engine innodb status\G select sleep(60); show engine innodb status\G Log sequence number 84 3836410803 1 row in set (0.06 sec) 1 row in set (1 min 0.00 sec) Log sequence number 84 3838334638 1 row in set (0.05 sec) |
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:
|
1 2 3 4 5 6 7 |
> select (((3838334638 - 3836410803)/1024/1024)*60)/2 as Estimated_innodb_log_file_size; +--------------------------------+ | Estimated_innodb_log_file_size | +--------------------------------+ | 55.041360855088 | +--------------------------------+ 1 row in set (0.00 sec) |
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:
|
1 2 |
[mysqld] innodb_log_file_size=56M |
That’s it! See you next!