Dynamic InnoDB Redo log Resize MySQL 8.0.30

Sep 2, 2022

Mins to Read

All

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 Online resize of the InnoDB Redo log.

Need for Redo Log

The redo log plays a vital role in relational databases. It primarily ensures the durability of the database, it is D(durability) in the ACID property. The Redo logs replay the committed transaction during the crash recovery.

InnoDB Redo log Before 8.0.30

Before the MySQL 8.0.30 version, the redo logs were the files physically located on the disk named ib_logfile0 and ib_logfile1. The number and size of redo log files were controlled by the innodb_log_files_in_group and innodb_log_file_size variables respectively. Have a better understanding from the below diagram.

InnoDB Redo log After 8.0.30

Having the right redo log file size is fundamental for MySQL to run smoothly without struggling. But to manage and configure the size, we need to do a MySQL restart which is not always a just-go thing in production database servers.

Here it is, InnoDB in the latest MySQL version 8.0.30 supporting the change of the innodb_redo_log_capacity system variable at the run time so we can increase or decrease the disk space occupied by the redo logs dynamically. And it doesn’t require a MySQL restart.
There are also some exciting variables and adjustments to get to know. This variable supersedes the innodb_log_files_in_group and innodb_log_file_size variables. Means when a innodb_redo_log_capacity setting is defined, the innodb_log_files_in_group and innodb_log_file_size settings are ignored; To compute the innodb_redo_log_capacity setting using this formula:

 
innodb_redo_log_capacity = innodb_log_files_in_group *    innodb_log_file_size

If none of those variables are set, the redo log capacity is set to 104857600 bytes (100MB) a default value. And the minimum value we can set to this “innodb_redo_log_capacity” variable is
8 MB where the maximum value is 128GB.

What is new in the InnoDB redo log?

In 8.0.30 InnoDB tries to maintain 32 redo log files in total, with each file equal to 1/32 * innodb_redo_log_capacity the default is 100MB. Now we can observe each redo log of size 3.2MB. ( i.e; 3.2*32= 100 MB)

Where are the new redo logs located?

The new redo logs are located in a sub-directory within the MySQL data directory by default. If the data directory is in /data/mysql then the redo logs will be in the below location

 
/data/mysql/#innodb_redo

Now the redo log acts as a Queue, in the prior MySQL version it was a circular file. The older redo log files are purged now.

As conveyed above the redo logs are located in folder #innodb_redo. 32 redo log files are at least maintained within this folder.

 
ls -ltrh
total 100M
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2484_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2485_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2486_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2487_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2488_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2489_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2490_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2491_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2492_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2493_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2494_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2495_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2496_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2497_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2498_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2499_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2500_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2501_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2502_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2503_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2504_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2505_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2506_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2507_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2508_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2509_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2510_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2511_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2512_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2513_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2514_tmp
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2483

Above the redo log after a restart. All the suffixes _tmp i.e; 31 in number are the spare redo logs still waiting to be used and there is an active file used by the InnoDB. Here 3.2MB (approx ) of 32 redo log files with 100MB total size is inside #innodb_redo.

The Redo logs file is named #ib_redoNNNN for active file and #ib_redoNNNN_tmp for spare redo logs. The redo number (NNNN) is incremented.

How to resize the redo log now?

Once the optimal redo log size is calculated for your database workload. The redo log can be resized easily, i have modified the redo log size to 2GB from a default of 100 MB.

 
mysql> set persist innodb_redo_log_capacity=2*1024*1024*1024;
Query OK, 0 rows affected (0.01 sec)

 
ls -ltrh
total 2.0G
-rw-r-----. 1 mysql mysql 3.2M Aug 25 00:14 #ib_redo2483
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2484_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2485_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2486_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2487_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2488_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2489_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2490_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2491_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2492_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2493_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2494_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2495_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2496_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2497_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2498_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2499_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2500_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2501_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2502_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2503_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2504_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2505_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2506_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2507_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2508_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2509_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2510_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2511_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2512_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2513_tmp
-rw-r-----. 1 mysql mysql  64M Aug 25 00:16 #ib_redo2514_tmp

