MySQL Bugs: #115348: Expansion of the undo tablespace

Bug #115348	Expansion of the undo tablespace
Submitted:	16 Jun 2024 15:56	Modified:	9 Jan 11:24
Reporter:	Xiaocong Ding (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0.40	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution, Tablespace, undo

Description:
When MySQL starts up, the initial size of the undo tablespace should be set to 16 MB instead of adopting the actual physical size of the undo tablespace. A truncate operation typically resets the undo tablespace to 16 MB. When the innodb_fast_shutdown=1, the shutdown procedure skips the undo tablespace's truncate step. When MySQL restarts, it adopts the undo tablespace's actual physical size as the initial_size, instead of the default 16 MB. However, the initial_size of the undo tablespace only determines whether the truncation operation will be invoked. This will cause the initial_size of the undo tablespace to inflate, and the truncate operation is skipped when initial_size is too large, which leads to unreasonable expansion of the undo tablespace.

How to repeat:
1. Prepare a table, and sysbench script is as follows:

sysbench --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=**** --mysql-password=**** --threads=32 --tables=1 --mysql-db=sbtest --report-interval=1 --mysql-ignore-errors=all oltp_update_non_index prepare

2. Start a long transaction by executing SQL:
a. begin;
b. select * from sbtest.sbtest1;

3. Make undo tablespace grow by running script:

sysbench --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=**** --mysql-password=**** --threads=32 --tables=1 --mysql-db=sbtest --report-interval=1 --time=300 --mysql-ignore-errors=all oltp_update_non_index run

4. set global innodb_fast_shutdown=1; shutdown;

5. Restart mysqld.

6. Execute sql: "select file_name, initial_size from information_schema.files where tablespace_name like '%undo%';". Then, it will be found that the undo tablepsace's initial_size becomes very large and the undo tablespace won't be truncate.

Suggested fix:
When MySQL starts up, the initial size of the undo tablespace should be set to 16 MB instead of adopting the actual physical size of the undo tablespace.

Just use UNDO_INITIAL_SIZE_IN_PAGES instead of actual size when open an undo tablespace. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: Fix suggestion.rtf (text/rtf), 1.03 KiB.

Hello Shay Fabio,

Thank you for the report and contribution.

regards,
Umesh

Here is a code suggestion. In function trx_rseg_add_rollback_segments, the parameter use_current of fil_space_set_undo_size should be false.

No need to use space->size to set the m_undo_initial. Otherwise this may result in a large initial_size for the undo tablespace, especially after a crash or OOM restart, potentially causing a huge undo tablespace which won't be truncated. Therefore, use_current here should be set to false.

This is the code I suggested:

diff --git a/storage/innobase/trx/trx0rseg.cc b/storage/innobase/trx/trx0rseg.cc
index 0070a54cbec..d3d1b3d20ed 100644
--- a/storage/innobase/trx/trx0rseg.cc
+++ b/storage/innobase/trx/trx0rseg.cc
@@ -961,7 +961,7 @@ bool trx_rseg_add_rollback_segments(space_id_t space_id, ulong target_rsegs,
   /* Save the size of the undo tablespace now that all rsegs have been created.
   No need to do this for the system temporary tablespace. */
   if (type == UNDO) {
-    fil_space_set_undo_size(space_id, true);
+    fil_space_set_undo_size(space_id, false);
   }
   return (success);
 }

Is this implemented in mysql version 8.0.40. Even after the restart still i could see the same size for the undo logs. Any suggestion to fix it , iam having a issue where the history list length is in millions and undo log file size in higher gb's.