Interpretation of new features | MySQL 8.0 online adjustment REDO

Author: Yang Taotao

Senior database expert, specializing in MySQL research for more than ten years. Good at MySQL, PostgreSQL, MongoDB and other open source databases related to backup and recovery, SQL tuning, monitoring operation and maintenance, high-availability architecture design, etc. Currently working in Aikesheng, providing MySQL-related technical support and MySQL-related course training for major operators and banking and financial enterprises.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

The latest release of MySQL version 8.0.30 brings a new feature point related to REDO log files: Online resizing of REDO log files! It greatly simplifies the workload of operation and maintenance (experienced students understand it)!

Usually, after a MySQL instance is deployed, the size of the REDO log file generally does not keep the default value. DBA students will adjust it to an appropriate value according to the amount and frequency of data writing. The size of the REDO log file that matches the business can make the database get the best performance (how to make the size of the REDO log file match the existing business is beyond the scope of this article).

The following is the version before and after MySQL 8.0.30, compare the change process of the REDO log file, and experience the ease of use of the latest version.

How to change REDO log file size prior to 8.0.30?

For these versions, the steps to modify the REDO log file size are cumbersome. Suppose you need to change its size to 2G, the steps are as follows:

1. The change of the REDO log file involves two traditional parameters: its final size is the multiplication of the values of these two parameters.

(1) innodb_log_files_in_group: The number of files on the REDO log disk, the default is 2.

(2) innodb_log_file_size: The size of a single file on the REDO log disk, the default is 48M.

(3) The current log size is a single 48M, two groups, that is, a total of 96M.

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

2. "Shutdown" fast shutdown instance parameter: innodb_fast_shutdown = 0 to ensure that InnoDB flushes all dirty pages to disk (please refer to the official manual for other values of this parameter):

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

3. After step 2 is executed, stop the MySQL instance.

4. Delete the old log files in the data directory:

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

5. Modify the parameter innodb_log_file_size in the configuration file my.cnf. Since there are two groups, set this parameter to 1G.

 [mysqld]
innodb_log_file_size=1G

6. Start the MySQL instance (if no error is reported, the change is successful).

7. View the new log file size:

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

How do I change the REDO log file size after 8.0.30?

After the latest version of MySQL 8.0.30 is released, the new parameter innodb_redo_log_capacity is used to replace the previous two parameters (currently setting these two parameters is still valid). Adjusting the size with the new parameter is very simple, just set it to the value you want to adjust. For example, adjust its size to 2G:

Before adjustment, the default is 100M:

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

Resize it to 2G:

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

The corresponding state variable innodb_redo_log_capacity_resized is added to facilitate monitoring the current REDO log file size on the MySQL side:

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

At the same time, the storage format of disk files is no longer a file like ib_logfileN, but replaced by a new file format like #ib_redoN. These new files are stored by default in the subdirectory '#innodb_redo' under the data directory.

1. There are a total of 32 such files, which are evenly distributed according to the parameter innodb_redo_log_capacity.

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

2. There are two types of files: one without the _tmp suffix represents the log file being used; the one with the _tmp suffix represents the redundant log file, and then use it after the file being used is full. As follows: 15 log files are in use and 17 are unused.

 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

At the same time, a new table innodb_redo_log_files is added to the performance_schema library: to obtain the LSN interval of the currently used REDO log file, the actual write size, and whether it is full or not. For example, the current statistics of 15 REDO log files are as follows: at a glance!

 <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 |
...
|      21 | ./#innodb_redo/#ib_redo21 | 1491704320 | 1558811136 |      67108864 |       0 |              0 |
+---------+---------------------------+------------+------------+---------------+---------+----------------+
15 rows in set (0.00 sec)

Summarize:

The new version of MySQL 8.0 brings more and more function points to simplify development and operation and maintenance work. Please upgrade as soon as possible if possible.