Change buffer and innodb_force_recovery=4

First, about Innodb_force_recovery

People familiar with MySQL must be familiar with Innodb_force_recovery this parameter, if the database crash, start again when InnoDB will restore the InnoDB table, restore success, the database starts normally, if the recovery fails, the database will not start. According to experience, innodb_force_recovery=4 This parameter is quite useful, recently used to restore a database, the steps on the Web are:

1. Increase in/ETC/MY.CNF:
[Mysqld]
Innodb_force_recovery = 4
2. Restart MySQL.
3, mysqldump export all the tables

Now that you have the DMP file, create a new MySQL environment to perform the import.

Take a look at mysql5.6 's official advice:

Http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html

4 (Srv_force_no_ibuf_merge)

Prevents insert buffer merge operations. If they would cause a crash,does not do them. Does not calculate table statistics. This value can permanently corrupt data files. After using the this value, is prepared to drop and recreate all secondary indexes. As of MySQL 5.6.15, sets InnoDB to Read-only.

As you can see here, the document says starting a database with this value can permanently destroy the data file, and it is recommended to delete and rebuild all two-level indexes, so get dmp and then go for a different data environment.

Ii. about Insert Buffer merge

To be exact, after MySQL 5.5, it should be called change buffer merge. First you need to know what is change buffer?

Let's take a look at mysql5.6 's official documentation:

Http://dev.mysql.com/doc/refman/5.6/en/innodb-insert-buffering.html

The following passage is a good explanation of what the change buffer is:

Similarly, deletes and updates may affect secondary index pages that is not adjacently located in an index tree. Merging cached changes at a later time when affected pages is read into the buffer pool by other operation s, avoids substantial random access I/O that would is required to read-in secondary index pages from disk.

Periodically, the purge operation that runs when the system is mostly idle, or during a slow shutdown, writes the U pdated index pages to disk.        The purge operation can write disk blocks for a series of index values more efficiently than if each value were Written to disk immediately.

As you can see from this passage, the core idea of change buffer is to reduce the frequency of random access to level two index pages.

Not every time the two-level index page is deleted or updated to read the corresponding page from disk to memory, but will be a little "wait" to see if other operations will also use this page, then you can "fuse" These dirty data together, and then periodically purge, write back to disk.

Here you can understand why when we use Innodb_force_recovery = 4 to recover the database, the official documentation recommends rebuilding all two-level indexes because there is a problem with the cache for the two-level index, and the disk cannot be synchronized to a consistent state.

Note:

Similar mechanism, in fact, in Oracle has already had, that is, when the transaction commits, must write log to disk, but the cache of dirty data block may not immediately write to the disk, if the data write disk to the transaction commit synchronization, then IO efficiency will be very poor.

This article is from the "Memory Fragments" blog, so be sure to keep this source http://weikle.blog.51cto.com/3324327/1683587

Change buffer and innodb_force_recovery=4