Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to recycle undo table space online in Mysql. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

1 Mysql5.6

1.1 related parameters

MySQL 5.6adds parameters innodb_undo_directory, innodb_undo_logs and innodb_undo_tablespaces, so that undo log can be removed from ibdata1 and stored separately.

Innodb_undo_directory: specify a separate directory for undo tablespaces. The default is. (that is, datadir). You can set either relative or absolute paths. Although the parameter instance cannot be changed directly after initialization, the parameter can be modified by stopping the library, 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: specify the number of undo tablespaces to be stored separately. For example, if set to 3, the undo tablespaces 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, as 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: 128by default. Each rollback segment can support 1024 online transactions simultaneously. These rollback segments are distributed evenly across undo tablespaces. This variable can be adjusted dynamically, but the number of physical rollback segments will not be reduced, but 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 greater than or equal to 3) to set the undo log to a separate undo tablespace. 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 these days. Innodb_undo_logs can be fixed at 128 by default.

Undo log can be stored outside of ibdata. But this feature is still a chicken rib:

First of all, you must specify a stand-alone Undo tablespace in the install instance, which cannot be changed after the install is completed.

The space id for Undo tablepsace must start at 1 and undo tablespace cannot be added or deleted.

1.3 Major transaction testing

Mysql > create table test.tbl (id int primary key auto_increment, name varchar); 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); 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: 0mysql > commit;Query OK, 0 rows affected (7.90 sec)

Observe that undolog has begun to swell! The space is also not reclaimed after the transaction commit.

$du-sh undo*10M undo00169M undo00210M undo003

2 Mysql5.7

5.7 introduction of online truncate undo tablespace

2.1 related parameters

Necessary conditions:

Innodb_undo_tablespaces: there are at least two, so one can use the other when cleaning. This parameter cannot be changed after the instance is initialized.

Innodb_rollback_segments: the number of rollback segments, there will always be one rollback segment allocated to the system table space, 32 reserved for temporary table space. So if you want to use undo tablespaces, this value should be at least 33. For example, if you use two undo tablespaces, this value matches 35. If you set up 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 exceeding this value are marked as truncate, and the dynamic parameter defaults to 1G

Innodb_purge_rseg_truncate_frequency: specifies how many times the purge operation is called before releasing the rollback segments. When the rollback segments in the undo tablespace is freed, the undo tablespace is truncate. Thus, the smaller this parameter, the more often undo tablespaces are tried truncate.

2.2 cleanup process

When the size of the undo tablespace exceeds innodb_max_undo_log_size, marking the tablespace needs to be cleaned up. Tags are looped to prevent a tablespace from being repeatedly cleaned.

The rollback segment in the marked tablespace becomes inactive and the running transaction waits for execution to finish.

Start purge

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 the 16KB table space is 10MB.

Reactivate rollback segments to assign them to new transactions

2.3 performance recommendation

The easiest way to avoid performance impact on truncate tablespaces is to increase the number of undo tablespaces

2.4 Major 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) 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 sec) mysql > insert into test.tbl (name) select name from test.tbl;Query OK, 1 row affected (0 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: 0mysql > commit;Query OK, 0 rows affected (7.90 sec)

Undo tablespace, successfully recycled 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

The above is how to achieve online recycling of undo tablespaces in the Mysql shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.