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

This article mainly explains "the principle of mysql undo storage management". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the principle of mysql undo storage management".

I. undo storage management

The 1.innodb storage engine also uses a segment approach to manage undo.

Each rollback segment contains 1024 undo log segment, and the transaction applies for the undo page in each undo log segment.

Starting with 2.innodb1.2, you can make further settings for rollback segment with the following parameters:

Innodb_undo_directory # undo table space storage directory

Innodb_undo_logs # set the number of rollback segment

Innodb_undo_tablespaces # sets the number of rollback segment files that make up the file, so that it can be evenly distributed across multiple files

Innodb_undo_log_truncate # parameter is set to 1, that is, online collection (contraction) of undo log log files is enabled. Dynamic setting is supported.

Innodb_max_undo_log_size # when the undo tablespace exceeds this parameter setting, it will be marked as truncation, and a undo tablespace will be selected for truncation

Generally speaking, innodb_purge_rseg_truncate_frequency # undo tablespaces cannot be directly truncate. You need to release all rollback segments before truncate. Purge system releases rollback segments every 128times. You can use the parameter

3. The process of writing a transaction to undo log on the undo log segment allocation page also requires writing to the redo log, and when the transaction commits, innodb does the following two things:

-put undo log into the list for future purge operations

-determine whether the page where the undo log resides can be reused, and if so, assign it to the next transaction

II. Undo log format

1.insert undo log

Refers to the undo log generated in the insert operation, because the record of the insert operation is visible only to the transaction itself. Therefore, the undo log is deleted directly after the transaction is committed, and no purge operation is required.

2.update undo log

What is recorded is the undo log generated for the delete and update operations, which needs to provide a mvcc mechanism, so it cannot be deleted when the transaction commits. Put in the undo log linked list when submitting and wait for the purge thread to clear.

three。 View undo information

1. View the page where rollback segment is located

Select segment_id,space,page_no from information_schema.innodb_trx_rollback_segment

two。 Record the undo log information corresponding to the transaction

Select * from information_schema.INNODB_TRX_UNDO\ G

IV. Purge mechanism

Purge is used to finalize delete and update operations.

The global dynamic parameter innodb_purge_batch_size is used to set the number of undo page to be cleaned up for each purge operation. Default value is 300

The global dynamic parameter innodb_max_purge_lag is used to control the length of history list. If it is greater than this parameter, it will delay the operation of DML.

The global dynamic parameter innodb_max_purge_lag_delay, which is used to control the maximum delay time, in milliseconds, for DML operations on each row of data.

Thank you for your reading, these are the contents of "the principles of mysql undo Storage Management". After the study of this article, I believe you have a deeper understanding of the principles of mysql undo storage management, 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.