MySQL5.7 new feature--online shrink undo table Space

1. MySQL 5.5-era undo log

In MySQL5.5 and before, you will find that as the database goes online more and more longer, the Ibdata1 file (that is, innodb shared table space, or system tablespace) will become larger, resulting in 2 more obvious problems:

(1) The disk remaining space is getting smaller, often to the late to add disk;

(2) The physical backup time is getting longer and the backup files are getting larger.

What's going on here?

In addition to the natural increase in the amount of data, InnoDB's undo log is stored in the IBDATA1 as well as before MySQL5.5. Once a large transaction occurs, the space occupied by the undo log used by this large firm will remain in the ibdata1, even if the transaction is closed.

So the question is, is there a way to clean up the space occupied by the free undo log above from the ibdata1? The answer is no direct way, only the entire library export SQL files, and then re-initialize the MySQL instance, and then the whole library import.

2. MySQL 5.6-era undo Log

MySQL 5.6 Adds 3 parameters, Innodb_undo_directory, Innodb_undo_logs and innodb_undo_tablespaces, to remove the undo log from the IBDATA1 and store it separately.

Here are some explanations for these 3 parameters:

(1) innodb_undo_directory, specify the directory where the undo table space is stored, by default. (That is, datadir), you can set a relative path or an absolute path. Although the parameter instance initialization cannot be changed directly, the parameter can be modified by first stopping the library, modifying the configuration file, and then moving the undo tablespace file.

(2) Innodb_undo_tablespaces, specify the number of undo table spaces that are stored separately, for example, if set to 3, the Undo table space is undo001, undo002, undo003, and the initial size of each file defaults to 10M. The parameter we recommend is set to greater than or equal to 3, and the reason is explained below. The instance of this parameter cannot be changed after initialization;

(3) Innodb_undo_logs, specify the number of rollback segments (previous version of the parameter name is innodb_rollback_segments), default 128. Each rollback segment can support up to 1024 online transactions at a time. These rollback segments are evenly distributed across the undo table space. The variable can be dynamically adjusted, but the physical rollback segment is not reduced, but only the number of rollback segments used is controlled.

In terms of actual usage, before initializing an instance, we only need to set the Innodb_undo_tablespaces parameter (which is recommended to be greater than or equal to 3) to set the undo log to 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, because SSDs are now very popular. The innodb_undo_logs can be unchanged by default to 128.

3. MySQL 5.7-era undo log

So the question comes again, can undo log be reduced after splitting it? MySQL 5.7 introduces new parameters, Innodb_undo_log_truncate, which can be opened online to shrink the undo table space after splitting. The undo tablespace file can be shrunk online when the following 2 conditions are met:

(1) innodb_undo_tablespaces>=2. Because the file is in the inactive state when truncate the undo tablespace, if there are only 1 undo tablespace, the entire system will be in an unusable state during this process. To minimize the impact of truncate on the system, it is recommended to set this parameter at least 3;

(2) innodb_undo_logs>=35 (default 128). Because in MySQL 5.7, the first undo log is always in the system tablespace, and the other 32 undo logs are assigned to the temporary tablespace, IBTMP1, at least 2 undo logs to ensure that there are at least 1 undo logs in each of the 2 undo table spaces;

When the above 2 conditions are met, setting the Innodb_undo_log_truncate to on will enable automatic truncate of the undo table space, which is also related to the following 2 parameters:

(1) Innodb_max_undo_log_size,undo table space file exceeds this value is marked as retractable, the default 1G, can be modified online;

(2) Innodb_purge_rseg_truncate_frequency, specifies how many times the purge operation is aroused before releasing rollback segments. The Undo table space is truncate when the rollback segments in the Undo Tablespace is released. Thus, the smaller the parameter, the more frequently the undo table space is tried truncate.

4. Truncate example of the undo tablespace for MySQL 5.7

(1) First ensure that the following parameters are set correctly:

# for experimental convenience, we reduce this value

Innodb_max_undo_log_size = 100M

Innodb_undo_log_truncate = On

Innodb_undo_logs = 128

Innodb_undo_tablespaces = 3

# for experimental convenience, we reduce this value

Innodb_purge_rseg_truncate_frequency = 10

(2) Create a table:

mysql> CREATE TABLE t1 (ID int primary key auto_increment, name varchar (200));

Query OK, 0 rows affected (0.13 sec)

(3) inserting test data

mysql> INSERT into T1 (name) VALUES (Repeat (' a ', 200));

mysql> INSERT into T1 (name) select name from T1;

mysql> INSERT into T1 (name) select name from T1;

mysql> INSERT into T1 (name) select name from T1;

mysql> INSERT into T1 (name) select name from T1;

When the undo tablespace file size is below, you can see that an undo file has exceeded 100M:

-rw-r-----1 mysql mysql 13M Feb 17:59 undo001

-rw-r-----1 mysql mysql 128M Feb 17:59 undo002

-rw-r-----1 mysql mysql 64M Feb 17:59 undo003

At this point, in order for the purge thread to run, several DELETE statements can be run:

mysql> Delete from T1 limit 1;

mysql> Delete from T1 limit 1;

mysql> Delete from T1 limit 1;

mysql> Delete from T1 limit 1;

To view the undo file size again:

-rw-r-----1 mysql mysql 13M Feb 18:05 undo001

-rw-r-----1 mysql mysql 10M Feb 18:05 undo002

-rw-r-----1 mysql mysql 64M Feb 18:05 undo003

As you can see, the undo file over 100M has shrunk to 10M.

This article is from the "stupid Child's DBA path" blog, please be sure to keep this source http://fengfeng688.blog.51cto.com/4896812/1919574

MySQL5.7 new feature--online shrink undo table Space