The fastest shortcut in the world is to keep your feet on the ground. Focus on the sharing place.

The purpose of the daily knowledge point series is to make a general summary of a certain knowledge point, which can be completed in one minute.

This is not a detailed interpretation of the original, only as a kind of introduction.

True understanding must be the knowledge you gain from your own research and exploration, joining the organization to take you forward and grow together.

Through the knowledge points of a few days ago, we know that in Innodb architecture, it mainly contains two large chunks of background thread and memory. Today, WE will talk about some knowledge points of memory in Innodb architecture. The architecture diagram is as follows:

Innodb memory

As usual, let’s look at the big picture:

At present, we can see that InnoDB has two main parts of memory:

  • The buffer pool
  • Redo log buffers

InnoDB storage engine is disk-based storage and manages records on a page basis.

It can therefore be thought of as a disk-based Database system.

In database systems, due to the gap between CPU speed and disk speed, disk-based database systems often use buffer pool technology to improve the overall performance of the database

The buffer pool

A buffer pool is simply an area of memory that uses memory speed to compensate for the impact of slow disk speeds on database performance.

Data page types cached in the buffer pool:

  • Index page
  • Data page
  • Undo pp.
  • Insert buffer
  • Adaptive Hash Index
  • Lock Info stored in InnoDB
  • Data dictionary information

Read data operation:

  • First place the pages read from disk in the buffer pool, a process called “fixing” the pages in the buffer pool

  • The next time you read the same page, first determine whether the page is in the buffer pool

  • If the page is in the buffer pool, the page is said to be hit in the buffer pool, and the page is read directly. Otherwise, the page on disk is read

Write data operation:

  • Pages in the buffer pool are first modified and then flushed to disk at a regular rate

  • The flush of a page from the buffer pool back to disk is not triggered every time a page is updated

  • Flush back to disk through a mechanism called Checkpoint

Configuration parameters:

  • Innodb_buffer_pool_instances, the default number of buffer pool instances is 1. InnoDB version 1.0.x is used, and each page is evenly allocated to different buffer pool instances based on the hash value. Reduce resource competition within the database and increase the concurrent processing capability of the database.
  • Innodb_buffer_pool_size, buffer pool size

Command parameters:

  • SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’

  • To view the number of buffer pool instances, run the SHOW VARIABLES LIKE ‘Innodb_buffer_pool_instances’ command.

Buffer pool management

A buffer pool is a large area of memory that holds various types of pages. In fact, such a large memory area is managed by the LRU algorithm, and the LRU algorithm here is also InnoDB to do some optimization of the traditional

LRU List

Traditional LRU:

  • The most frequently used pages are at the front of the LRU list
  • The least-used page is at the end of the LRU list
  • When the buffer pool cannot hold newly read pages, the pages at the end of the LRU list are released first

InnoDB modified LRU:

  • Midpoint location added to LRU list
  • The newly read page is the MIDpoint location placed into the LRU list
  • By default, this position is at 5/8 of the length of the LRU list. The midpoint position is controlled by the innodb_old_blocks_pct parameter
  • The innodb_old_blocks_time parameter was introduced to indicate how long the page must wait after reading the mid position before being added to the hot end of the LRU list

Free List

  • The LRU list is used to manage pages that have been read, but when the database is started, the LRU list is empty, that is, there are no pages. The pages are stored in the Free list.

  • When paging from the buffer pool is required, the Free page is first looked up from the Free list to see if there is a Free page available, and if so, the page is removed from the Free list and placed in the LRU list. Otherwise, according to the LRU algorithm, the page at the end of the LRU list is eliminated and the memory space is allocated to the new page

  • When a page is added from the old part of the LRU list to the new part, the operation is called Page Made Young, An operation that does not move a page from old to new due to innodb_old_blocks_time is called a page not made Young

  • You can run the SHOW ENGINE INNODBSTATUS command to view the usage and running status of the LRU list and Free list

Flush List

  • The LRU list is used to manage the availability of pages in the buffer pool, and the Flush list is used to manage flushing pages back to disk

  • When a page in the LRU list is modified, it is called a dirty page, meaning that the data of the page in the buffer pool and the page on disk are inconsistent.

  • The database uses the CHECKPOINT mechanism to Flush dirty pages back to disk, and the pages in the Flush list are the dirty pages.

  • Dirty pages exist in both the LRU list and the Flush list.

Pages in the buffer pool may also be allocated to adaptive hash indexes, Lock messages, InsertBuffer, and so on, which are not maintained by the LRU algorithm and therefore do not exist in the LRU list.

Redo log buffer

  • InnoDB storage engine first puts redo log information into this buffer and then flusher it to redo log files on a regular basis

  • The redo log buffer generally does not need to be large because it is typically flushed to the log file every second

  • This value is controlled by the innodb_log_BUFFer_SIZE configuration parameter, which defaults to 8MB

  • SHOW VARIABLES LIKE ‘innodb_log_buffer_size’

When does it flush to disk?

  • The Master Thread flushes the redo log buffer to the redo log file every second

  • The redo log buffer is flushed to the redo log file at each transaction commit

  • When the redo log buffer pool space is less than 1/2, the redo log buffer is flushed to the redo log file