3

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).

1 Answer 1

3

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)

7
  • Does that mean that the setting is a "max"? But it initially allocates only the 100 MiB that the OP observed?
    – Rick James
    Feb 17, 2023 at 19:27
  • I haven't used it much, I've just upgraded my laptop to 8.0.32. I sum up the files in $datadir/#innodb_redo and they total 100MB. The files appear to be preallocated, like the old ib_logfiles were. There are 32 files, each 1/32nd of the total capacity. Feb 17, 2023 at 19:30
  • Hmmm... My 8.0.32 (Ubuntu) has 32 x 3MB = 100MB; most are ..._tmp.
    – Rick James
    Feb 17, 2023 at 20:37
  • I have perceived (without much proof) that a large value for files_in_group hurts performance. Maybe that is what is happening now?
    – Rick James
    Feb 17, 2023 at 20:40
  • Both in the old system and the new system, InnoDB is only writing to a single file at a time. The others are "spare". There is no difference in performance between one file, two files, or many files in the log group. The reason they now use 32 files by default is to support online resizing. You should read the blog I linked to. Feb 17, 2023 at 22:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.