Bug #115348 Expansion of the undo tablespace

• Submitted: 16 Jun 2024 15:56
• Modified: 9 Jan 11:24
• Reporter: Xiaocong Ding (OCA)
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 8.0.40
• OS: Any
• CPU Architecture: Any
• Tags: Contribution, Tablespace, undo

[16 Jun 2024 15:56] Xiaocong Ding

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.

[17 Jun 2024 2:08] Xiaocong Ding

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.

[17 Jun 2024 7:32] MySQL Verification Team

Hello Shay Fabio,

Thank you for the report and contribution.

regards,
Umesh

[9 Jan 11:24] Xiaocong Ding

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