MySQL 8.0.30 changes to innodb log file size now using innodb_redo_log_capacity

NealC

NealC

Well-known member
I've been investigating a slower than desired site and made a few changes. This evening I was learning about setting innodb_file_size and the logs for MySQL 8.0.30 on Ubuntu Server 22.04 pointed me to the fact it's now deprecated and we should now use innodb_redo_log_capacity. I set this new property to 4G and my site is so much faster. If you're not aware of this change in MySQL server I suggest researching it. Here is an article that may be of interest in my google research learning about it.

mydbops.wordpress.com

Dynamic InnoDB Redo log Resize MySQL 8.0.30

Tired of downtimes and planning to resize your redo log files in MySQL ( InnoDB)? Here we can find a smile on our faces! The latest release of MySQL 8.0.30 ( 2022-07-26 ) has added the Onlin…
mydbops.wordpress.com mydbops.wordpress.com
 
I've been looking at the new innodb_redo_log_capacity setting and trying to find some guidance on how to optimise this.

The most useful information I've found so far is: https://blogs.oracle.com/mysql/post/dynamic-innodb-redo-log-in-mysql-80

They do note that you will see warning messages in the MySQL error log if your redo log capacity is too small, for example:

[Warning] [MY-013865] [InnoDB] Redo log writer is waiting for a new redo log file.
Consider increasing innodb_redo_log_capacity.
Click to expand...

They also suggest a query that could be used to calculate the optimal redo log capacity (to be run at peak traffic time):

Code: 
select VARIABLE_VALUE from performance_schema.global_status where VARIABLE_NAME='Innodb_redo_log_current_lsn' into @a;
select sleep(60) into @garb; 
select VARIABLE_VALUE from performance_schema.global_status where VARIABLE_NAME='Innodb_redo_log_current_lsn' into @b;
select format_bytes(abs(@a - @b)) per_min, format_bytes(abs(@a - @b)*60) per_hour;

... it takes 60 seconds to excecute and will calculate the log capacity used per minute and by extrapolation, per hour.

On my sites, the results suggest that the default of 100M is plenty large enough (noting that traffic is actually pretty quiet at the moment over the Christmas period).

I'd be curious to see what results other people see on their sites.
 
Hmmm I remember spending some time computing ideal log file sizes a while back, guess this is as good as any time to review it.

Running @Sim's snippet, I get the following:

On the current primary:
Code: 
+----------+------------+
| per_min  | per_hour   |
+----------+------------+
| 8.01 MiB | 480.42 MiB |
+----------+------------+

On a replica I get roughly the same.

And looking at the now legacy variables in my config:

Code: 
# InnoDB tx log file (https://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/)
# tl;dr experiment shows a minute of production write logs is approx 272MB
# divided by 2 (files in group) is ~135MB
# rounded to next power-of-two is 256MB
innodb_log_file_size=256M
innodb_log_files_in_group=2

So it seems that while a nice addition, it works out to a fairly similar value
 
The values automatically configured by innodb_dedicated_server seem pretty extreme really.

1704745996403.webp

So for a dedicated database server with 12GB of RAM, we'd see innodb_redo_log_capacity set to 7GB, regardless of whether it actually needs that much.

In the MySQL blog article I linked to above, they say:

An undersized Redo Log Capacity is problematic and lead to performance issues.
However, it’s not recommended to oversize the Redo Log either. Redo Log files consume disk space and increases the recovery time in case of a restart (innodb_fast_shutdown=1) or a sudden crash. And it also slows down shutdown when innodb_fast_shutdown=0.
Click to expand...
... but they don't mention any metrics for exactly what this means in real terms and how much of a problem it really is to have a redo log capacity that is far larger than you actually need.

One thing to note is that if MySQL detects that you have insufficient capacity in your redo log files, it will note this via a warning in the log files:
2023-09-17T00:02:09.388631Z 0 [Warning] [MY-013865] [InnoDB] Redo log writer is waiting for a new redo log file.
Consider increasing innodb_redo_log_capacity.
Click to expand...
... so when manually tuning your database server, it's worth keeping an eye on the log files to see if we've exceeded our configured capacity.
 
Sim said:
The values automatically configured by innodb_dedicated_server seem pretty extreme really.

So for a dedicated database server with 12GB of RAM, we'd see innodb_redo_log_capacity set to 7GB, regardless of whether it actually needs that much.
Click to expand...
7GB on a 12Gb system dedicated to mysql seems quite reasonable and not extreme at all. I've been using it for couple of years, and not had an issue as well as not having to regularly tune or change config settings. InnoDB buffer pool size is recommended at 80% of RAM, so that would have been 9.6GB. innodb_dedicated_server previously set buffer pool to 75%, thus 9.0Gb.

innodb_dedicated_server gives a fairly well-tuned config at startup. Sure, monitoring and tuning can probably do better, but I decided the continued effort wasn't worth it and innodb_dedicated_server provides a stable and efficient DB for XF.
 
Last edited:
Mouth said:
7GB on a 12Gb system dedicated to mysql seems quite reasonable and not extreme at all
Click to expand...

7GB of diskspace is nothing when it comes to system resources - however, my concern is what impact allocating that much redo log capacity would have on shutdown / restart - although presumably it isn't that big of a deal, or people would be commenting about it.

I guess modern NVMe drives would make it far less problematic than it might have been if you were running on spinning drives.
 
You must log in or register to reply here.
Back
Top Bottom