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

This article mainly explains "how to use Innodb independent undo tablespace in MySQL5.6". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to use Innodb independent undo tablespace in MySQL5.6".

1. Use

There are several parameters to control this behavior

# innodb_undo_tablespaces

Used to set the number of undo tablespaces created, which can no longer be changed after initialization during Install db

The default value is 0, which means that the tablespace of undo is not set independently and recorded in ibdata by default. Otherwise, so many undo files are created in the undo directory. For example, if the value is set to 16, a undo tablespace file named undo001~undo016 will be created, and the default size of each file is 10m.

Modifying this value may cause Innodb to fail to complete initialization

# innodb_undo_logs

Used to indicate the number of rollback segments (named innodb_rollback_segments in previous versions). 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.

The default is 128 rollback segments

# innodb_undo_directory

When a separate undo tablespace is turned on, specify the directory where the undo files are stored

If we want to transfer the location of the undo file, we just need to modify the configuration and copy the undo file over.

two。 Related code

# when innodb starts (innobase_start_or_create_for_mysql), the undo tablespace is initialized. For more information, please see function srv_undo_tablespaces_init.

-> if you create a new instance, the undo log file will be created. The space id of the undo tablespace starts at 1. The default initialization size is 10m, which is controlled by the macro SRV _ UNDO_TABLESPACE_SIZE_IN_PAGES.

-> read the space id (trx_rseg_get_n_undo_tablespaces) of all undo table spaces of the current instance

First read the file header of the transaction system from ibdata, and then find the corresponding space id and page no (trx_sysf_rseg_get_space,trx_sysf_rseg_get_page_no) of the rollback segment from the recorded rollback segment information, and sort it according to space id and return it.

-> Open the undo file (srv_undo_tablespace_open) according to the space id read in the previous step. If it does not exist, it indicates that the startup failed.

Therefore, the undo file is also an important file similar to ibdata, which cannot be deleted at present. So don't try to delete the undo file to free up space -!

It can be tolerated that the number of table space defined is less than the number of existing undo files (but all undo files will still be opened), otherwise error initialization failure will be reported.

# initialization of undo rollback segment (trx_sys_create_rsegs)

If it is a normal shutdown restart and the number of rollback segments set is greater than the number of rollback segments already in use (trx_sysf_rseg_find_free), a new rollback segment (trx_rseg_create) will be created.

This always initializes the rollback segment from the first undolog tablespace, which seems to be problematic. In extreme cases, if I restart the incremental innodb_undo_logs every time, does it mean that all undo rollback segments will be written to the first undo tablespace?

After initialization, copy the number of available undo rollback segments to srv_available_undo_logs, which can be viewed through show status:

Root@performance_schema 12:16:18 > show status like 'Innodb_available_undo_logs'

+-+

| | Variable_name | Value |

+-+

| | Innodb_available_undo_logs | 128 | |

+-+

1 row in set (0.00 sec)

After startup, innodb_undo_logs can be adjusted dynamically, but the maximum cannot exceed Innodb_available_undo_logs

# when a non-read-only transaction is started, it will be assigned a rollback segment (trx_assign_rseg_low). Dynamically adjusting innodb_undo_logs can limit the range of allocated rollback segments

TODO

When there are long-running transactions, it may cause the purge operation to not have time to reclaim the undo space, thus causing the undo space to expand sharply; in theory, if you do a clean shutdown, you should be able to safely delete these undo files and reinitialize them. Perhaps a future version of MySQL may implement this feature, which is necessary for some services (such as cloud computing providers that charge for disk space)

Thank you for your reading, the above is the content of "how to use Innodb independent undo tablespace in MySQL5.6". After the study of this article, I believe you have a deeper understanding of how to use Innodb independent undo tablespace in MySQL5.6, 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.