MySQL InnoDB Architecture Memory

First

1. Buffer pool
The InnoDB storage engine is disk-based and manages the records in it as a page. Buffer pools are typically used to improve the overall performance of the database. The buffer pool is simply a piece of memory, which compensates for the slow disk speed impact on database performance through memory speed. When reading in the database, first the page read from the disk in the buffer pool, the next time the same page is read, the first to determine whether there is a buffer pool, if there is a hit directly read, no words are read from the disk. When the database is changed, the pages in the pool are first buffered and then flushed to disk at a certain frequency. The refresh mechanism here does not trigger every page when a change occurs. Instead, it is flushed to disk through a checkpoint mechanism. The size of the buffer pool can be set through the Innodb_buffer_pool_size (unit block) parameter. The database types in the buffer pool are: Index page, data page, undo page, insert cache page (insert buffer), adaptive hash (Adaptive Hash index), InnoDB stored lock information (lock info), data dictionary information Dictionary). The parameter innodb_buffer_pool_instances setting allows for multiple buffer pool instances, each of which is evenly distributed to different instances based on the hash value, reducing the competition for resources within the database and increasing the concurrency of the database.
2.LRU list, free list, and flush list
The buffer pool for the InnoDB storage engine is managed by the LRU algorithm, which is the most frequently used page in the LRU list (which manages the pages that have been read to) and the least used page at the end of the LRU list. The buffer pool has a size of 16K per page. But it does some optimizations to the LRU algorithm and joins the midpoint position. The newly acquired data is put into the midpoint instead of the list header. Typically midpoint is located at 5/8 of the LRU list. Can be set by the innodb_old_blocks_pct parameter. The list after midpoint is called the old list, previously known as the new list. Why is midpoint used in the InnoDB storage engine? This is because, as the read directly is also put into the first end of the LRU, some SQL operations cause the page in the buffer pool to be brushed out, thus affecting the efficiency of the buffer pool. You can use the parameter Innodb_old_blocks_time Settings page to wait for how long after you read to the midpoint location to join the LRU new list.

When the database starts, the LRU list is empty, and all the pages are placed in the free list. When a page needs to be allocated from the buffer pool, first check to see if there are idle free pages from the free list, and if so, remove from the list and add to the LRU list. Otherwise, the page at the end of the LRU list is eliminated according to the LRU algorithm, and the memory space is allocated to the new page. When the page is added to the new part from the old part of the LRU, the action that occurs is page made young, and because the setting of the Innodb_old_blocks_time parameter causes the page not to be added from old to new, the operation is called Page not made. The ability to compress pages starts with the 1.0.X version of the InnoDB storage engine. The original 16K page is compressed to 1K, 2K, 4K, and 8 K, because the size of the page changes, the LRU list page has a corresponding change, for non-16K pages through the UNZIP_LRU list for maintenance. When a page in the LRU list is modified, the page is called a dirty page (Dirry page), and the database brushes the dirty page back to disk through the checkpoint mechanism. The page in the Flush list is the dirty page list. Dirty pages are present in the LRU list page that exists in the flush list. The Lur list is used to manage the availability of pages in the buffer pool, and the flush list is used for administration and will also be brushed back to disk. Use the Show Engine InnoDB status command to view the usage and operational status of the LRU list and the free list.

As can be seen from the diagram:

Buffer Pool Size has a total of 8,191 pages (8191*16k);

Free buffers indicates the number of pages in the current free list;

Datebase pages represents the number of pages in the LRU list. The sum of the free buffers and database pages may not be equal to the buffer pool size. This is because the buffer pool may also be assigned to pages such as adaptive hash indexes, lock information, insert buffer, and so on. This part is not maintained by the LRU algorithm and is therefore not in the LRU list.

Page made young represents the number of times the page moved to the previous segment in the LRU list.

Buffer pool hits rate indicates the hit ratio of the buffer pools, which cannot be less than 95%.

LRU Len indicates how many pages are in the LRU list.

Unzip_lru Len represents the number of pages compressed, where LRU len contains Unzip_lru len. Modified db pages represents the current number of dirty pages.

You can also pass INFORMATION_SCHEMA. INNODB_BUFFER_PAGE_LRU table to observe specific information for each page in the LRU list.

3. Redo log buffers (redo log buffer)
The InnoDB storage engine first puts the redo log information into this buffer and then brushes it into the redo log file at a certain frequency. Redo log buffers generally do not require much, as long as the amount of transactions generated per second is within this buffer size. The size can be set by the Innodb_log_buffer_size parameter.

4. Additional Memory pools
In the InnoDB storage engine, the management of memory is done in a way called the Memory Heap (heap). When allocating memory for some data structures themselves, applications need to be made from an additional pool of memory that needs to be requested from the buffer pool when there is not enough memory for that area. For example L: The buffer pool is allocated, but the framebuffer (frame buffer) in each buffer pool also has a corresponding buffer control object (buffer controlled block), which records some information such as LRU, lock, wait, and so on, and the memory of this object needs to be requested from the additional memory pool. Therefore in the application of a large buffer pool is also to consider the corresponding increase in this value.

MySQL InnoDB Architecture Memory