Memory management mechanism of INNODB storage engine

The InnoDB buffer pool is managed by the LRU algorithm to manage the page. Frequently used page is placed in the front of the LRU list, the least used page at the end of the LRU list, when the buffer pool is full, priority to retire the end of the page.

The LRU structure in InnoDB

The default size of page in the InnoDB engine is 16kb,innodb some optimizations are made to the traditional LRU algorithm. As shown in the following:

The LRU list is divided into two parts, the previous part of the midpoint point is called the new list, and the later part is called the old list, and the pages in the new list are the most live hot data. The location of the midpoint is set by parameters innodb_old_blocks_pct .

The innodb_old_blocks_pct default value of the parameter is 37, which means that the newly read page is inserted into the 37% (approximately 3/8) to the left of the LRU list.

mysql> show variables like ‘innodb_old_blocks%‘;+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| innodb_old_blocks_pct  | 37    || innodb_old_blocks_time | 0     |+------------------------+-------+

Why not use the traditional LRU algorithm?

If you put the page you read directly to the LRU header, some SQL operations may cause the page in the buffer pool to be brushed out. Common such operations are scan operations for indexes or data. Such operations access many of the pages in the table, which are often needed only in this query and are not active data. If you put it into the LRU header, it is very likely that the real hotspot data will be removed from the LRU list, and the InnoDB will need to re-access the disk reads the next time, so that performance is poor.

At the same time, InnoDB further introduces another parameter to manage the LRU list, which is innodb_old_blocks_time used to indicate how long it will take to wait until the page is placed in the midpoint position to be added to the new end of the LRU list as the hotspot data.

Changes to the page in LRU

When the database starts, the LRU list is empty, that is, there are no page, and the page is stored in the free list. When paging is required from the buffer pool, first look for free pages available from free list, and if any, remove the page from the. Add to the LRU. Otherwise, according to the LRU algorithm, the page at the end of the LRU list is eliminated to be assigned to the new page.

When the page enters the new part from the old part, the action that occurs at this point is page made young. Because innodb_old_blocks_time the parameter causes the page not to move from old to the new part called Page not made young. show engine innodb statusthe status of the LRU list and the free list can be observed by command.

 ----------------------BUFFER POOL and MEMORY----------------------total Memory allocated 4395630592;      Additional pool allocated 0Dictionary memory allocated 28892957Buffer pool size 262143Free buffers 0Database pages  258559OLD database pages 95424Modified db pages 36012Pending reads 0Pending WRITES:LRU 0, flush list 0, single page 0Pages made young 72342127, isn't young 08.82 youngs/s, 0.00 Non-youngs/spages Read 72300801, created 339791, written 13639 0668.56 reads/s, 0.35 creates/s, 3.79 Writes/sbuffer pool hits rate 1000/1000, young-making rate 0/1000 not 0/1000pag ES read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/slru len:258559, Unzip_lru len:0i/o sum[459] : Cur[1], unzip sum[0]:cur[0]  

• Buffer pool size Indicates a total of 262,143 page for buffer pools, i.e. 262143 * 16K, approximately 4GB
• Free buffers indicates the number of page in the current free list
• Database pages represents the number of page in the LRU list
• Old database pages represents the page number of the old part of the LRU list
• Modified db pages represents the number of dirty pages (Dirty page)
• Pages made young indicates the number of times the page moved to the new part of the LRU list
• YOUNGS/S, NON-YOUNGS/S represents the number of times per second of these two operations
• The buffer pool hit rate represents the rate of the buffer pools, which, if less than 95%, need to be observed if the full table scan causes LRU contamination
• LRU Len indicates the total number of page in LRU

You can see the free buffers with database pages and not the buffer pool size, because the pages in the buffer pools are also assigned to the Adaptive Hash Index, lock information, Insert buffer, and so on, and this part of the page does not require LRU algorithm maintenance.

Dirty pages (Dirty page)

When the page in the LRU list is modified, it is called a dirty page, that is, the page in the buffer pool and the data on the page in the disk are inconsistent. This innodb the dirty page back to disk through the checkpoint mechanism. The page in the Flush list is a dirty page list. Dirty pages exist both in the LRU list and in the Flush list, and they do not affect each other. Modified db pages shows the number of dirty pages.

Redo Log Buffering

The InnoDB engine first places the redo log information into the Redo log buffer (redo log buffers) and then flushes to the redo log file at a certain frequency. Redo log buffers do not need to be set very large, generally every second will be refreshed redo log buffer, the size of the configuration only need to ensure that the transaction generated per second within the size of this buffer. Set by innodb_log_buffer_size the parameter:

mysql> show variables like ‘innodb_log_buffer%‘;+------------------------+----------+| Variable_name          | Value    |+------------------------+----------+| innodb_log_buffer_size | 16777216 |+------------------------+----------+

The contents of the redo log buffer are flushed to the disk redo log file in the following situations:

• Master thread refreshes every second
• Each transaction is committed when it refreshes
• When redo log buffer space is less than 1/2

Additional Memory pools

Additional memory pools are used to allocate memory for some data structures themselves, such as LRU, lock, wait, and so on, which are recorded by buffer control objects. The additional memory pool is not sufficient when the request is made from the buffer pool. Therefore, when a large InnoDB buffer pool is requested, the additional memory pool should be properly adjusted. Set the innodb_additional_mem_pool_size size by parameter. View the following commands:

mysql> show variables like ‘%pool_size‘;+---------------------------------+------------+| Variable_name                   | Value      |+---------------------------------+------------+| innodb_additional_mem_pool_size | 67108864   || innodb_buffer_pool_size         | 4294967296 |+---------------------------------+------------+

Memory management mechanism of INNODB storage engine