The newest release of MySQL 8.0.14 includes the ability to control the number and location of UNDO tablespaces using SQL. Users can now manage UNDO tablespaces using SQL from the MySQL client.

Background

More undo tablespaces help busy systems handle a larger number of read-write concurrent transactions by having more rollback segments available to provide undo logs to those transactions.  Users can now create up to  127 UNDO tablespaces and the minimum number of UNDO tablespaces required for MySQL 8.0 is 2. UNDO logs are no longer stored in the InnoDB system tablespace (a.k.a ibdata).

MySQL 8.0 was released with the ability to increase and decrease the number of undo tablespaces while the server is online. This was done by changing the value of the setting innodb_undo_tablespaces.  If the setting is increased, new undo tablespaces would be created as needed and put online.  If the setting is decreased, unused undo tablespaces would be taken offline, but not deleted since it might be some time before the transactions using them are completed and purged.  If innodb_undo_tablespaces is increased again, these inactive tablespaces will be reused.

The undo tablespaces created in MySQL 8.0 using the innodb_undo_tablespaces setting have implicitly assigned names like ‘undo_001’ and are created in the same innodb_undo_directory. The file names end with an ‘ibu’ suffix. There are always at least two undo tablespaces so that they can be truncated periodically if they grow larger than the size specified in innodb_max_undo_log_size. The setting innodb_undo_log_truncate is ON by default in 8.0.

MySQL 8.0.14 introduces the ability to control undo tablespaces using SQL. The  innodb_undo_tablespaces setting is deprecated and is always set to 2. These 2 undo tablespaces are still located in the innodb_undo_directory and named ‘undo_001’ and ‘undo_002’.   They are the only undo tablespaces automatically created at startup.  The file names are still ‘undo_001.ibu’ and ‘undo_002.ibu.

All other undo tablespaces will now be created using SQL like this;

CREATE UNDO TABLESPACE any_undo_name ADD DATAFILE ‘any_undo.ibu’;

They can each be given their own name, filename, and location. It is probably wise to include the word ‘undo’ in the name and filename, but that is not required.

The datafile name must end in ‘.ibu’. If only the base name and .ibd suffix is used, then the file will be located in the innodb_undo_directory. Since relative file names in other ADD DATAFILE clauses are considered relative to the default data directory, it would be confusing to allow a relative path in an undo ADD DATAFILE clause (Is it relative to the datadir or the innodb_undo_directory?) so that is not allowed. But you can put an absolute path in here for an undo tablespace. This allows you to create undo tablespaces on a separate disk. Remember though that all remote datafile locations must be listed in the  innodb_directories setting so that the datafiles can be discovered at startup, before any recovery would start.

When you want to take an undo tablespace offline, you can issue this command;

ALTER UNDO TABLESPACE any_undo_name SET INACTIVE;

This command will immediately make this undo tablespace unavailable for new transactions. It does not make it empty or unused immediately. Active transactions will continue to use it and even after those transactions are all committed, other transactions that started before any of these transactions ended may need these undo logs to see older versions of records.

Only after all transactions with an interest in these undo logs are completed and the purge thread has had time to completely clean up these undo logs can the undo tablespace be considered empty. At that time, the inactive undo tablespace can be dropped using;

DROP UNDO TABLESPACE any_undo_name;

Instead of dropping the undo tablespace when it is inactive, it could be made active again with;

ALTER UNDO TABLESPACE any_undo_name SET ACTIVE;

FAQ #1: So how can I tell whether an undo tablespace is active or inactive-but-not-empty or empty?

There is a new column added to the information_schema.innodb_tablespaces table called ‘state’. You can query it like this;

SELECT NAME, SPACE_TYPE, STATE
FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE SPACE_TYPE = ‘Undo’ ORDER BY NAME;

The following query will return 1 when an inactive undo tablespace is finally empty;

SELECT count(*) = 1 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE NAME = ‘any_undo_name’ AND STATE = ’empty’;

FAQ #2: How many of my undo tablespaces can I make inactive?

You can explicitly SET INACTIVE all but 2 of your undo tablespaces. The background undo truncation activity of the purge thread needs to be able to temporarily set one of the remaining two undo tablespaces to inactive so that it can be truncated and then set active again.

FAQ #3: What if I already have more than 2 undo tablespaces defined by innodb_undo_tablespaces and I upgrade to 8.0.14?

Even though InnoDB 8.0.14 will only create 2 implicit undo tablespaces and will issue a deprecation warning when the setting is higher than that, it will still use the undo tablespaces it finds in the innodb_undo_directory. You can make any one of those implicitly named undo tablespaces inactive with ALTER UNDO TABLESPACE name SET ACTIVE; and once they are empty, you can drop any one of those except the first 2.

FAQ #4: Can I turn off innodb_undo_log_truncate and do the same thing explicitly?

Yes. The command ALTER UNDO TABLESPACE name SET INACTIVE; will truncate that undo tablespace as soon as it becomes empty. In this way, a DBA can watch the size of his undo tablespaces and truncate them whenever he chooses.

Summary

Controlling undo tablespaces through SQL is simpler and more flexible than using a configuration setting and it allows the flexibility to choose where the undo tablespaces are located.