Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.2Mb
PDF (A4) - 37.2Mb
PDF (RPM) - 36.6Mb
HTML Download (TGZ) - 10.1Mb
HTML Download (Zip) - 10.2Mb
HTML Download (RPM) - 8.8Mb
Man Pages (TGZ) - 206.0Kb
Man Pages (Zip) - 313.0Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Truncating Undo Logs That Reside in Undo Tablespaces

15.7.8 Truncating Undo Logs That Reside in Undo Tablespaces

You can truncate undo logs that reside in undo tablespaces, provided that the following conditions are true:

  • Your MySQL instance is configured with a minimum of two undo tablespaces (innodb_undo_tablespaces=2). When an undo tablespace is truncated, it is temporarily taken offline. For the server to function, there must be at least one other active undo tablespace. The number of undo tablespaces is defined by the innodb_undo_tablespaces option, which can only be set when the MySQL instance is initialized. The default value is 0. To check the value of innodb_undo_tablespaces, submit the following query:

    mysql> SELECT @@innodb_undo_tablespaces;
    +---------------------------+
    | @@innodb_undo_tablespaces |
    +---------------------------+
    |                         2 |
    +---------------------------+
    1 row in set (0.00 sec)
  • innodb_undo_logs, which defines the number of rollback segments used by InnoDB, must be set to 35 or greater. A setting of 35 or greater ensures that a redo-enabled undo log is assigned to each of the two undo tablespaces. With an innodb_undo_logs setting of 35:

    • The first rollback segment always resides in the system tablespace (when undo tablespaces are present, this rollback segment is inactive)

    • Rollback segments 2 to 33 reside in the shared temporary tablespace (ibtmp1)

    • The 34th rollback segment resides in the first undo tablespace (if present)

    • The 35th rollback segment resides in the second undo tablespace (if present)

    There is a many-to-one relationship between rollback segments and undo tablespaces. If the number of allocated rollback segments is greater than 35, the additional rollback segments are assigned to undo tablespaces in a round-robin fashion. For example, if you have 2 undo tablespaces (undo tablespace 1 and undo-tablespace 2) and innodb_undo_logs=37, undo-tablespace 1 and undo-tablespace 2 would each be assigned a second rollback segment.

    By default, innodb_undo_logs is set to 128, which is also the maximum value. To check the value of innodb_undo_logs, submit the following query:

    mysql> SELECT @@innodb_undo_logs;
    +--------------------+
    | @@innodb_undo_logs |
    +--------------------+
    |                128 |
    +--------------------+
    1 row in set (0.00 sec)

    innodb_undo_logs is a dynamic global variable and can be configured using a SET GLOBAL statement:

    mysql> SET GLOBAL innodb_undo_logs=128;

Enabling Truncation of Undo Tablespaces

To truncate undo logs that reside in undo tablespaces, you must first enable innodb_undo_log_truncate.

mysql> SET GLOBAL innodb_undo_log_truncate=ON;

When you enable innodb_undo_log_truncate, 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 |
+----------------------------+
1 row in set (0.00 sec)

You can configure innodb_max_undo_log_size using a SET GLOBAL statement:

mysql> SET GLOBAL innodb_max_undo_log_size=2147483648;
Query OK, 0 rows affected (0.00 sec)

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 round-robin 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 allocated 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 is 10MB.

    Note

    If you check the size of an undo tablespace after a truncation operation, the file size may be larger than 10MB due to immediate use following the completion of the truncation operation. The innodb_undo_directory option defines the location of undo tablespace files. The default value of . represents directory where InnoDB creates its other log files by default.

    mysql> select @@innodb_undo_directory;
    +-------------------------+
    | @@innodb_undo_directory |
    +-------------------------+
    | .                       |
    +-------------------------+
    1 row in set (0.00 sec)
  5. The rollback segments are reactivated so that they can be allocated 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, you can use the innodb_purge_rseg_truncate_frequency option to temporarily increase the frequency with which the purge system frees rollback segments. By default, innodb_purge_rseg_truncate_frequency is 128, which is also the maximum value.

mysql> select @@innodb_purge_rseg_truncate_frequency;
+----------------------------------------+
| @@innodb_purge_rseg_truncate_frequency |
+----------------------------------------+
|                                    128 |
+----------------------------------------+
1 row in set (0.00 sec)

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;
Query OK, 0 rows affected (0.00 sec)

Performance Impact of Truncating Undo Tablespace Files Online

While an undo tablespace truncation operation is in progress, rollback segments in one undo tablespace are temporarily deactivated. For example, if you have 2 undo tablespaces (innodb_undo_tablespaces=2) and 128 allocated undo logs (innodb_undo_logs=128), 95 of the undo logs reside in the two undo tablespaces (48 rollback segments in one undo tablespace and 47 in the other). If the first undo tablespace is taken offline, 48 undo logs are made inactive, reducing the undo log resource by slightly more than half. While the truncation operation is in progress, the remaining undo logs 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.