Detailed explanation of memory management of MySQL InnoDB storage engine

Storage Engine Memory Management

In the InnoDB storage engine, the buffer pool in the database is managed by the LRU (Latest Recent Used) algorithm, that is, the most frequently used pages are at the front of the LRU list, and the least used pages are at the end of the LRU list. When the buffer pool cannot store the newly read pages, the pages at the end of the LRU list are released first.

In the above figure, I use 8 data pages to represent the queue. I will keep you in suspense about their specific functions. In the InnoDB storage engine, the default size of a page in the buffer pool is 16KB. There is a midpoint position in the LRU list. The newly read data page is not directly placed at the head of the LRU list, but at the midpoint position of the LRU list. This operation is called the midpoint insertion stategy, also known as the midpoint insertion strategy. In the default configuration, this position is at 5/8 of the LRU length, which is why 8 data pages are used above. The following diagram illustrates the process of inserting a new data page:

The location of the mitpoint can be controlled by the parameter innodb_old_blocks_pct, as follows:

mysql> show variables like 'innodb_old_blocks_pct';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 37 |
+-----------------------+-------+
 row in set (. sec)

From the above example, the result is 37, which means that the newly read page will be inserted at a position approximately 37% of the end of the LRU list, which is about 3/8 of the distance. In the InnoDB storage engine, the pages before the midpoint are called the new list, and the pages after the midpoint are called the old list. The pages in the new list are the most active data.

Why not just put the data page at the head of the LRU queue?

The reason why the newly read data page is not placed at the head of the LRU queue is that some full table scan SQL operations may refresh all hot data out of the LRU queue, resulting in the next access to the hot data requiring the corresponding data to be retrieved from the disk, thus affecting the efficiency of the buffer pool. To solve this problem, InnoDB uses another parameter to manage the LRU list, innodb_old_blocks_time, which is used to indicate how long it will take for a page to be added to the hot end of the LRU list after it is read to the midpoint. Therefore, when the above-mentioned SQL operation needs to be executed, the following method can be used to prevent the hot data in the LRU list from being flushed out as much as possible.

mysql> set global innodb_old_blocks_time=;
Query OK, rows affected (0.00 sec)

This means that after 1000s, the data is allowed to be refreshed to the hot end of the LRU list.

If in actual situations, the active ratio of data pages is more than 63%, users can also reduce the probability of hot pages being flushed by setting innodb_old_blocks_pct.

mysql> set global innodb_old_blocks_pct=;                                                                                                     
Query OK, rows affected (0.00 sec)

When the database is just started, the content of LRU is empty. At this time, all data pages are placed in the Free list. When paging from the buffer pool is required, first check whether there is an available Free page from the Free list. If so, delete the page from the Free page and then put it into the LRU list. Eliminate the data page at the end of the LRU list and allocate the memory space to the new page. The flowchart of this process is as follows:

When a page in the LRU list is added from the old part to the new part, the operation that occurs at this time is called page made young, and the operation that is not moved from the old part to the new part due to the setting of innodb_old_blocks_time is called page_not_made young. You can use show engine innodb status to observe the usage and running status of the LRU list and Free list.

mysql> show engine innodb status\G
***
***
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 
Dictionary memory allocated 
Buffer pool size   
Free buffers       
Database pages     
Old database pages 
Modified db pages  
Pending reads      
Pending writes: LRU, flush list, single page 
Pages made young , not young 
0.00 youngs/s, 0.00 non-youngs/s
Pages read, created, written 
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: , unzip_LRU len: 
I/O sum[]:cur[], unzip sum[]:cur[]
--------------
ROW OPERATIONS
--------------
 queries inside InnoDB, queries in queue
 read views open inside InnoDB
Process ID=, Main thread ID=, state: sleeping
Number of rows inserted, updated, deleted, read 
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

 row in set (0.00 sec)

From the above results, we can see that the current buffer pool size has a total of 8191 pages, the size of each data page is 16k, and the total size is 8191*16k=128M of the buffer pool, where Free buffers indicates the number of pages in the current Free list. page made young shows the number of times a page in the LRU list is moved to the front. Because the server does not change the value of innodb_old_blocks_time during operation, not young is 0. youngs/s and non_youngs/s indicate the number of these two types of operations per second.

The InnoDB storage engine supports compressed pages starting from version 1.0.x, which compresses the original 16KB data page into 1KB, 2KB, 4KB, and 8KB. For pages other than 16KB, they are managed by unzip_LRU. Line 22 in the above command shows the information of compressed and uncompressed pages.

One thing to note is that the sum of the Free buffers value and the Database Pages value is not necessarily equal to the buffer pool size, because the pages in the buffer pool may also be allocated with pages such as adaptive hash indexes and lock information, and these pages do not require LRU algorithm maintenance.

Dirty Pages

After a page in the LRU list is modified, the page is called a "dirty page", that is, the data page in the buffer pool is inconsistent with the data on the disk. The data in the buffer pool is newer. At this time, the database will refresh the dirty page back to the disk through the checkpoint mechanism, and the page in the Flush list is the dirty page list. The dirty page exists in both the LRU list and the 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 the refresh of pages back to the disk. The two do not affect each other. The flush list can also be viewed through show engine innodb status. In the 13th row of the previous result list, modified db pages is the current number of dirty pages, which can be viewed through the metadata table INNODB_BUFFER_PAGE_LRU.

The above is a detailed explanation of the memory management of the MySQL InnoDB storage engine. For more information about InnoDB memory management, please pay attention to other related articles on 123WORDPRESS.COM!