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

In this issue, the editor will bring you about how MySQL5.7 recycles the physical file space of undo log. The article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

The undo log of MySQL is stored in a shared tablespace ibdata1 file, and as the business runs, the ibdata1 becomes larger and larger. In the previous version of MySQL5.7, it was necessary to stop using mysqldump to export data, then rebuild the database, and then import data to solve this problem. MySQL5.7 provides a mechanism for online recycling.

In the MySQL5.6 version, the undo log rollback segment can be separated into a separate tablespace, but there is still no mechanism for recycling, using three parameters

Innodb_undo_directory (specify the directory to store, default is the data directory)

Innodb_undo_logs=128 (specify rollback segment 128KB)

Innodb_undo_tablespaces=4 (specify how many undo log files there are)

The following are the default settings for MySQL5.6 version parameters:

Click (here) to collapse or open

Mysql > show variables like 'innodb_undo_%'

+-+ +

| | Variable_name | Value |

+-+ +

| innodb_undo_directory |. | |

| | innodb_undo_logs | 128 | |

| | innodb_undo_tablespaces | 0 | |

+-+ +

The following are the default settings for MySQL5.7 version parameters

Click (here) to collapse or open

Mysql > show variables like 'innodb_undo_%'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_undo_directory |. / | |

| | innodb_undo_log_truncate | OFF |

| | innodb_undo_logs | 128 | |

| | innodb_undo_tablespaces | 0 | |

+-+ +

Note: separate undo log from the shared tablespace, set parameters to my.cnf during MySQL installation, and detach after MySQL startup will cause an error.

The parameters involved in the MySQL5.7 undo log recycling mechanism are as follows:

The innodb_undo_log_truncate parameter is set to 1, which starts to recycle undo log log files online, and the default is OFF.

The innodb_undo_tablespaces parameter must be greater than or equal to 2. When you reclaim a log file, make sure that another undo log is available.

The number of innodb_undo_logs undo rollback segments is at least 35 or greater, and the default is 128.

After the innodb_max_undo_log_size exceeds this threshold, the penalty truncate recycling action is performed. By default, 1GB of truncate is recycled and becomes 10MB.

Innodb_purge_resg_truncate_grequency controls how often the undo log is recycled. If you want to increase the frequency of releasing the rollback segment, you have to lower the innodb_purge_resg_truncate_grequency setting.

This is how the MySQL5.7 recycle undo log physical file space shared by Xiaobian is like. 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.