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

This article focuses on "what is the principle of Mysql 5.5crash recovery". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what is the principle of Mysql 5.5 crash recovery"?

If you have a lot of memory, then while enjoying the performance, you also enjoy the pain of waiting for a long time to recover when you CRASH.

This situation will change after MYSQL 5.5 InnoDB Plugin 1.0.7.

First of all, let's understand the principle of crash recovery:

Crash recovery (Crash recovery) can be seen as two stages.

The first stage is called scan redo log (Redo scan), when InnoDB reads the Redo Log on disk and stores it in a Hash table.

The second phase applies these Redo Log and applies these logs to Data Page.

In the process of reading Redo Log into Buffer Pool's Hash table, InnoDB allocates 16K Block to store these Redo when needed.

To ensure that there is enough space left in the Buffer Pool to store the data page (Data Page), so that if the Redo is large, the Block heap will be large as well.

Here every time InnoDB reads a Redo, it traverses the previous Heap to make sure it doesn't take up too much space.

So, if the InnoDB has a large Buffer Pool and a lot of Dirty Page before the crash, the Heap may be very large, and each traversal will greatly reduce the efficiency of recovery.

InnoDB solves the above problem by adding a header to the Heap to store this information.

Another time-consuming operation during the recovery process occurs during the application of the Redo.

Every Data Page with Redo Log will be put into a linked list called Flush_list to wait for Flush.

And the Data Page in this linked list is strictly installed in the LSN order.

This is always fine when InnoDB is working properly, because the LSN value of Data Page always increases monotonously.

But in the recovery phase, InnoDB needs to constantly scan the entire linked list to determine the location of a Data Page.

During the recovery phase, InnoDB stores these Page through an auxiliary red-black tree (Red-Black Tree) to avoid simple scanning.

At the end of the recovery phase, the red-black tree will be deleted and the Flush_list will remain its original structure.

Test result

In InnoDB Blog, a test is given:

It took Plugin1.0.6 7 hours and 38 minutes to recover.

It took only 13 minutes and 56 seconds to use Plugin1.0.7, which is 32 times faster.

The scan Redo phase is 16 times faster and the application log phase is 35 times faster.

The following is the original text:

Configuration parameters:

-innodb-buffer-pool-size=18g

-innodb-log-file-size=2047m

-innodb-adaptive-flushing=0

-innodb-io-capacity=100

The latter two are used to throttle flushing in order to maximize the number of dirty pages.

It took only about 20 min of running a workload to arrive to the test dataset, including cache prewarming.

So at time of crash we had:

Modified db pages 1007907

Redo bytes: 3050455773

And the recovery times were:

Plugin 1.0.7 (also Plugin 1.1): 1m52s scan, 12m04s apply, total 13m56s

Plugin 1.0.6: 31m39s scan, 7h06m21s apply, total 7h48m

1.0.7 (and Plugin 1.1) is better 16.95x on scan, 35.33x on apply, 32.87x overall

At this point, I believe you have a deeper understanding of "what is the principle of Mysql 5.5crash recovery". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.