Shulou(Shulou.com)06/01 Report--

Innodb architecture

Innodb storage engine mainly includes memory pool and background threads.

Memory pool: multiple memory blocks form a memory pool, which mainly maintains the internal data of processes / threads and caches disk data. Modify memory and redo log before modifying files.

Background thread: refresh the amount in the memory pool

Memory buffer pool

The data of Innodb is stored on disk in the form of pages, so memory is used to cache page data.

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

When you modify the data, you first modify the page data in the buffer pool, and then flush it to disk, not every time, but through the Checkpoint mechanism.

Data page type: index page, data page, undo page, insert buffer (insert buffer), adaptive hash index, lock information, data dictionary information, etc.

The cache pool is managed by the LRU algorithm.

LRU 、 Free List 、 Flush List

Ordinary LRU: most frequently at the front of the list, at least at the end of the list, release the page at the end of the list first.

Innodb LRU: the midpoint position is added to the LRU queue. The default value is 5max 8, which means that newly read pages are added to the 5max 8 position of the list. After midpoint, the list becomes the old table, formerly known as the new table. That is, from the end of the list to the end of the table, 37% are old tables, and the rest are new tables. The new table holds active data.

Free List: the LRU table is empty when the database is started, and the pages are stored in Free List. Get it from this table when you need it.

Flush List manages modified pages in the cache.

Unzip_LRU, compressed page size is 1, 2, 4, 8KB, it is still managed by LRU. Unzip_LRU manages pages of different sizes separately and allocates memory using partner algorithm.

Redo log buffer

Redo log writes to the buffer first, and then flushes to disk at a certain frequency (1s/ times). The default is 8m. The following are the main situations when it is brushed to disk:

Master Thread executes once per second.

When things are submitted.

The remaining space of redo log buffer is less than 1 stroke 2.

Additional memory pool

Memory allocation for some data structures themselves is allocated from additional memory pools.

Thread Master Thread

Responsible for asynchronously flushing data from the cache pool to disk, including dirty pages. Merge insert cache (INSERT BUFFER), collection of UNDO pages, etc.

IO Thread

AIO is widely used in Innodb to handle write requests, while IO Thread mainly handles callbacks to these requests, including write, read, insert buffer, and log IO Thread.

Purge Thread

Master Thread is in charge before it is mainly used to recycle undo log,Innodb1.1.

Page Cleaner Thread

Clean up the UNDO log of submitted things.

Checkpoint

Transactional databases generally adopt the Write Ahead Log strategy. When a transaction is submitted, redo log is written first and then the page in memory is modified. When the database is down, the modified data that has not been written to disk can be recovered through redo log. The purpose of Checkpoint is to ensure that all modified pages prior to this point have been flushed to disk, and that the previous redo log is not needed to recover data.

Sharp Checkpoint

When the database is shut down, all dirty pages are written to disk and are generally not used when the database is running.

Fuzzy Checkpoint

Refresh only part of the dirty page.

Master Thread Checkpoint:Master Thread asynchronously has refreshed a certain percentage of dirty pages at a certain frequency.

Flush_LRU_LIST Checkpoint: in order to ensure that there are a certain number of free pages in LRU, Page Clear Thread will remove the tail pages in LRU and refresh them if there are dirty pages.

Async/Sync Flush Checkpoint: in order to ensure redo log recycling (overwriting), dirty pages that need to be flushed to disk that are not available in the redo file.

Dirty Page too much Checkpoint: there are too many dirty pages.

Master Thread works before Innodb 1.2.x

It mainly includes main loop, background loop, flush loop and suspend loop. The parameters can be configured.

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

twenty-seven

twenty-eight

twenty-nine

While (true) {

/ / almost once a second

For (int i in 0.. 9) {

Refresh log cache to disk

Statistics within / / 1s

If IO

Predetermined value

Refresh some dirty pages (no more than 100)

If has no user activity

Enter background loop {

Delete useless undo pages

Merge 20 insertion buffers

May jump to flush loop {

Maybe jump to suspend loop

}

Jump back to the primary loop

}

Sleep 1s

}

/ / almost once in 10 seconds

Within if IO < 200s / / 10s

Refresh 100 dirty pages to disk

Merge up to 5 insert buffers

Refresh the log buffer

Delete useless undo

Refresh 100 or 10 dirty pages

}

Innodb 1.2.x

The dirty page refresh function in Master Thread is performed entirely by Page Cleaner Thread.

+ View Code

Nnodb key feature insertion buffer

When inserting data requires updating a nonclustered index, multiple random IO is required if it is updated each time, so merging these values into the buffer for the same page improves IO performance.

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

Condition: secondary index, index cannot be unique (because if it is unique, uniqueness must be guaranteed, then all index pages have to be checked, or random IO)

Change Buffer: including Insert Buffer, Delete Buffer, and Purge Buffer,update operations, including marking the record as deleted and actually deleting the record, corresponding to the last two Buffer.

Inside the Insert Buffer is a B + tree.

There are three cases of Merge Insert Buffer:

The corresponding index page is read into the buffer pool.

If the free space of the corresponding index page is less than 1 prime 32, a merge is forced.

Merge insert buffer in Master Thread.

Write twice

When a dirty page is flushed to disk, if a page goes down before it is finished, the data of the page can no longer be recovered through redo. Innodb provides doublewrite mechanism. The steps to refresh dirty pages are as follows:

one

two

three

1. First copy the dirty page data to doublewrite buffer (2MB memory)

two。 Write the doublewrite buffer twice, each time the 1MB is written to the doublewrite disk (2MB).

3. Synchronize dirty page data to disk immediately. Pages with cluttered data can be read from doublewrite and written to shared tablespaces.

Adaptive hash indexing

The InnoDB storage engine monitors the lookup of indexes on the table, and if it is observed that the establishment of a hash index can lead to a speed increase, it builds a hash index, so it is called adaptive. The adaptive hash index is constructed by the B + tree of the buffer pool, so the speed of establishment is very fast. And there is no need to hash the entire table, the InnoDB storage engine automatically hashes certain pages based on the frequency and pattern of access.

Asynchronous IO

Asynchronous IO is provided in linux and windows, which can do the IO operation of merging consecutive pages to make random IO change order IO.

Refresh adjacency pages

When refreshing the page, determine whether the adjacent pages are also dirty pages.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.