5 min To learn about MySQL5.7 's undo log online Shrinkage new feature

Part1: written in the first

In the MysQL5.6 release, the Undo log rollback log can be separated into a separate tablespace, with the drawback that the space size cannot be reclaimed, until Mysql5.7,but MariadDB10.1 is not supported.

This article describes and demonstrates how MysQL5.7 shrinks the undo log online.

The Undo log log is saved in the shared tablespace ibdata1 file, and as the database runs, the ibdata1 file becomes larger, and in previous versions of MySQL database, if we want to reclaim the space occupied by ibdata1 files, it can be very complex and difficult, You must first export the Mysqldump-a whole library, then delete the data directory, then re-initialize the database, and finally import the full library backup, to achieve ibdata1 recycling.

MySQL Full library backup method can be consulted:

http://suifu.blog.51cto.com/9167728/1758022

The installation method of MySQL5.7 can be used for reference:

http://suifu.blog.51cto.com/9167728/1855415

① first we need to adjust the my.cnf parameters

innodb_undo_directory= /data/undolog innodb_undo_tablespaces=4 innodb_undo_logs=128 innodb_max_undo_log_size=1G innodb_purge_rseg_truncate_frequency innodb_undo_log_truncate=1

Warning: Warning

Where the innodb_undo_directory parameter needs to be written to MY.CNF when the database is initialized, otherwise the following error is reported:

Expected to open 4 undo tablespaces but is able to find only 0 undo tablespaces. Set the innodb_undo_tablespaces parameter to the correct value and retry. Suggested value is 0

Part2: MySQL 5.7 Online Recycling Undolog

① first, an entire table is updated with a table of 1 million rows.

mysql> show variables  like  ‘innodb_undo%‘ ; + --------------------------+---------------+ | Variable_name            | Value         | + --------------------------+---------------+ | innodb_undo_directory    | /data/undolog | | innodb_undo_log_truncate |  ON             | | innodb_undo_logs         | 128           | | innodb_undo_tablespaces  | 4             | + --------------------------+---------------+ 4  rows  in  set  (0.00 sec)   mysql>  update  helei  set  c1=1; Query OK, 1000000  rows  affected (45.48 sec) Rows  matched: 1000000  Changed: 1000000  Warnings: 0  mysql>  update  helei  set  c2=222; Query OK, 1000000  rows  affected (43.25 sec) Rows  matched: 1000000  Changed: 1000000  Warnings: 0  mysql>  update  helei  set  c4= ‘heleiheleiheleiheleihel‘ ; Query OK, 1000000  rows  affected (10.28 sec) Rows  matched: 1000000  Changed: 1000000  Warnings: 0

② Note the log size of Undolog

[Email protected] undolog]# Ls-lshrt

Total 412M

128m-rw-r-----1 mysql mysql 128M Sep 16:56 undo004

76m-rw-r-----1 mysql mysql 76M Sep 16:56 undo003

136m-rw-r-----1 mysql mysql 136M Sep 16:56 undo001

72m-rw-r-----1 mysql mysql 72M Sep 16:56 undo002

③error.log Log

2016-09-26T23:51:06.062828Z 0 [Note] InnoDB: Truncating UNDO tablespace with space identifier 1 2016-09-26T23:51:06.159077Z 0 [Note] InnoDB: Completed truncate of UNDO tablespace with space identifier 1 2016-09-26T23:51:06.159101Z 0 [Note] InnoDB: Truncating UNDO tablespace with space identifier 2 2016-09-26T23:51:06.242355Z 0 [Note] InnoDB: Completed truncate of UNDO tablespace with space identifier 2 2016-09-26T23:51:06.242378Z 0 [Note] InnoDB: Truncating UNDO tablespace with space identifier 3 2016-09-26T23:51:06.313036Z 0 [Note] InnoDB: Completed truncate of UNDO tablespace with space identifier 3 2016-09-26T23:51:06.313060Z 0 [Note] InnoDB: Truncating UNDO tablespace with space identifier 4 2016-09-26T23:51:06.403003Z 0 [Note] InnoDB: Completed truncate of UNDO tablespace with space identifier 4

④ Observing physical files

[Email protected] undolog]# Ls-lshrt

Total 168M

76m-rw-r-----1 mysql mysql 76M Sep 16:56 undo003

10m-rw-r-----1 mysql mysql 10M Sep 16:56 undo001

10m-rw-r-----1 mysql mysql 10M Sep 16:56 undo004

72m-rw-r-----1 mysql mysql 72M Sep 16:56 undo002

You can see that the undo log over the 100M SetPoint has been reclaimed, and the default is 10m,undo log space is released

Part3: Meaning

This feature reduces disk usage and improves backup speeds for physical backup software such as Xtrabackup.

5 min To learn about MySQL5.7 's undo log online Shrinkage new feature