How to Change MySQL REDO Log File Size Online in MySQL 8.0.30 and Later

Background

MySQL 8.0.30 adds a new feature that allows online adjustment of the REDO log file size, greatly reducing DBA workload.

Before 8.0.30 – How to change REDO log file size?

For versions prior to 8.0.30, changing the REDO log size required a series of manual steps. Assuming the target size is 2 GB, the process is as follows:

1. Identify the two traditional parameters that determine the total log size

The total size equals innodb_log_files_in_group (default 2) multiplied by innodb_log_file_size (default 48 M). The current total is 96 M.

root@ytt-large:~/sandboxes/msb_5_7_34/data# ls -sihl ib_logfile*
3277012  48M -rw-r----- 1 root root  48M Jul 29 16:18 ib_logfile0
3277013  48M -rw-r----- 1 root root  48M Jul 29 16:18 ib_logfile1

2. Set fast shutdown to 0 to flush all dirty pages

<mysql:(none):5.7.34-log> set global innodb_fast_shutdown=0;
Query OK, 0 rows affected (0.00 sec)

3. Stop the MySQL instance

After step 2, shut down the server.

4. Delete the old log files

root@ytt-large:~/sandboxes/msb_5_7_34/data# rm -rf ib_logfile*

5. Modify innodb_log_file_size in my.cnf

[mysqld]
innodb_log_file_size=1G

6. Restart MySQL and verify

root@ytt-large:~/sandboxes/msb_5_7_34/data# ls -sihl ib_logfile*
3277898 1.1G -rw-r----- 1 root root 1.0G Jul 29 16:31 ib_logfile0
3277923 1.1G -rw-r----- 1 root root 1.0G Jul 29 16:31 ib_logfile1

After 8.0.30 – How to change REDO log file size?

MySQL 8.0.30 introduces the single parameter innodb_redo_log_capacity that replaces the two older parameters. Adjusting the size is as simple as setting the desired value, e.g., 2 GB.

<mysql:(none):8.0.30> select @@innodb_redo_log_capacity;
+----------------------------+
| @@innodb_redo_log_capacity |
+----------------------------+
|                  104857600 |
+----------------------------+
1 row in set (0.00 sec)

<mysql:(none):8.0.30> set persist innodb_redo_log_capacity=2*1024*1024*1024;
Query OK, 0 rows affected (0.20 sec)

A new status variable innodb_redo_log_capacity_resized allows monitoring the current REDO log size:

<mysql:(none):8.0.30> show status like 'innodb_redo_log_capacity_resized';
+----------------------------------+------------+
| Variable_name                    | Value      |
+----------------------------------+------------+
| Innodb_redo_log_capacity_resized | 2147483648 |
+----------------------------------+------------+
1 row in set (0.00 sec)

The physical log files are now stored as #ib_redoN under the #innodb_redo subdirectory, with 32 files distributed evenly according to the capacity setting.

root@ytt-large:/var/lib/mysql/#innodb_redo# ls | wc -l
32

Two types of files exist: those without the _tmp suffix are actively used, while files with _tmp are spare and become active when the current ones fill up.

root@ytt-large:/var/lib/mysql/#innodb_redo# ls | grep -v '_tmp' | wc -l
15
root@ytt-large:/var/lib/mysql/#innodb_redo# ls | grep '_tmp' | wc -l
17

The performance_schema now includes the table innodb_redo_log_files , which reports LSN ranges, actual written size, and fullness for each REDO log file.

<mysql:performance_schema:8.0.30> select * from innodb_redo_log_files;
+---------+---------------------------+------------+------------+---------------+---------+----------------+
| FILE_ID | FILE_NAME                 | START_LSN  | END_LSN    | SIZE_IN_BYTES | IS_FULL | CONSUMER_LEVEL |
+---------+---------------------------+------------+------------+---------------+---------+----------------+
|       7 | ./#innodb_redo/#ib_redo7  |  552208896 |  619315712 |      67108864 |       1 |              0 |
... (more rows) ...
+---------+---------------------------+------------+------------+---------------+---------+----------------+

Conclusion

MySQL 8.0 introduces many features that simplify development and operations; upgrading to the latest version is strongly recommended to benefit from online REDO log resizing and other improvements.