MySQL InnoDB Recovery Time Problems

In MySQL InnoDB, there is still a long time-consuming problem after the recovery fault. The original reasons are as follows:

When a fault occurs and the service is restarted, the recovery operation is automatically completed to restore the database to the previous normal state. The recovery process completes two steps. Step 1: Check the redo log and redo all the previously completed and committed transactions. Step 2: cancel all unfinished transactions in the UNDO log. So why does the restoration process become so long after only two steps are completed? Before InnoDB improved the recovery speed, two improvement requests were put forward in the MySQL bug list: Bug #29847 and bug #49535.

"Folk approaches"-Governance

Method 1: Reduce the size of innodb_buffer_pool_size before restarting mysqld, and comment out innodb_flush_method = o_direct temporarily, which will shorten the fault recovery time.

Method 2: At the beginning, set the innodb_log_file_size parameter in my. CNF to a smaller value. This option has a direct relationship with the length of recovery time, but too small will also affect the performance.

"Professional solution"-improvementCode

Bug #49535 mentioned that when redo logs are redone during restoration, checking the available memory will consume more than 90% of the CPU. When the redo log is restored, a space will be opened in the buffer pool to read the redo log from the disk to the memory and put it in a hash table. As the redo log reading increases, this hash table will increase constantly. To ensure that the space does not exceed the buffer pool size, we need to traverse the hash table to obtain the size of each redo log read, obviously, it is inefficient and resource-consuming. The solution is to add a header field to the hash table structure to separately record the total size.

bug #29847 is caused by a large flush list. After each log is executed, it is inserted into a list called the flush list, which is the dirty page list we call. Normally, it is completed with a new one, then the new and new records will be placed in front of the List, and when recovery occurs, each and new records will be placed in the original position in the previous LRU order. At the same time, unfortunately, this flush list is an old linked list structure, and the traversal of each insert is painful. You know! The longer the flush list changes, the longer it will take. Therefore, we have mentioned that reducing the innodb_log_file_size can effectively shorten the recovery time. In fact, it is to reduce the flush list size. The solution is to adopt a data structure called the Red-black tree, which I have not understood yet: the problem has not been restored for too long since pluin 1.0.7, to improve the performance, we can increase the redo log settings as much as possible. InnoDB also ensures that there will be no extra-long recovery wait.