Managing and Truncating InnoDB Undo Logs in MySQL 5.7 and 8.0

Background A user of MySQL 5.7 experienced a rapid increase in undo log size because a slow SQL statement ran for thousands of seconds, preventing undo segments from being cleaned.

Even after killing the slow query, the undo size exceeded innodb_max_undo_log_size and the undo files did not shrink immediately.

Test verification The following variables were shown with innodb_undo_log_truncate enabled:

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)

Simulated undo growth:

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

The InnoDB undo‑clean‑up strategy is:

When innodb_undo_log_truncate is enabled, undo tablespaces larger than innodb_max_undo_log_size are marked for truncation.

The marked undo segments become inactive and cannot be allocated to new transactions.

The purge thread releases those inactive segments.

After release, the tablespace is truncated to its initial size (10 M).

The parameter innodb_purge_rseg_truncate_frequency (default 128) controls how often the purge thread releases segments. Reducing it speeds up undo shrinkage.

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)

Setting the frequency to a lower value, e.g., 16, allows the purge thread to free segments more frequently, resulting in undo files shrinking to 10 M.

// reduce the value
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)

# after purge runs
# du -sh ./undo*
10M     ./undo001
10M     ./undo002
15M     ./undo003

MySQL 8.0 Manual Truncation MySQL 8.0 adds SQL statements to manage undo tablespaces.

At least three active undo tablespaces are required so that two remain active for automated truncation.

Creating a manual undo tablespace (file 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)
-- verify three active undo tablespaces
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)

To truncate manually, set the tablespace to INACTIVE first:

# simulate undo growth
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)

After being marked INACTIVE, the purge thread accelerates, the undo tablespace is truncated to a small size and its state 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 files after shrink
du -sh ./undo*
81M     ./undo_001
2.1M    ./undo_002
26M     ./undo_003.ibu

An empty undo tablespace can be re‑activated:

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)

MySQL 8.0 also allows dropping an undo tablespace, but only when its state is empty :

mysql> ALTER UNDO TABLESPACE undo_003 SET INACTIVE;
Query OK, 0 rows affected (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)