On innodb characteristics in MySQL

Long time no update blog, last weekend to the netease Daniel Ginger Sir's book MySQL Technology insider InnoDB storage engine again, to the current work InnoDB characteristics have some new understanding, the following talk about their own books.

1. The architecture of InnoDB consists of a series of background threads, memory pools, and files that are similar to other db. An area is partitioned in memory, the buffer pool, which is used to temporarily store data pages that the user reads and writes. InnoDB on the buffer pool read and write data page, flush to disk and other operations also use the checkpoint mechanism, the LRU algorithm, which is consistent with the SQLSERVER,DB2 database design, There is no further elaboration. It is important to note that the midpoint control location of the specified LRU list in MySQL is controlled by the parameter innodb_old_blocks_pct, which determines how much of the newly read page will be inserted into the LRU trailer. Typically 37%.

2. Insert buffering: Insert buffering is a groundbreaking design for the InnoDB storage engine, primarily to address nonclustered index inserts or update operations, because nonclustered indexes have access to nonclustered index pages in a discrete way. This results in a decrease in the performance of the insert operation. This is because the properties of the nonclustered index itself are determined. So InnoDB introduces the feature of insert buffering, and instead of inserting or updating a nonclustered index directly into the index page, you first determine whether the inserted nonclustered index page is in the buffer pool, or if it is inserted directly , and if not, put it in an insert buffer object first. Then the merge operation of insert buffer and secondary index page child node is then performed at a certain frequency and condition, which is usually able to merge multiple inserts into one operation (because in an index page), This greatly improves the performance for nonclustered index insertions.

The use of the insert buffer satisfies two conditions: 1. The index is a secondary index; 2. The index is not unique. Because when you insert a buffer, the database does not look up the index page to determine the uniqueness of the inserted record. If you go to look for sure there will be discrete reads, which causes the insert Buffer lost its meaning.

The current problem with insert buffering is that the insertion buffer consumes too much buffer pool memory in case of write-intensive situations.

At present, there is no insertion buffer on the Db2,sqlserver feature.

3.double-write: Two writes mainly to the InnoDB data engine is the reliability of the data page. Under other databases, when a data page is in the process of writing a disaster, such as a power problem that results in an outage, a write failure occurs for that data page. Of course we can recover by redo log, However, because redo logging itself is the physical operation of the page, such as offsets, write records, and so on. When the physical page itself is damaged during the outage, recovery from redo logs is not available. In the face of this situation, DB2, SQL Server repairs the data page through internal command Repairdb or other repair tools. And the MySQL InnoDB two write feature guarantees that the data is written to the shared tablespace before writing to the disk, and then the disk is synchronized through the function. This is equivalent to creating a copy of the data page.

4. Adaptive Hash Index: This is another important feature of MySQL InnoDB. The InnoDB storage engine monitors queries on each index page on the table, and if it observes that a hash index can lead to a speed increase, it establishes its own hash index. InnoDB storage causes a hash index to be automatically established for some hotspot pages automatically based on the frequency and mode of access. This is what SQL Server and DB2 don't have.

On innodb characteristics in MySQL