Shulou(Shulou.com)06/01 Report--

This article mainly explains "how MySQL5.7 shrinks undo table space online". The content in the article is simple and clear, easy to learn and understand. Please follow the editor's train of thought to study and learn "how MySQL5.7 shrinks undo table space online".

1. Undo log in the era of MySQL 5.5

Before and before MySQL5.5, you will find that as the database goes online longer and longer, the ibdata1 file (that is, the shared tablespace of InnoDB, or system tablespace) will become larger and larger, which will cause two obvious problems:

(1) the remaining space of the disk is getting smaller and smaller, and the disk is often added at a later stage.

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

What's going on?

In addition to the natural increase in the amount of data, the undo log of InnoDB is also stored in ibdata1 before and before MySQL5.5. Once a large transaction occurs, the space occupied by the undo log used by the large firm will always exist in the ibdata1, even if the transaction has been closed.

So the question is, is there a way to clean up the free undo log space mentioned above from the ibdata1? The answer is that there is no direct way, but to export the sql file from the whole library, then reinitialize the mysql instance, and then import the whole library.

2. Undo log in MySQL 5.6era

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.

Here is an explanation for these three parameters:

(1) innodb_undo_directory, specify a separate directory for undo tablespaces. The default is. (i.e. datadir). You can set a relative path or an absolute path. Although the parameter instance cannot be changed directly after initialization, it can be modified by stopping the library, modifying the configuration file, and then moving the undo tablespace file.

(2) 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.

(3) innodb_undo_logs, which specifies the number of rollback segments (in previous versions, the parameter name is innodb_rollback_segments). 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.

In terms of practical use, before initializing the instance, we only need to set the innodb_undo_tablespaces parameter (it is recommended that it is 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 do this because SSD is very popular these days. Innodb_undo_logs can be fixed at 128 by default.

3. Undo log in MySQL 5.7era

So here comes the question again: can undo log be shrunk after it is taken apart? MySQL 5.7introduces a new parameter, innodb_undo_log_truncate, which can shrink the split undo table space online when turned on. Undo tablespace files can be shrunk online if the following two conditions are met:

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

(2) innodb_undo_logs > = 35 (default 128). Because in MySQL 5.7The first undo log is always in the system tablespace, and the other 32 undo log are allocated to the temporary tablespace, that is, ibtmp1, and there are at least two undo log to ensure at least one undo log in each of the two undo tablespaces.

After the above two conditions are met, set innodb_undo_log_truncate to ON to enable automatic truncate of undo tablespaces, which is also related to the following two parameters:

(1) innodb_max_undo_log_size,undo tablespace files are marked as shrinking if they exceed this value. Default is 1G, which can be modified online.

(2) innodb_purge_rseg_truncate_frequency, which specifies how many times the purge operation is called before releasing 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.

4. Truncate example of undo tablespace for MySQL 5.7s

(1) first, make sure that the following parameters are set correctly:

# for the convenience of the experiment, we reduce this value

Innodb_max_undo_log_size = 100m

Innodb_undo_log_truncate = ON

Innodb_undo_logs = 128,

Innodb_undo_tablespaces = 3

# for the convenience of the experiment, 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)

Query OK, 0 rows affected (0.13 sec)

(3) insert test data

Mysql > insert into T1 (name) values (repeat); Query OK, 1 row affected (0.01 sec) mysql > insert into T1 (name) select name from T1 scene query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: Warnings:

Mysql > insert into T1 (name) select name from T1 politics query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: Warnings:

Mysql > insert into T1 (name) select name from T1 politics query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: Warnings:

...

Mysql > insert into T1 (name) select name from T1 politics query OK, 8388608 rows affected (2 min 11.31 sec)

Records: 8388608 Duplicates: Warnings:

At this point, the size of the undo tablespace file is as follows, and you can see that one undo file has exceeded 100m:

-rw-r- 1 mysql mysql 13M Feb 17 17:59 undo001-rw-r- 1 mysql mysql 128m Feb 17 17:59 undo002-rw-r- 1 mysql mysql 64m Feb 17 17:59 undo003

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

Mysql > delete from T1 limit 1 politics query OK, 1 row affected (0.00 sec) mysql > delete from T1 limit 1 politics query OK, 1 row affected (0.00 sec) mysql > delete from T1 limit 1 politics query OK, 1 row affected (0.00 sec) mysql > delete from T1 limit 1 politics query OK, 1 row affected (0.00 sec)

Then check the undo file size:

-rw-r- 1 mysql mysql 13M Feb 17 18:05 undo001-rw-r- 1 mysql mysql 10M Feb 17 18:05 undo002-rw-r- 1 mysql mysql 64m Feb 17 18:05 undo003

As you can see, undo files over 100m have shrunk to 10m.

Thank you for reading, the above is the content of "how MySQL5.7 shrinks undo tablespaces online". After the study of this article, I believe you have a deeper understanding of how MySQL5.7 shrinks undo tablespaces online, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.