MySQL 8 CREATE UNDO TABLESPACE
The latest release of MySQL 8.0.14 contains the capability to switch the number and position of UNDO tablespaces using SQL. Users can now achieve UNDO tablespaces by means of SQL from the MySQL client.
More undo tablespaces aid busy schemes handle a greater number of read-write simultaneous transactions by consuming more rollback sections obtainable to provide undo logs to those transactions. Users can now make up to 127 UNDO tablespaces and the least number of UNDO tablespaces compulsory for MySQL 8.0 is 2. UNDO logs are no longer kept in the InnoDB scheme tablespace (a.k.a ibdata).
MySQL 8.0 was released with the aptitude to upsurge and reduction the number of undo tablespaces while the server is operational. This was complete by altering the value of the setting innodb_undo_tablespaces. If the setting is improved, new undo tablespaces would be formed as wanted and put online. If the setting is reduced, vacant undo tablespaces would be taken off, but not erased since it might be some time before the dealings using them are finished and eliminated. If innodb_undo_tablespaces is augmented again, these sedentary tablespaces will be recycled.
The undo tablespaces formed in MySQL 8.0 using the innodb_undo_tablespaces setting have indirectly given names like ‘undo_001’ and are formed in the same innodb_undo_directory. The file terms end with an ‘ibu’ suffix. There are continuously at least two undo tablespaces so that they can be shortened occasionally if they grow greater than the size stated in innodb_max_undo_log_size. The setting innodb_undo_log_truncate is ON by defaulting in 8.0.
MySQL 8.0.14 presents the capability to control undo tablespaces using SQL. The innodb_undo_tablespaces setting is denounced and is constantly set to 2. These 2 undo tablespaces are silent situated in the innodb_undo_directory and named ‘undo_001’ and ‘undo_002’. They are the only undo tablespaces repeatedly shaped at startup. The file terms are silent ‘undo_001.ibu’ and ‘undo_002.ibu.
All additional loosen tablespaces will now be shaped using SQL like this;
CREATE UNDO TABLESPACE anyundoname ADD DATAFILE ‘anyundo.ibu’;
They can each be agreed their own name, filename, and place. It is possibly intelligent to include the word ‘undo’ in the name and filename, but that is not essential.
The datafile name essential end in ‘.ibu’. If only the improper name and .ibd suffix is used, then the file will be situated in the innodb_undo_directory. Since comparative file names in other ADD DATAFILE sections are careful comparative to the default data directory, it would be unclear to allow a qualified path in an undo ADD DATAFILE section (Is it comparative to the datadir or the innodb_undo_directory?) so that is not allowable. But you can put an complete path in here for an untie tablespace. This allows you to make untie tablespaces on a distinct disk. Think of though that all distant datafile places must be registered in the innodb_directories situation so that the datafiles can be exposed at startup, before any retrieval would jump.
When you need to take an undo tablespace off, you can issue this expertise;
ALTER UNDO TABLESPACE anyundoname SET INACTIVE;
This expertise will directly make this undo tablespace inaccessible for new transactions. It does not make it blank or unused directly. Full of life transactions will last to use it and even after those transactions are all dedicated, other transactions that happening before any of these transactions finished may need these undo logs to see older types of accounts.
Only after all transactions with an attention in these undo logs are finished and the elimination thread has had time to finally clean up these untie logs can the undo tablespace be well thought-out empty. At that time, the lazy undo tablespace can be throw down using;
DROP UNDO TABLESPACE anyundoname;
Instead of reducing the undo tablespace when it is lazy, it could be made dynamic again with;
ALTER UNDO TABLESPACE anyundoname SET ACTIVE;