(mysql.info) innodb-insert-buffering

Info Catalog

(mysql.info) innodb-physical-structure

(mysql.info) table-and-index

(mysql.info) innodb-adaptive-hash

 
 14.2.13.2 Insert Buffering
 ..........................
 
 It is a common situation in database applications that the primary key
 is a unique identifier and new rows are inserted in the ascending order
 of the primary key. Thus, the insertions to the clustered index do not
 require random reads from a disk.
 
 On the other hand, secondary indexes are usually non-unique, and
 insertions into secondary indexes happen in a relatively random order.
 This would cause a lot of random disk I/O operations without a special
 mechanism used in `InnoDB'.
 
 If an index record should be inserted to a non-unique secondary index,
 `InnoDB' checks whether the secondary index page is in the buffer pool.
 If that is the case, `InnoDB' does the insertion directly to the index
 page. If the index page is not found in the buffer pool, `InnoDB'
 inserts the record to a special insert buffer structure. The insert
 buffer is kept so small that it fits entirely in the buffer pool, and
 insertions can be done very fast.
 
 Periodically, the insert buffer is merged into the secondary index
 trees in the database. Often it is possible to merge several insertions
 to the same page of the index tree, saving disk I/O operations. It has
 been measured that the insert buffer can speed up insertions into a
 table up to 15 times.
 
 The insert buffer merging may continue to happen _after_ the inserting
 transaction has been committed. In fact, it may continue to happen
 after a server shutdown and restart (see  forcing-recovery).
 
 The insert buffer merging may take many hours, when many secondary
 indexes must be updated, and many rows have been inserted. During this
 time, disk I/O will be increased, which can cause significant slowdown
 on disk-bound queries. Another significant background I/O operation is
 the purge thread (see  innodb-multi-versioning).
 

Info Catalog

(mysql.info) innodb-physical-structure

(mysql.info) table-and-index

(mysql.info) innodb-adaptive-hash