Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.7Mb
PDF (A4) - 37.7Mb
PDF (RPM) - 33.8Mb
HTML Download (TGZ) - 8.4Mb
HTML Download (Zip) - 8.4Mb
HTML Download (RPM) - 7.3Mb
Man Pages (TGZ) - 130.0Kb
Man Pages (Zip) - 185.6Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb

MySQL 8.0 Reference Manual  /  ...  /  Truncating Undo Tablespaces

Pre-General Availability Draft: 2018-02-20

15.7.9 Truncating Undo Tablespaces

To truncate undo tablespaces, the MySQL instance must be configured with a minimum of two undo tablespaces, which is the default and minimum value in MySQL 8.0. A minimum of two undo tablespaces ensures that one undo tablespace remains active while the other is taken offline to be truncated. The number of undo tablespaces is defined by the innodb_undo_tablespaces option. Use this statement to check the value of innodb_undo_tablespaces:

mysql> SELECT @@innodb_undo_tablespaces;
| @@innodb_undo_tablespaces |
|                         2 |

innodb_undo_tablespaces is deprecated and will be removed in a future release.

For information about configuring undo tablespaces, see Section 15.7.8, “Configuring Undo Tablespaces”.

Enabling Truncation of Undo Tablespaces

To truncate undo tablespaces, enable innodb_undo_log_truncate.

mysql> SET GLOBAL innodb_undo_log_truncate=ON;

When innodb_undo_log_truncate is enabled, undo tablespace files that exceed the size limit defined by innodb_max_undo_log_size are marked for truncation. innodb_max_undo_log_size is a dynamic global variable with a default value of 1024 MiB (1073741824 bytes).

mysql> SELECT @@innodb_max_undo_log_size;
| @@innodb_max_undo_log_size |
|                 1073741824 |

You can configure innodb_max_undo_log_size using a SET GLOBAL statement:

mysql> SET GLOBAL innodb_max_undo_log_size=2147483648;

When innodb_undo_log_truncate is enabled:

  1. Undo tablespaces that exceed the innodb_max_undo_log_size setting are marked for truncation. Selection of an undo tablespace for truncation is performed in a circular fashion to avoid truncating the same undo tablespace each time.

  2. Rollback segments residing in the selected undo tablespace are made inactive so that they are not assigned to new transactions. Existing transactions that are currently using rollback segments are allowed to complete.

  3. The purge system frees rollback segments that are no longer needed.

  4. After all rollback segments in the undo tablespace are freed, the truncate operation runs and the undo tablespace is truncated to its initial size. The initial size of an undo tablespace file depends on the innodb_page_size value. For the default 16k InnoDB page size, the initial undo tablespace file size is 10MiB. For 4k, 8k, 32k, and 64k page sizes, the initial undo tablespace files sizes are 7MiB, 8MiB, 20MiB, and 40MiB, respectively.

    The size of an undo tablespace after a truncate operation may be larger than the initial size due to immediate use following the completion of the operation.

    The innodb_undo_directory option defines the location of undo tablespace files. The default value of . represents the directory where InnoDB creates other log files by default.

    mysql> SELECT @@innodb_undo_directory;
    | @@innodb_undo_directory |
    | .                       |
  5. The rollback segments are reactivated so that they can be assigned to new transactions.

Expediting Truncation of Undo Tablespace Files

An undo tablespace cannot be truncated until its rollback segments are freed. Normally, the purge system frees rollback segments once every 128 times that purge is invoked. To expedite the truncation of undo tablespaces, use the innodb_purge_rseg_truncate_frequency option to temporarily increase the frequency with which the purge system frees rollback segments. The default innodb_purge_rseg_truncate_frequency setting is 128, which is also the maximum value.

mysql> SELECT @@innodb_purge_rseg_truncate_frequency;
| @@innodb_purge_rseg_truncate_frequency |
|                                    128 |

To increase the frequency with which the purge thread frees rollback segments, decrease the value of innodb_purge_rseg_truncate_frequency. For example:

mysql> SET GLOBAL innodb_purge_rseg_truncate_frequency=32;

Performance Impact of Truncating Undo Tablespace Files Online

While an undo tablespace is truncated, rollback segments in that tablespace are temporarily deactivated. The remaining active rollback segments in the other undo tablespaces assume responsibility for the entire system load, which may result in a slight performance degradation. The degree of performance degradation depends on a number of factors including:

  • Number of undo tablespaces

  • Number of undo logs

  • Undo tablespace size

  • Speed of the I/O susbsystem

  • Existing long running transactions

  • System load

User Comments
Sign Up Login You must be logged in to post a comment.