The aim of the InnoDB change buffer is to avoid delays when a leaf page of a secondary index is not present in the buffer pool, and a record needs to be inserted, delete-marked, or purged. Instead of reading the page into the buffer pool for making such a modification, we may insert a record to the change buffer (a special index tree in the InnoDB system tablespace). The buffered changes are guaranteed to be merged if the index page actually needs to be read later.
The change buffer could be useful when the database is stored on a rotational medium (hard disk) where random seeks are slower than sequential reads or writes.
Obviously, the change buffer will cause write amplification, due to potentially large amount of metadata that is being written to the change buffer. We will have to write redo log records for modifying the change buffer tree as well as the user tablespace. Furthermore, in the user tablespace, we must maintain a change buffer bitmap page that uses 2 bits for estimating the amount of free space in pages, and 1 bit to specify whether buffered changes exist. This bitmap needs to be updated on every operation, which could reduce performance.
Even if the change buffer were free of bugs such as
MDEV-24449 (potentially causing the corruption of any page in the system tablespace) or MDEV-26977 (corruption of secondary indexes due to a currently unknown reason), it will make diagnosis of other data corruption harder.
In a recent case, the database of a customer crashed due to corruption that was detected in a page reorganize operation while applying an INSERT operation from the change buffer. It is unknown what caused that corruption in the first place. Because any read of a page may require a change buffer merge, also CHECK TABLE may cause a change buffer merge, and therefore crash in such a case. If CHECK TABLE had not invoked a change buffer merge, instead of crashing it should have reported something like the following (example taken from
Recent benchmarks attached to
MDEV-19514 show that the change buffer sometimes reduce performance, and in the best case seem to bring a few per cent improvement to throughput. However, such improvement could come with a price: If the buffered changes are never merged ( MDEV-19514, motivated by the reduction of random crashes and the removal of an innodb_force_recovery option that can inflict further corruption), then the InnoDB system tablespace can grow out of control ( MDEV-21952).
Because of all this, it is best to disable the change buffer by default.