Key features of InnoDB - insert cache, write twice, adaptive hash index details

The key features of the InnoDB storage engine include insert buffer , double write, and adaptive hash index. These features bring better performance and higher reliability to the InnoDB storage engine.

Insert Buffer

Insert buffering is the most exciting of the key features of the InnoDB storage engine. However, the name might lead you to think that the insert buffer is part of the buffer pool. In fact, it is not the case. Although it is good to have Insert Buffer information in the InnoDB buffer pool, Insert Buffer is also a component of the physical page like the data page.

The primary key is a unique identifier for a row. Row records are inserted in the order of increasing primary keys in the application. Therefore, inserts into a clustered index are generally sequential and do not require random reads from disk.

For example, we define a table according to the following SQL: create table t (id int auto_increment, name varchar (30), primary key (id));

The id column is self-increasing, which means that when an insert operation is performed, the id column will automatically increase, and the row records in the page are stored in the order in which the id is executed. Normally, there is no need to randomly read another page to perform the storage of records. Therefore, in such cases, the insert operation can generally be completed quickly. However, it is impossible to have only one clustered index on each table. In more cases, there are multiple non-clustered secondary indexes on a table . For example, we also need to search according to the name field, and the name field is not unique.

The table is defined by the following SQL statement: create table t (id int auto_increment, name varchar(30), primary key(id), key(name));

This creates a non-clustered and non-unique index . When performing an insert operation, the data pages are still stored in the order of execution of the primary key id , but for non-clustered indexes, the insertion of leaf nodes is no longer sequential. At this time, discrete access to non-clustered index pages is required, and the insertion performance becomes lower here. However, this is not a fault with the index on the name field, because the characteristics of the B+ tree determine the discreteness of non-clustered index insertion.

The InnoDB storage engine has innovatively designed the insert buffer . For non-clustered index insert or update operations, instead of directly inserting into the index page each time, it first determines whether the inserted non-clustered index page is in the buffer pool . If it is there, it is inserted directly; if not, it is first placed in an insert buffer, as if deceiving the database that this non-clustered index has been inserted into the leaf node, and then the merge operation of the insert buffer and the non-clustered index page child nodes is performed at a certain frequency . At this time, multiple insertions can usually be merged into one operation (because they are in one index page), which greatly improves the performance of insert and modify operations on non-clustered indexes.

The use of insert buffering requires the following two conditions to be met:

1. The index is a secondary index.

2. The index is not unique.

When the above two conditions are met, the InnoDB storage engine will use insert buffering, which can improve performance. However, consider a situation where the application performs a large number of insert and update operations, all of which involve non-unique non-clustered indexes. If the database crashes during this process, a large amount of insert buffers will not be merged into the actual non-clustered index. If this is the case, recovery may take a long time, even several hours in extreme cases to perform a merge restore operation.

Auxiliary indexes cannot be unique because we do not do a lookup on the index page when inserting it into the insert buffer. If we search for it, there will definitely be discrete reads, and inserting the buffer will lose its meaning.

View the insert buffer information:

show engine innodb status\G

seg size shows that the current insert buffer size is 2*16KB, free list len ​​represents the length of the free list, and size represents the number of merged record pages.

The following line is probably what we really care about, because it shows improved performance. inserts represents the number of inserted records, merged recs represents the number of merged pages, and merges represents the number of merges.

The ratio of merged recs:merges is about 3:1, which means that insert buffering reduces the IO requests for non-clustered index pages by about 3 times.

question:

Currently, there is a problem with insert buffering. In write-intensive situations, insert buffering will take up too much buffer pool memory. By default, it can take up a maximum of 1/2 of the buffer pool memory. Percona has released some patches to fix the problem of insert buffer taking up too much buffer pool memory. For details, please go to http://www.percona.com/percona-lab.html. Simply put, modifying IBUF_POOL_SIZE_PER_MAX_SIZE can control the size of the insert buffer. For example, if you change IBUF_POOL_SIZE_PER_MAX_SIZE to 3, only 1/3 of the buffer pool memory can be used at most.

Write twice

If insert buffering brings performance to the InnoDB storage engine, then double writes bring data reliability to the InnoDB storage engine. When the database crashes, it may happen that the database is writing a page, but only part of the page is written (for example, for a 16K page, only the first 4K of the page is written). We call this a partial page write. Before the InnoDB storage engine used the double write technology, there were cases where data was lost due to partial write failure.

Some people may think that if a write failure occurs, it can be recovered through the redo log. This is one way. But it must be clear that the redo log records the physical operations on the page , such as offset 800, writing the 'aaaa' record. If the page itself is corrupted, there is no point in redoing it. That is to say, before applying the redo log, we need a copy of the page . When a write failure occurs, the page is first restored through the copy of the page , and then redoed . This is doublewrite.

