Mysql Online Recovery of undo Tablespace Actual Combat Records

1 Mysql5.6

1.1 Related parameters

MySQL 5.6 adds three parameters, innodb_undo_directory, innodb_undo_logs and innodb_undo_tablespaces, which can remove undo log from ibdata1 and store it separately.

innodb_undo_directory: Specifies the directory where the undo table space will be stored separately. The default is. (that is, datadir). Relative or absolute paths can be set. 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 tablespace 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: Specifies the number of undo tablespaces stored separately. For example, if it is set to 3, the undo tablespaces are undo001, undo002 and 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 in each undo tablespace. This variable can be adjusted dynamically, but the physical rollback segments will not decrease, only the number of rollback segments used will be controlled.

1.2 Use

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

undo log can be stored outside of ibdata. But this feature is still chicken ribs:

First, you must specify a stand-alone Undo tablespace at the time of the install instance, which cannot be changed after install is completed. space id of Undo tablepsace must start at 1, undo tablespace cannot be added or deleted.

1.3 Large 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! Space is not reclaimed after transaction commit.

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

2 Mysql5.7

5.7 Introduced on-line truncate undo tablespace

2.1 Related parameters

Requirements:

innodb_undo_tablespaces: There are at least two, so that one can be used when cleaning, and the parameter instance cannot be changed after initialization innodb_rollback_segments: The number of rollback segments, with one always allocated to the system tablespace and 32 reserved for the temporary tablespace. So if you want to use the undo tablespace, this value should be at least 33. For example, using two undo tablespaces, this value is matched with 35. If you set more than one undo tablespace, the rollback segment in the system tablespace becomes inactive.

Startup parameters:

innodb_undo_log_truncate=on innodb_max_undo_log_size: Tablespaces that exceed this value are marked as truncate, and the dynamic parameter defaults to 1G innodb_purge_rseg_truncate_frequency: Specifies how many times an purge operation is called up before rollback segments is released. When rollback segments in undo table space is released, undo table space will be truncate. Thus, the smaller the parameter, the more frequently the undo tablespace is attempted with truncate.

2.2 Cleaning process

When the size of the undo tablespace exceeds innodb_max_undo_log_size, mark that the tablespace needs cleaning. Marking loops around to avoid 1 tablespace being cleaned repeatedly. The rollback segment in the tag table space becomes inactive, and the running transaction waits to finish execution. Start purge After releasing all the rollback segments in the undo tablespace, run truncate and truncate the undo tablespace to its initial size, which is determined by innodb_page_size. By default, the size of 16KB corresponds to the tablespace of 10MB Reactivate rollback segments to assign them to new transactions

2.3 Performance Recommendations

The easiest way to avoid affecting performance when truncate tablespaces is to increase the number of undo tablespaces

2.4 Large Transaction Testing

Configure 8 undo table spaces, innodb_purge_rseg_truncate_frequency=10

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

Begin 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 tablespace, expanded to 100MB + and recovered successfully

$ 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...

Summarize