Now all the redo files beyond the active one are resized to 64MB ( 2GB overall) per file.

How to Monitor InnoDB redo log resize?

Status variables

MySQL 8.0.30 has introduced a new status variable name Innodb_redo_log_resize_status. It can be monitored to check the status of InnoDD redo resize ( upsize or downsize ).

 
show global status like 'Innodb_redo_log_resize_status';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_redo_log_resize_status | OK    |
+-------------------------------+-------+
1 row in set (0.00 sec)

Error Log table

MySQL Error log table was introduced in MySQL 8.0.22. This can help in tracking the InnoDB redo resize easier.

 
select (LOGGED),ERROR_CODE,SUBSYSTEM,DATA from performance_schema.error_log where subsystem='INNODB' and DATA like '%redo%' order by LOGGED desc limit 3;
+----------------------------+------------+-----------+----------------------------------------------------------------------------------------------------------------+
| LOGGED                     | ERROR_CODE | SUBSYSTEM | DATA                                                                                                           |
+----------------------------+------------+-----------+----------------------------------------------------------------------------------------------------------------+                                                                         |
| 2022-08-25 00:55:53.601520 | MY-013884  | InnoDB    | User has set innodb_redo_log_capacity to 2048M.                                                                |
| 2022-08-25 00:55:53.601601 | MY-013885  | InnoDB    | Redo log has been requested to resize from 100M to 2048M.                                                      |
| 2022-08-25 00:55:53.601628 | MY-013887  | InnoDB    | Redo log has been resized to 2048M.                                                                            |                                                       |
+----------------------------+------------+-----------+----------------------------------------------------------------------------------------------------------------+

Monitoring and Instrumentation

The redo log slot and their status can be monitored from a new table under the performance schema named innodb_redo_log_files.

 
select FILE_ID as "Slot_number", (END_LSN-START_LSN) as "Total LSN" , sys.format_bytes(SIZE_IN_BYTES) as SLOT Size, if(IS_FULL="0","Active","In Active") as "Slot Status" from performance_schema.innodb_redo_log_files;
+-------------+----------+-----------+-------------+
| Slot_number | Total LSN | SLOT Size | Slot Status|
+-------------+----------+-----------+-------------+
| 2494 | 67106816 | 64.00 MiB | In Active          |
| 2495 | 67106816 | 64.00 MiB | In Active          |
| 2496 | 67106816 | 64.00 MiB | In Active          |
| 2497 | 67106816 | 64.00 MiB | In Active          |
| 2498 | 67106816 | 64.00 MiB | In Active          |
| 2499 | 67106816 | 64.00 MiB | In Active          |
| 2500 | 67106816 | 64.00 MiB | In Active          |
| 2501 | 67106816 | 64.00 MiB | In Active          |
| 2502 | 67106816 | 64.00 MiB | In Active          |
| 2503 | 67106816 | 64.00 MiB | In Active          |
| 2504 | 67106816 | 64.00 MiB | In Active          |
| 2505 | 67106816 | 64.00 MiB | In Active          |
| 2506 | 67106816 | 64.00 MiB | In Active          |
| 2507 | 67106816 | 64.00 MiB | Active             |
+-------------+----------+-----------+-------------+
14 rows in set (0.00 sec)

Comparison Table

Let us compare the redo file before 8.0.30 and from 8.0.30

Things to Consider

Ensure you persist the redo log capacity in the config file or use “SET PERSIST”.
Check the compatibility of your physical backup tools.
Stop relying on innodb_log_file_size and innodb_log_files_in_group

This feature can save downtime in redo log resize, and ease performance tuning on dynamic workloads. As more and more databases are moving towards K8’s and DBaaS, this feature is a boon. As MySQL has been released Percona server will get this feature. MariaDB has a similar implementation of the redo log file in MariaDB 10.5 ( single redo file ) but the redo log resize is dynamic in enterprise MariaDB.

Optimize performance, enhance reliability, and ensure data security with Mydbops' MySQL InnoDB Cluster Consulting and Support Services. Our team can help you fine-tune temporary tablespace configuration, troubleshoot performance issues, and implement best practices. Contact us today to schedule a free consultation.

No items found.