The architecture of the InnoDB storage engine doublewrite is shown in Figure 2-4.

Doublewrite consists of two parts: one is the doublewrite buffer in memory , which is 2MB in size; the other is 128 consecutive pages in the shared tablespace on the physical disk, that is, two extents, which are also 2MB in size (a copy of the page) . When the dirty page of the buffer pool is refreshed, it is not written directly to the disk. Instead , the dirty page is copied to the doublewrite buffer in the memory through the memcpy function. Then , it is written to the physical disk of the shared tablespace twice, 1 MB each time, through the doublewrite buffer . Then , the fsync function is called immediately to synchronize the disk to avoid problems caused by buffered writing. In this process, because the doublewrite pages are continuous, the process is written sequentially and the overhead is not very large. After completing the writing of the doublewrite page, the page in the doublewrite buffer is written to each tablespace file. At this time, the writing is discrete.

You can observe the doublewrite operation by running the following command: show global status like 'innodb_dblwr%'\G

Doublewrite writes a total of 18,445 pages, but the actual number of writes is 434, (42:1) which is basically in line with 64:1.

If you find that Innodb_dblwr_pages_written:Innodb_dblwr_writes is much less than 64:1 during peak hours, it means that the write pressure on your system is not very high.

If the operating system crashes in the process of writing the page to disk, during the recovery process, the InnoDB storage engine can find a copy of the changed page from the doublewrite in the shared tablespace , copy it to the tablespace file , and then apply the redo log . The following shows a case of recovery by doublewrite:

090924 11:36:32 mysqld restarted
090924 11:36:33 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the.ibd files……
InnoDB: Error: space id in fsp header 0, but in the page header 4294967295
InnoDB: Error: tablespace id 4294967295 in file ./test/t.ibd is not sensible
InnoDB: Error: tablespace id 0 in file ./test/t2.ibd is not sensible
090924 11:36:33 InnoDB: Operating system error number 40 in a file operation.
InnoDB: Error number 40 means 'Too many levels of symbolic links'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/operating-system-error-codes.html
InnoDB: File name./now/member
InnoDB: File operation call: 'stat'.
InnoDB: Error: os_file_readdir_next_file() returned -1 in
InnoDB: directory./now
InnoDB: Crash recovery may have failed for some.ibd files!
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...

The parameter skip_innodb_doublewrite can disable the double write function, which may cause the write failure problem mentioned above. However, if you have multiple slave servers and need to provide faster performance (such as RAID0 on the slaves), perhaps enabling this parameter is a solution. However, on the master server that needs to provide high data reliability , we should always ensure that the double write function is enabled.

Note: Some file systems provide a mechanism to prevent partial write failures, such as the ZFS file system. In this case, we should not enable doublewrite.

Adaptive Hash Index

Hash is a very fast search method, and the search time complexity is generally O(1). Commonly used in join operations, such as hash joins in SQL Server and Oracle. However, common databases such as SQL Server and Oracle do not support hash indexes. The default index type of MySQL's Heap storage engine is hash, while the InnoDB storage engine proposes another implementation method, adaptive hash index.

The InnoDB storage engine monitors the search for indexes on the table . If it observes that creating a hash index can improve the speed, it will create a hash index, so it is called adaptive. The adaptive hash index is constructed from the B+ tree of the buffer pool , so it is built very quickly. And there is no need to create a hash index for the entire table. The InnoDB storage engine will automatically create hash indexes for certain pages based on the frequency and pattern of access .

According to the official documentation of InnoDB, after enabling the adaptive hash index, the reading and writing speeds can be increased by 2 times; for the connection operations of the auxiliary index, the performance can be increased by 5 times. The adaptive hash index is a very good optimization mode. Its design concept is database self-tuning, which means that the DBA does not need to adjust the database.

View the current usage of the adaptive hash index: show engine innodb status\G

Now you can see the usage information of the adaptive hash index, including the size of the adaptive hash index, usage, and the number of adaptive hash index searches per second. It is worth noting that hash indexes can only be used to search for equal values, such as select * from table where index_col='xxx', and cannot be used for other search types, such as range searches. Therefore, non-hash searches/s occur here. Use the hash searches:non-hash searches command to get a rough idea of ​​the efficiency of using hash indexes.

Since the adaptive hash index is controlled by the InnoDB storage engine, the information here is for reference only. However, we can disable or enable this feature through the parameter innodb_adaptive_hash_index, which is enabled by default.

The above article on the key features of InnoDB - insert cache, write twice, 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.