A brief discussion on innodb's index page structure, insert buffer, and adaptive hash index

Physical Structure of an InnoDB Index

All InnoDB indexes are Btree indexes, index records are stored on leaves, and the default index page size is 16K. When a new record is inserted, InnoDB will try to leave 1/16 of the free page size for future insert and update operations.

If the index records are inserted in exactly the order of the index record size, then the index will also fill up 15/16 of the entire page size. If the insertion order is completely random, then the index page is basically filled up to 1/2 to 15/16 self-built. If the fill factor is less than 1/2, InnoDB will try to rebuild the b-tree.

After MySQL 5.6, you can use the innodb_page_size parameter to set the size of each index page in the current instance. Once set, it cannot be changed back. The recommended configuration is generally 16K, 8K or 4K. In addition, if a MySQL instance is set to innodb_page_size A that is different from the default value, then you will not be able to use files on other instances with a different value from A (for example, to do a physical backup and restore)

Insert Buffering

Database applications usually insert data in the order of the primary key. In this case, because the order of the clustered index is exactly the same as the order of the primary key values, the insert operation will reduce a lot of random IO.

On the other hand, secondary indexes are usually not unique, so data is inserted into the secondary index in a relatively random order. Similarly, when delete and update operations affect data pages, they involve changes to the index, which are not adjacent to each other on the secondary index. This results in a lot of random IO.

When inserting a record or deleting a record from a non-unique secondary index, InnoDB first checks whether the secondary index page is in the buffer pool. If it is in the buffer pool, InnoDB will modify the index page directly in memory. If the index is not in the buffer pool either, InnoDB will record the modification in the insert buffer. The insert buffer is usually small, so it can be kept entirely in the buffer pool and updated very frequently. This modification process is called change buffering (usually, it only affects insert operations, so it is also called insert buffering, and the data structure is the insert buffer)

Disk I/O for Flushing the Insert Buffer

So how does insert buffering reduce random IO? Every once in a while, the insert buffer will merge the secondary non-unique indexes in the insert buffer. Normally, it merges N modifications into the index page of the same btree index, thus saving a lot of IO operations. After testing, insertbuffer can increase the insertion speed by 15 times.

After the transaction is committed, the insert buffer may still be merging writes. Therefore, if the DB is restarted abnormally, during the reovery phase, when a large number of secondary indexes need to be updated or inserted, the insert buffer may take a long time, even several hours. During this phase, disk IO will increase, which will cause a significant performance degradation for disk-bound queries.

Adaptive Hash Indexes

Adaptive Hash Index (AHI) makes InnoDB look more like an in-memory database when the buffer pool has enough memory and certain workloads, without sacrificing any transaction features and stability. This feature is controlled by the parameter innodb_adaptive_hash_index, a dynamic parameter. The default value is on, which means that adaptive hash index is turned on. After turning off AHI, the built-in hash table will be cleared immediately, and normal operations can still continue, but directly accessing the B-TREE index. The hash table will be rebuilt after re-enabling AHI.

By observing the search pattern, MySQL will use the prefix of the index key to create a hash index. This prefix can be of any length, and it may be just some values ​​on the B-tree, not the entire b-tree. The hash index is detected and a hash index is created on the frequently accessed index pages.

If a table is mostly in the buffer pool, creating a hash index can speed up equality queries by converting the btree index value into a sorted pointer. Innodb has this mechanism that can monitor the search status of the index. If it notices that some queries can be optimized by creating hash indexes, it will automatically create them, so it is "adaptive".

In some workloads, the performance improvement gained by hash index lookups outweighs the additional overhead of monitoring index searches and maintaining the hash table structure. However, sometimes, under high load conditions, the read/write locks added to the adaptive hash index can also cause competition, such as high-concurrency join operations. The Like operator and the % wildcard character also do not apply to AHI. If your workload is not suitable for AHI, it is recommended to turn it off to avoid unnecessary performance overhead. Because it is difficult to predict whether AHI is appropriate in a specific situation within MySQL, it is recommended to perform a stress test on an actual workload (with and without AHI). In 5.6 and later versions, it will be considered that more and more workloads are best to disable adaptive hash indexing, although it is currently enabled by default.

The creation of a hash index is often based on an existing b-tree. InnoDB can create a hash index by observing the search situation of the b-tree and establishing a b-tree index prefix of any length. A hash index can be partial, including only the most frequently accessed pages of the b-tree index.

You can decide whether to use adaptive hash indexes by observing the SEMAPHORES section in the show engine innodb status results. If you see many threads waiting on the rw-latch created in the btr0sea.c file, it is recommended to turn off the adaptive hash index. The following is a screenshot of a case I have encountered. It is a typical case of AHI contention in high concurrency mode. AHI needs to be turned off.

The above brief discussion on InnoDB's index page structure, insert buffer, and adaptive hash index is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.