Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27734

Set innodb_change_buffering=none by default

    XMLWordPrintable

Details

    Description

      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 MDEV-19916):

      2019-12-09  8:13:03 4 [ERROR] InnoDB: Record overlaps another: 107+12
      		 
      2019-12-09  8:13:03 4 [ERROR] InnoDB: Summed data size 14, returned by func 13

      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.

      Attachments

        Issue Links

          blocks

          Task - A task that needs to be done. MDEV-27735 Deprecate the parameter innodb_change_buffering

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Task - A task that needs to be done. MDEV-29694 Remove the InnoDB change buffer

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28148 InnoDB slave crash / signal 6

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-28542 Useless INSERT BUFFER AND ADAPTIVE HASH INDEX output in SHOW ENGINE INNODB STATUS

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28975 AWS RDS mariadb 10.5.12 crashes upon OS update

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29082 InnoDB: Failing assertion: !cursor->index->is_committed()

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29590 Deadlock between ibuf_insert_to_index_page_low() and ibuf_delete_for_discarded_space()

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29905 Change buffer operations fail to check for log file overflow

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30009 InnoDB shutdown hangs when the change buffer is corrupted

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30134 buf_page_t::unfix(): Assertion `!((f ^ (f - 1)) & LRU_MASK)' failed

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31088 Server freeze due to innodb_change_buffering and innodb_file_per_table=0

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33431 Latching order violation reported fil_system.sys_space.latch and ibuf_pessimistic_insert_mutex

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34879 InnoDB fails to merge the change buffer to ROW_FORMAT=COMPRESSED tables

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35628 Lock order inversion between ibuf_mutex and ibuf.index->lock

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-9663 InnoDB assertion failure: *cursor->index->name == TEMP_INDEX_PREFIX, or !cursor->index->is_committed()

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-19514 Defer change buffer merge until pages are requested

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21952 ibdata1 file size growing in MariaDB

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22739 !cursor->index->is_committed() in row0ins.cc after update to 10.4.13 from 10.3.21

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22986 [ERROR] InnoDB: Record in index was not found on update in mysqld.log on slave

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24449 Corruption of system tablespace or last recovered page

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25796 Failing assertion: !cursor->index->is_committed() in row0ins.cc

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26917 InnoDB: Clustered record for sec rec not found index

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26977 mariadb 10.5.12 reboot loop in AWS | [ERROR] InnoDB: Insert buffer insert fails | Corruption of an index tree

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27411 Index Corruption in mariadb 10.5.11 slave

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27765 MariaDB stopped to work randomly - misery started at "Unable to find a record to delete-mark"

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27987 "[ERROR] InnoDB: Unable to find a record to delete-mark" shortly after upgrading slave from 10.5.10 to 10.6.5

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-28349 Provide "crash safe" options for CHECK TABLE and ALTER TABLE ... CHECK PARTITION ...

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29126 DB is crashing repeatedly with error message "InnoDB: Rec offset 99, cur1 offset 1723, cur2 offset 16095"

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30819 InnoDB fails to start up after downgrading from MariaDB 11.0

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31621 Remove ibuf_read_merge_pages() call from ibuf_insert_low()

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35553 InnoDB: ibuf cursor restoration fails error in MariaDB 10.6.20

          • Major - Major loss of function.
          • Needs Feedback
          mentioned in

          Page Loading...

          Activity

            People

              marko Marko Mäkelä
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.