The Ultimate Guide to High Availability with MariaDB

XtraDB/InnoDB Change Buffering

You are viewing an old version of this article. View the current version here.

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.

In versions 5.1, 5.2 and 5.3, 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:

  • A page can be modified several times in memory and be flushed to disk only once.
  • Dirty pages are flushed together, so the number of IO operations is lower.

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:

  • inserts
    • Only buffer insert operations
  • deletes
    • Only buffer delete operations
  • changes
    • Buffer both insert and delete operations
  • purges
    • Buffer the actual physical deletes that occur in the background
  • all
    • Buffer inserts, deletes and purges. This is the default setting from MariaDB 5.5.
  • none
    • Don't buffer any operations.

Modifying the value of this variable only affects the buffering of new operations. The merging of already buffered changes is not affected.

The innodb_change_buffer_max_size server system variable, introduced in MariaDB 10.0, determines the maximum size of the change buffer, expressed as a percentage of the buffer pool.

  • innodb_buffer_pool_pages_dirty: Number of dirty pages in the Change Buffer.
  • innodb_buffer_pool_bytes_dirty: Total size of the dirty pages, in bytes.
  • innodb_buffer_pool_wait_free: How many times InnoDB was forced to flush dirty pages to write new data, because the buffer pool had no more free pages.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.