Mysql InnoDB Architecture

InnoDB Architecture

• The InnoDB storage engine primarily includes memory pools and background threads.

• Memory pool: Multiple memory blocks make up a pool of memory, mainly maintain the internal data of the process/thread, cache the disk data, modify the memory before modifying the file, redo log

• Background thread: Flush the amount in the memory pool

Memory Buffer Pool

• InnoDB data is stored on the disk as a page, so memory is used as the cached page data.

• When reading page data, the page data on the disk is "FIX" to the buffer pool, and the next read can be read directly from the buffer pool.

• When the data is modified, the page data in the buffer pool is modified and then flushed to disk, not refreshed every time, but flushed to disk by the checkpoint mechanism.

• Data page types: index pages, data pages, undo pages, insert buffers (insert buffer), adaptive hash indexes, lock information, data dictionary information, and more

• The cache pool is managed by the LRU algorithm.

LRU, free list, Flush list

• Common LRU: Most frequently in the front of the list, at least at the end of the end, releasing the page at the end of the list.

• Innodb LRU: The midpoint location is added to the LRU queue, and the default value of 5/8 indicates that the newly read page is added to the 5/8 position of the list. Midpoint after the list becomes an old table, formerly known as the new table. That is, the end of the list is the old table at the end of table 37%, and the rest is the new table. The new table holds the active data.

• Free list: The LRU table is empty when the database is started, and the pages are stored in the list. Gets from the table when it is needed.

• The Flush list manages the pages that have been modified in the cache.

• UNZIP_LRU, the compressed page size is 1, 2, 4, 8KB, which is still part of the LRU management. UNZIP_LRU is managed separately for different size pages and uses the partner algorithm to allocate memory.

Redo Log Buffer

Redo log is written to the buffer first and then flushed to disk (1s/times) at a certain frequency, and the default is 8M. It brushes to disk mainly in a few cases:

1. Master thread executes once per second.

2. When things are submitted.

3. Redo log buffer has less than 1/2 remaining space.

Additional Pool of memory

Memory allocations to some of the data structures themselves are allocated from additional memory pools.

Threading Master Thread

Responsible for asynchronously flushing data from the cache pool to disk, including dirty pages. Merge Insert buffer, Undo page recycle, and so on.

IO Thread

InnoDB uses AIO to handle write requests in large numbers, and IO thread mainly handles callbacks for these requests, including write, read, insert buffer, and log IO thread.

Purge Thread

Primarily used to reclaim the undo log,innodb1.1 before the master thread is responsible.

Page Cleaner Thread

Clears the undo log of the submitted thing.

Checkpoint

A transactional database typically uses the write Ahead log policy to write redo log and then modify the in-memory page when the object commits. When the database is down, the modified data that has not yet been written to the disk can be recovered by redo log. The checkpoint function is to ensure that all modified pages before that point have been flushed to disk, and that the previous redo log is not needed to recover the data.

Sharp Checkpoint

Occurs when the database shuts down, and all dirty pages are written to disk, which is not normally used by the database runtime.

Fuzzy Checkpoint

Refreshes only partially partially dirty pages.

1. The Master thread Checkpoint:master thread asynchronously refreshes a certain percentage of dirty pages at a certain frequency.

2. Flush_lru_list Checkpoint: In order to ensure that there is a certain number of free pages in the LRU, the page Clear thread will remove the tail end of the LRU and refresh if there are dirty pages.

3. Async/sync flush Checkpoint: To ensure that the redo log is used (overwritten), the dirty pages that are not available in the redo file need to be flushed to disk.

4. Dirty page too much Checkpoint: too many dirty pages.

Master thread working mode InnoDB 1.2.x ago

Mainly includes the main loop, background loop, flush loop, and suspend loop. The parameters can be configured.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21st 22 23 24 25 26 27 28 29

while(true){     //差不多1s一次     for(inti in0..9){         刷新日志缓存到磁盘         //1s内的统计值         ifIO < 5             合并插入缓存         if脏页比例 > 预定值             刷新部分脏页（不超过100）         if没有用户活动             进入background loop{                 删除无用undo页                 合并20个插入缓冲                 可能跳到flush loop{                     可能跳到suspend loop                 }                 跳回主loop             }         sleep 1s;     }     //差不多10s一次     ifIO < 200 //10s内         刷新100个脏页到磁盘     合并最多5个插入缓冲     刷新日志缓冲     删除无用undo     刷新100或10个脏页 }

　　

Innodb 1.2.x

The dirty page refresh feature in Master thread is performed entirely by page Cleaner thread.

+ View Code

　　

Nnodb Key Features insert buffering

• When inserting data requires updating a nonclustered index, multiple random IO is required each time it is updated, so write these values to the buffer to merge the same page to improve IO performance.

• When you insert a nonclustered index, you first determine whether the index page is in the buffer pool, and then insert it directly. Otherwise, writes to the insert Buffer object.

• Condition: Two-level index, index cannot be unique (because if unique, you must ensure uniqueness, you have to check all index pages, or random io)

• Change buffer: Includes insert buffer, delete buffer, Purge buffer,update operation including marking the record as deleted and actually deleting the record two procedures, corresponding to the latter two buffer.

• Insert buffer inside is a B + tree

• Merge Insert Buffer Three cases:

1. The corresponding index page is read into the buffer pool.

2. The corresponding index page has less than 1/32 free space, forcing the merge.

3. The merge Insert buffer in the Master thread.

Write two times

When the dirty page is flushed to disk, if a page has not finished writing down, at this time the page data is chaotic cannot be restored through redo. InnoDB provides the doublewrite mechanism, which refreshes the dirty page steps as follows:

1 2 3

1. 先将脏页数据复制到doublewrite buffer中（2MB内存） 2. 将doublewrite buffer分两次，每次1MB写入到doublewrite磁盘（2MB）中。 3. 马上同步脏页数据到磁盘。对于数据混乱的页则可以从doublewrite中读取到，该页写到共享表空间。

　　

Adaptive Hash Index

The InnoDB storage engine monitors the lookup of indexes on a table, and if it observes that a hash index can lead to an increase in speed, a hash index is established, so it is called adaptive (adaptive). The adaptive Hash index is constructed from the B + Tree of the buffer pool and is therefore fast to build. And without having to hash the entire table, the InnoDB storage engine automatically hashes the pages based on the frequency and pattern of access.

Asynchronous IO

The asynchronous IO is provided in Linux and Windows, which can be used to make the sequential IO of a continuous page with a sequential IO operation.

Refresh adjacency Page

Determines whether adjacent pages are dirty pages when the page is refreshed.

Mysql InnoDB Architecture