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

Summary

  1. Need for Redo Log
  2. InnoDB Redo log Before 8.0.30
  3. InnoDB Redo log After 8.0.30
  4. What is new in the InnoDB redo log?
    1. Where are the new redo logs located?
    2. How to resize the redo log now?
    3. How to Monitor InnoDB redo log resize?
      1. Status variables
      2. Error Log table
  5. Monitoring and Instrumentation?
  6. Comparison Table?
  7. Things to Consider
  8. Conclusion

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

Before 8.0.30In 8.0.30
Redo log files 2 redo files by default32 redo log files by default
Redo ResizeStatic needs a restartDynamic ( no restart )
Redo log writesCircular writes ( file0->file1->file0)Logs writes are in a Sequential Queue
Redo log namingStatic naming ( ib_logfile0 and iblogfile1)Dynamic naming and incremented (#ib_redoNNNN)

Things to Consider

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

Conclusion

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.

One thought on “Online InnoDB Redo log Resize MySQL 8.0.30

  1. Thanks for blogging about MySQL! 🙂

    One small correction, though:

    > The Redo logs replay the committed transaction during the crash recovery and roll back the uncommitted transactions in the log.

    The rollback of uncommitted transactions is done by using an Undo Log which is a separate concept.

    The Redo Log is about physical changes to bytes of your tablespace files – it talks very low level stuff like “remember to put the four bytes [0,42,222,0] at offset 496346 of mysqld.ibd file if I haven’t done that already”. This is because we don’t update the tablespace files on disc immediately, but rather are as lazy about it as possible to save on I/O.

    The Undo Log talks at a higher level of abstraction and explains what transactions did, like “Hi, I am transaction with id 17 and I’ve just modified a record in table Artists by changing row with id 133, and the previous value of the column Likes was 17 before I touched it, bye”.

    So, to recover you need both: Redo Log to bring the tablespace files up to the state they (conceptually – as a sum of what was visible disc, and what was in memory in Buffer Pull still not written back to disc because we are lazy) were at the moment of the crash (by Redo-ing the changes to these files), and Undo Logs to later rollback transactions which weren’t committed (by Undo-ing their changes to rows).

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s