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

Undo log rollback log physical file space recovery

Before MySQL5.6

Undo log in the ibdata1 file, the ibdata1 file will become larger and larger. If you want to recycle, you must export the full library, delete the data directory, reinitialize the database, and finally import the whole library in order to achieve ibdata1 recycling.

MySQL5.6

Undo log rollbacks can be separated into a separate tablespace, but the space size cannot be reclaimed.

MySQL5.7

Support online recycling

1. The following parameters are specified in the configuration file. If you specify the data later, an error will be reported.

# Storage directory

Innodb_undo_directory=/data2/

# rollback segment 128k

Innodb_undo_logs=128

# specify multiple undolog files

Innodb_undo_tablespaces=4

2. Undo log is split from the ibdata1 in the shared tablespace. When installing MySQL, you need to specify it in my.cnf.

If the database is started and re-specified, an error will be reported.

3. Parameters

If the innodb_undo_log_truncate parameter is set to 1, the online collection of undo log files is enabled, and dynamic settings are supported.

The innodb_undo_tablespaces parameter must be greater than or equal to 2, that is, when you recycle a undolog log, make sure that another undo is available.

The number of innodb_undo_logs undo rollback segments, at least 35 or more. Default is 128.

Innodb_max_undo_log_size (default 1GB) when the threshold is exceeded, the truncate recycling action is triggered, and the space is reduced to 10MB after truncate.

Innodb_purge_rseg_truncate_frequency controls how often the undo log is recycled. The undo log space does not contract until its rollback segment is released and wants to increase the release.

The frequency of rollback, you need to lower the set value of innodb_purge_rseg_truncate_frequency.

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.