MySQL 8.0.30+: redo_log_capacity vs log_file_size?

Question

Cheers. We're trying to track down the cause of a significant increase in IO load after switching from MySQL 5.7 to 8.0.30, and one issue we've noticed is related to this part of documentation:

The innodb_redo_log_capacity variable supersedes the innodb_log_files_in_group and innodb_log_file_size variables, which are deprecated. When the innodb_redo_log_capacity setting is defined, the innodb_log_files_in_group and innodb_log_file_size settings are ignored; otherwise, these settings are used to compute the innodb_redo_log_capacity setting (innodb_log_files_in_group * innodb_log_file_size = innodb_redo_log_capacity). If none of those variables are set, redo log capacity is set to the innodb_redo_log_capacity default value, which is 104857600 bytes (100MB). The maximum redo log capacity is 128GB.

We used to set innodb_log_file_size to 4G or such, and leave the innodb_log_files_in_group undefined in the ini file, relying on the built-in default of 2. With MySQL 5.7, this has produced two 4GiB-sized log files for a total capacity of 8 GiB.

Under MySQL 8, the calculation from legacy variables, as described by the documentation, just doesn't work - even when I explicitly set the files-in-group var, the redo-log-capacity is always set to 100 MiB (as shown by a SHOW VARIABLES ... query). This matches the size of the redo log folder (observed in OS) exactly. (And no, I don't have innodb_redo_log_capacity set elsewhere in the ini file.) Only after I set the new variable, innodb_redo_log_cpaacity in the ini file is the new value set in the running server's global variables.

Am I missing something, or is this broken?

FWIW, I'm on windows server 2019, but the ini file is unix-formatted. The values defined in the ini file for the legacy variables are correctly read by the server (tested with non-default values).

Answer 1

This is intentional. The old variables are ignored as of MySQL 8.0.30.

https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_log_file_size

innodb_log_file_size and innodb_log_files_in_group are deprecated in MySQL 8.0.30. These variables are superseded by innodb_redo_log_capacity.

The blog that describes the new InnoDB log architecture in more detail says:

https://blogs.oracle.com/post/dynamic-innodb-redo-log-in-mysql-80

This means that now, you don’t need to restart MySQL if you want to increase or decrease the size of the InnoDB Redo Logs files. In fact, we don’t talk anymore about file size but about capacity ! The DBA doesn’t need to specify any file size and/or amount of files for Redo Logs anymore as it was the case previously.

(emphasis mine)