Mysql online recovery of undo table space actual combat record

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 move the undo log from ibdata1 and store it separately.

• innodb_undo_directory: Specifies the directory where the undo tablespace is stored separately. The default value is . (i.e. datadir). You can set a relative path or an absolute path. Although this parameter instance cannot be modified directly after initialization, you can modify it by stopping the database, 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 separately stored undo tablespaces. 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, the reason will be explained below. This parameter instance cannot be modified after initialization

Instance initialization is to modify innodb_undo_tablespaces:

mysql_install_db ...... --innodb_undo_tablespaces

$ ls
...
undo001 undo002 undo003

• innodb_rollback_segments: The default value is 128. Each rollback segment can support 1024 online transactions simultaneously. These rollback segments are evenly distributed to each undo tablespace. This variable can be adjusted dynamically, but the physical rollback segments will not be reduced, it will only control the number of rollback segments used.
  

1.2 Usage

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 the undo log to a separate undo tablespace. If you need to put the undo log on a faster device, you can set the innodb_undo_directory parameter, but we generally don't do this because SSDs are very popular now. innodb_undo_logs can be set to 128 by default.

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

• First, you must specify an independent Undo tablespace when installing the instance. It cannot be changed after the installation is complete.
• The space ID of the undo tablespace must start from 1. The 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 the undolog has begun to expand! The space is not reclaimed after the transaction is committed.

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

2 Mysql5.7

5.7 introduced online truncate undo tablespace

2.1 Related parameters

Necessary conditions:

• innodb_undo_tablespaces: At least two, so that one can use the other during cleanup. This parameter cannot be changed after the instance is initialized.
• innodb_rollback_segments: The number of rollback segments. There is always one rollback segment allocated to the system tablespace, and 32 are reserved for temporary tablespaces. So if you want to use the undo tablespace, this value must be at least 33. For example, if two undo tablespaces are used, this value is 35. If you set up multiple undo tablespaces, the rollback segments in the system tablespace become inactive.

Startup parameters:

• innodb_undo_log_truncate=on
• innodb_max_undo_log_size: Tablespaces exceeding this value will be marked as truncate. The default value of the dynamic parameter is 1G
• innodb_purge_rseg_truncate_frequency: Specifies how many times the purge operation is invoked before releasing the rollback segments. The undo tablespace will be truncated only when the rollback segments in the undo tablespace are released. It can be seen that the smaller the parameter, the more frequently the undo tablespace is attempted to be truncate.

2.2 Cleaning process

1. When the size of the undo tablespace exceeds innodb_max_undo_log_size, the tablespace is marked for cleanup. The marking will be repeated to avoid a table space being cleaned repeatedly.
2. The rollback segment in the mark tablespace becomes inactive, and the running transactions wait for completion.
3. Start purging
4. After releasing all rollback segments in the undo tablespace, run truncate and truncate the undo tablespace to its initial size. The initial size is determined by innodb_page_size. The default size of 16KB corresponds to a tablespace of 10MB.
5. Reactivate rollback segments so they can be assigned to new transactions

2.3 Performance Recommendations

The easiest way to avoid a performance hit when truncating a tablespace is to increase the number of undo tablespaces.

2.4 Large Transaction Testing

Configure 8 undo tablespaces, 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)

The undo tablespace is successfully recovered after expanding to 100MB+

$ 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

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.