INSERT, UPDATE and DELETE statements can be particularly heavy operations to perform, as all indexes need to be updated after each change. For this reason these changes are often buffered.
Pages are modified in the buffer pool, and not immediately on disk. When rows are deleted, a flag is set, thus rows are not immediately deleted on disk. Later the changes will be written to disk (''flushed'') by InnoDB background threads. Pages that have been modified in memory and not yet flushed are called dirty pages. The buffering of data changes is called Change Buffer.
Before MariaDB 5.5, only inserted rows could be buffered, so this buffer was called Insert Buffer. The old name still appears in several places, for example in the output of SHOW ENGINE INNODB STATUS.
The change buffer only contains changes to the indexes. Changes to UNIQUE indexes are primary keys cannot be buffered, because InnoDB has to read the disk to check for duplicate values.
The Change Buffer is an optimization because:
If the server crashes, usually the Change Buffer is not empty. However, changes are not lost because they are written to the transaction logs, so they can be applied at server restart.
The main server system variable here is innodb_change_buffering, which determines which form of change buffering, if any, to use.
The following settings are available:
Modifying the value of this variable only affects the buffering of new operations. The merging of already buffered changes is not affected.
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.