Technology sharing | What to do if undo is too big

Author: Wang Yuchen

Akerson database engineer, responsible for MySQL daily maintenance and DMP product support.

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.

---

problem background

Some users encountered a surge in undo when using the MySQL 5.7 database. After investigation, there was a slow SQL that was executed for tens of thousands of seconds and still did not end. As a result, the undo generated by subsequent transactions could not be cleaned up, and more and more

Online truncate undo log is enabled, after killing slow SQL, the undo size exceeds the size set by innodb_max_undo_log_size, but the undo file does not shrink immediately

Test verification

The test parameters are as follows, enable innodb_undo_log_truncate

 mysql> show variables like '%undo%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_max_undo_log_size | 104857600 |
| innodb_undo_directory    | ./        |
| innodb_undo_log_truncate | ON        |
| innodb_undo_logs         | 128       |
| innodb_undo_tablespaces  | 3         |
+--------------------------+-----------+
5 rows in set (0.00 sec)

Simulate undo growth, exceeding the size set by innodb_max_undo_log_size

 # du -sh ./undo*
152M    ./undo001
296M    ./undo002
15M     ./undo003

Check out the official documentation on the undo cleanup strategy, which is briefly summarized as follows:

1. After enabling innodb_undo_log_truncate, undo tablespaces that exceed the size set by innodb_max_undo_log_size are marked as truncated

2. The rollback segment of the marked undo tablespace is set to inactive and cannot be allocated to new transactions

3. The purge thread releases unnecessary rollback segments

4. After the rollback segment is released, the undo tablespace is truncated to the initial size of 10M

It can be seen that before shrinking the undo size, the purge thread needs to release the rollback segment first. This involves another parameter innodb_purge_rseg_truncate_frequency, the default value is 128, which means that the purge thread releases the rollback segment once every 128 calls.

In the context of this issue, this parameter is set to a default value

 mysql> show variables like 'innodb_purge_rseg_truncate_frequency';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_purge_rseg_truncate_frequency | 128   |
+--------------------------------------+-------+
1 row in set (0.01 sec)

So in order to shrink the undo file as soon as possible, we can reduce the innodb_purge_rseg_truncate_frequency value to increase the frequency of the purge thread releasing the rollback segment

 //调小该值
mysql> show variables like 'innodb_purge_rseg_truncate_frequency';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_purge_rseg_truncate_frequency | 16    |
+--------------------------------------+-------+
1 row in set (0.01 sec)

//达到purge线程调用次数，释放回滚段，undo表空间被截断
# du -sh ./undo*
10M     ./undo001
10M     ./undo002
15M     ./undo003

MySQL8.0 adds Manual Truncation

MySQL8.0 adds support for using SQL statements to manage undo tablespaces

1. At least three active undo tablespaces are required, because it is necessary to ensure that there are two active undo tablespaces to support Automated Truncation

Manually create an undo tablespace, which must end with .ibu

 mysql> create undo tablespace undo_003 add datafile '/data/mysql/data/3307/undo_003.ibu';
Query OK, 0 rows affected (0.27 sec)
//三个处于 active 状态的 undo 表空间
mysql> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE '%undo%';
+-----------------+--------+
| NAME            | STATE  |
+-----------------+--------+
| innodb_undo_001 | active |
| innodb_undo_002 | active |
| undo_003        | active |
+-----------------+--------+
3 rows in set (0.00 sec)

2. To manually truncate the undo tablespace, you need to set the undo tablespace to inactive first

 //模拟 undo 增长
# du -sh ./undo*
81M     ./undo_001
157M    ./undo_002
26M     ./undo_003.ibu

mysql> ALTER UNDO TABLESPACE innodb_undo_002 SET INACTIVE;
Query OK, 0 rows affected (0.01 sec)

3. After manually setting inactive, the undo tablespace is marked as truncated, the purge thread will increase the return frequency, quickly clear and finally truncate the undo tablespace, and the status becomes empty

 mysql> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE '%undo%';
+-----------------+--------+
| NAME            | STATE  |
+-----------------+--------+
| innodb_undo_001 | active |
| innodb_undo_002 | empty  |
| undo_003        | active |
+-----------------+--------+
//undo 文件收缩
# du -sh ./undo*
81M     ./undo_001
2.1M    ./undo_002
26M     ./undo_003.ibu

4. The undo tablespace in the empty state can be reactivated for use

 mysql> ALTER UNDO TABLESPACE innodb_undo_002 SET ACTIVE;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE '%undo%';
+-----------------+--------+
| NAME            | STATE  |
+-----------------+--------+
| innodb_undo_001 | active |
| innodb_undo_002 | active |
| undo_003        | active |
+-----------------+--------+
3 rows in set (0.01 sec)

5. MySQL 8.0 supports deleting tablespaces, but only if the tablespace is in the empty state

 mysql> ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE '%undo%';
+-----------------+--------+
| NAME            | STATE  |
+-----------------+--------+
| innodb_undo_001 | active |
| innodb_undo_002 | active |
| undo_003        | empty  |
+-----------------+--------+
3 rows in set (0.01 sec)

mysql> DROP UNDO TABLESPACE undo_003;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE LIKE 'UNDO LOG';
+-----------------+------------+
| TABLESPACE_NAME | FILE_NAME  |
+-----------------+------------+
| innodb_undo_001 | ./undo_001 |
| innodb_undo_002 | ./undo_002 |
+-----------------+------------+
2 rows in set (0.01 sec)