Mysql Online Recycling undo Table Space

1 Mysql5.6

1.1 Relevant parameters

MySQL 5.6 adds three parameters, innodb_undo_directory, innodb_undo_logs and innodb_undo_tablespaces, which can be removed from ibdata1 and stored separately.

  • innodb_undo_directory: Specifies a directory that stores undo table spaces separately, defaulting to. (that is, datadir), and can set relative or absolute paths. Although the parameter instance can not be changed directly after initialization, it can be modified by stopping the library first, modifying the configuration file, and then moving the undo table space file.

    Default parameters:

    mysql> show variables like '%undo%';
    +-------------------------+-------+
    | Variable_name           | Value |
    +-------------------------+-------+
    | innodb_undo_directory   | .     |
    | innodb_undo_logs        | 128   |
    | innodb_undo_tablespaces | 0     |
    +-------------------------+-------+
  • innodb_undo_tablespaces: Specify the number of undo table spaces that are stored separately. For example, if set to 3, the undo table spaces are undo001, undo002, undo003, and the initial size of each file defaults to 10M. We recommend setting this parameter to be greater than or equal to 3 for reasons explained below. This parameter instance cannot be changed after initialization

Instance initialization is to modify innodb_undo_tablespaces:

mysql_install_db ...... --innodb_undo_tablespaces

$ ls
...
undo001  undo002  undo003
  • innodb_rollback_segments: 128 by default. Each rollback segment can support 1024 online transactions at the same time. These rollback segments are evenly distributed across the undo table spaces. This variable can be dynamically adjusted, but the number of rollback segments used will not be reduced physically, but will be controlled.

1.2 Use

Before initializing the instance, we only need to set the innodb_undo_tablespaces parameter (recommendation greater than or equal to 3) to set the undo log into a separate undo table space. If you need to put undo log on a faster device, you can set the innodb_undo_directory parameter, but generally we don't do this, because SSD is very popular now. innodb_undo_logs can be unchanged at 128 by default.

Undo logs can be stored outside of ibdata. But this feature is still chicken ribs:

  • First, you must specify a separate Undo tablespace at the time of the installation instance, which cannot be changed after the installation is completed.
  • Undo tablepsace's space id must start from 1, and undo tablespace cannot be added or deleted.

1.3 Big Transaction Testing

mysql> create table test.tbl( id int primary key auto_increment, name varchar(200));
Query OK, 0 rows affected (0.03 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test.tbl(name) values(repeat('1',00));
Query OK, 1 row affected (0.00 sec)

mysql> insert into test.tbl(name) select name from test.tbl;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

...

mysql> insert into test.tbl(name) select name from test.tbl;
Query OK, 2097152 rows affected (24.84 sec)
Records: 2097152  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (7.90 sec)

Observe that undolog has begun to expand! There is no recovery after transaction commit.

$ du -sh undo*
10M    undo001
69M    undo002
10M    undo003

2 Mysql5.7

5.7 Introduces online truncate undo tablespace

2.1 Relevant parameters

Necessary condition:

  • innodb_undo_tablespaces: There are at least two, one of which can be used when cleaning up, and the parameter instance cannot be changed after initialization.
  • innodb_rollback_segments: The number of rollback segments, there will always be a rollback segment allocated to the system table space, 32 reserved to the temporary table space. So if you want to use undo table space, this value should be at least 33. For example, with two undo table spaces, the value is 35. If multiple Undo table spaces are set, the rollback segment in the system table space becomes inactive.

Start parameters:

  • innodb_undo_log_truncate=on
  • innodb_max_undo_log_size: The table space beyond this value is marked truncate, and the dynamic parameter defaults to 1G
  • innodb_purge_rseg_truncate_frequency: Specifies how many times the purge operation is invoked before the rollback segments are released. When rollback segments in undo table space are released, undo table space is truncated. Thus, the smaller the parameter, the higher the frequency of truncate attempts in undo table spaces.

2.2 Cleaning Process

  1. When the undo table space size exceeds innodb_max_undo_log_size, the tag of the table space needs to be cleaned up. Markups are looped to avoid a table space being cleaned up repeatedly.
  2. The rollback segment in the tag table space becomes inactive, and the running transaction awaits execution.
  3. Start purge
  4. After releasing all rollback segments in the undo table space, run truncate and truncate the undo table space to its initial size, which is determined by innodb_page_size. The default size of 16KB corresponds to the table space of 10MB.
  5. Reactivate rollback segments to assign them to new transactions

2.3 Performance Recommendations

The easiest way to avoid performance impacts in truncate table spaces is to increase the number of undo table spaces

2.4 Big Transaction Testing

Configure eight undo table spaces, innodb_purge_rseg_truncate_frequency=10

mysqld --initialize ... --innodb_undo_tablespaces=8

Start testing

mysql> show global variables like '%undo%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory    | ./         |
| innodb_undo_log_truncate | ON         |
| innodb_undo_logs         | 128        |
| innodb_undo_tablespaces  | 8          |
+--------------------------+------------+

mysql> select @@innodb_purge_rseg_truncate_frequency;
+----------------------------------------+
| @@innodb_purge_rseg_truncate_frequency |
+----------------------------------------+
|                                     10 |
+----------------------------------------+

select @@innodb_max_undo_log_size;
+----------------------------+
| @@innodb_max_undo_log_size |
+----------------------------+
|                   10485760 |
+----------------------------+

mysql> create table test.tbl( id int primary key auto_increment, name varchar(200));
Query OK, 0 rows affected (0.03 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test.tbl(name) values(repeat('1',00));
Query OK, 1 row affected (0.00 sec)

mysql> insert into test.tbl(name) select name from test.tbl;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

...

mysql> insert into test.tbl(name) select name from test.tbl;
Query OK, 2097152 rows affected (24.84 sec)
Records: 2097152  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (7.90 sec)

undo table space, expanded to 100MB + and successfully recovered

$ du -sh undo*
10M    undo001
10M    undo002
10M    undo003
10M    undo004
10M    undo005
10M    undo006
125M   undo007
10M    undo008

$ du -sh undo*
10M    undo001
10M    undo002
10M    undo003
10M    undo004
10M    undo005
10M    undo006
10M    undo007
10M    undo008

3 Reference

https://dev.mysql.com/doc/ref...

Tags: MySQL

Posted on Mon, 09 Sep 2019 02:22:10 -0700 by screative