Bug #118538 Undo Logs Not Shrinking Despite No Active Long Transactions or Open Snapshots

• Submitted: 27 Jun 4:56
• Modified: 1 Aug 12:46
• Reporter: Shahbaz Gavasekar
• Status: No Feedback
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.30
• OS: Linux (Amazon Linux 2)
• Assigned to: MySQL Verification Team
• CPU Architecture: x86

[27 Jun 4:56] Shahbaz Gavasekar

Description:
We are observing an issue where undo log size is not reducing and history list length continues to increase, even though:

There are no long-running transactions.

Purge activity from past transactions has already completed.

No SELECT queries are holding references to the old row versions.

This behavior is seen on MySQL 8.0.30 Community Edition.

Logs :

------------
TRANSACTIONS
------------
Trx id counter 288141089314
Purge done for trx's n:o < 285560447141 undo n:o < 629964356 state: running
History list length 97394422
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421946628912992, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628912184, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628873400, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628908144, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628858856, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628845120, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628813608, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628904912, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628904104, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628841080, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628878248, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628856432, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628896024, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628857240, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628851584, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628897640, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628891176, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628870976, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628883904, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628882288, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628879864, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628852392, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628837848, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628836232, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628850776, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628815224, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628899256, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628883096, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628860472, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628888752, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628827344, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628891984, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628870168, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628893600, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628875824, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628844312, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628828152, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628833000, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628875016, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421946628839464, not started
0 lock struct(s), heap size 1128, 0 row lock(s)

Variables :

+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory    | ./         |
| innodb_undo_log_encrypt  | OFF        |
| innodb_undo_log_truncate | ON         |
| innodb_undo_tablespaces  | 2          |
+--------------------------+------------+
5 rows in set (0.00 sec)

How to repeat:
This issue cannot be consistently reproduced in a controlled environment. However, it was observed on a MySQL 8.0 instance where:

A large purge activity had been completed.

There were no long-running transactions or active SELECT queries referencing older row versions.

The history list length continued increasing, and undo logs were not truncated.

[27 Jun 5:04] Shahbaz Gavasekar

Logs :

1.3T	undo_001
4.1T	undo_002

[1 Jul 11:22] MySQL Verification Team

Hi,

I cannot reproduce this behavior with latest 8.0. This is rather old release you are using with many bugfixes. Can you try to reproduce this using latest 8.0?

Thank you for using MySQL Server.

[1 Jul 11:33] Shahbaz Gavasekar

The behavior was observed on MySQL 8.0.30. Could you please confirm if there are any known issues or bugs specific to this version related to undo log growth or truncation?

[2 Aug 1:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".