MySQL MySQL buffer pool structure analysis

Transferred from: http://blog.csdn.net/wyzxg/article/details/7700394

Other references:

"High performance MySQL"-8.4.5 innodb buffer pool

MySQL Technology insider InnoDB Storage Engine (second edition content updated)-2.3 InnoDB Architecture

##############################################

The book is to explain the background thread first, and then to explain the memory section, so that better understand the InnoDB engine memory pool when using the process.

"Background Threads"

InnoDB has multiple blocks of memory that can be thought of as constituting a large memory pool that is responsible for the following tasks:

Maintains multiple internal data structures that all processes/threads need access to.
Caches the data on the disk, facilitates quick reads, and caches the data in the disk file before making modifications to it.
Redo log (redo log) buffer.

The primary role of a background thread is to refresh the data in the memory pool to ensure that the memory in the buffer pool caches the most recent data. In addition, the modified data file is flushed to the disk file, and InnoDB can be restored to normal operation in the event of an exception in the database.

By default, the InnoDB storage engine has a background thread of 7, 4 io thread,1 master thread,1 Lock (lock) monitoring threads, and 1 Error monitoring threads. The number of IO thread is controlled by the Innodb_file_io_threads parameter in the configuration file, which defaults to 4 and can be viewed through the show engine InnoDB status \g to view IO thread, for example:

Mysql>Show engine InnoDB status \g*************************** 1. Row***************************type:innodb name:status:=====================================...--------FILEI/O--------I/O thread0State:waiting forI/O Request (Insertbuffer thread) I/O thread1State:waiting forI/O Request (Logthread) I/O thread2State:waiting forI/O Request (Readthread) I/O thread3State:waiting forI/O Request (Readthread) I/O thread4State:waiting forI/O Request (Readthread) I/O thread5State:waiting forI/O Request (Readthread) I/O thread6State:waiting forI/o request (write thread) I/O thread7State:waiting forI/o request (write thread) I/O thread8State:waiting forI/o request (write thread) I/O thread9State:waiting forI/O Request (write thread)

You can see that four of the above IO threads were insert buffer thread, log thread, read thread, write thread. MySQL 5.5 can be configured for the number of read thread, write thread of Io thread, and the default read thread, write thread is increased to 4 respectively, the default insert buffer thread, log Thread is still on the same threads as the MySQL 5.5 version, and the innodb_file_io_threads parameter is not used, but Innodb_read_io_thread and innodb_write_io_ are used respectively The thread parameter, which can be adjusted based on CPU cores, disk IO performance, and if the read thread or write thread is configured to be large but the actual server performance is not met, will cause the thread to request a backlog, but will degrade performance.

Memory

The InnoDB storage engine memory consists of the buffer pool, the redo log buffer pool (redo log buffer), and the additional memory pool (additional), respectively, by the parameters in the configuration file innodb_ The size of buffer_pool_size and innodb_log_buffer_size is determined.

##############################################

Three lists and three page types in MySQL buffer pool

Buffer pool is managed through three lists
1) free list
2) LRU List
3) flush list

buffer pool The smallest unit is page, Three types of page
1) are defined in InnoDB free page: This page is not used, the page is in the free list
2) Clean page: This page is used, corresponding to a page in the data file, However, the page is not modified, and this type page is in the LRU list
3) Dirty page: This page is used to correspond to a page in the data file, but the page has been modified, this type of page is in the LRU list and the flush list

how the buffer pool Flush list Works How does the
Dirty page exist in the flush list?
The page that exists in the flush list can only be dirty Page,flush list dirty page is sorted by oldest_modification time when page access/ When the modification is encapsulated as a mini-transaction,mini-transactin commit, the mini-transaction involves the page into the flush list, the greater the value of oldest_modification, Note that the later the page was modified, in the head of the flush list, the smaller the value of the Oldest_modification, the earlier the page was modified, the end of the flush list, so that when the flush list does flush action, Start scan from the tail of the flush list, write a certain number of dirty page to disk, recommend checkpoint point, make the recovery time as short as possible. In addition to the flush operation of the flush list itself, the dirty page is removed from the flush list, and the flush operation of the LRU list also causes the dirty page to be removed from the flush list.

How the buffer pool LRU list works
In general, whenever a new page is read in buffer pool, the MySQL database InnoDB storage engine will determine if the free page of the current buffer pool is sufficient and, if insufficient, attempt to flush the LRU list.
Before MySQL 5.6.2, the user thread was reading into a page (buf_read_page), creating a new page (buf_page_create), a read-ahead page (buf_read_ahead_linear), and so on. After the operation succeeds, call the Buf_flush_free_margin function to determine if there is enough free pages for the current buffer pool, and if there are insufficient pages for the. LRU list flush, release sufficient pages To ensure the availability of the system.

By judging how many dirty pages need to be flush in the current BUF pool, it is possible to reserve enough replaceable pages (free pages or clean pages in LRU list tail).

Description
The available pages consist of the following two parts:
1. All the page in the BUF pool free list can be used immediately.
2. All clean pages of the BUF pool LRU list tail (5+2*buf_read_ahead_area).
Where: Buf_read_ahead_area is 64, is a linear read AHEAD reads the size, 1 extent

Since the Buf_flush_free_margin function is invoked in the user thread, if flush LRU list is required, the response time of the user is significantly affected. Therefore, after MySQL 5.6.2, InnoDB specifically opened a page cleaner thread that handles the flush action of the dirty page (including the LRU list flush and flush list flush), reducing the page The effect of flush for the user.
In the version before and after MySQL 5.6.2, the LRU list flush differs in that it is initiated by the user thread or by a background page cleaner thread. However, the BUF_FLUSH_LRU function is called to perform a true flush operation, whether it is a user thread or a background page cleaner thread, and then decides that the LRU list flush will be required.

The difference is that, before MySQL 5.6.2, the user thread called the Buf_flush_free_margin function, in determining whether the LRU list flush is really required, the LRU list tail part of the clean Pages are also categorized as pages that can be replaced without the need for flush. In the page cleaner thread, the LRU list flush call occurs every 1s, regardless of whether the page in the LRU list tail is clean. It is also understandable that the user thread needs to minimize the flush probability and improve the user response, while the background thread tries to flush the attempt, freeing up enough free pages to ensure that the user thread does not clog.

Buffer Pool lru/flush List Flush comparison
1). The LRU list flush, triggered by the user thread (before MySQL 5.6.2), while the Flush list flush is handled by the MySQL database InnoDB storage engine backend Srv_master thread. (After MySQL 5.6.2, they are migrated to the page cleaner thread)
2). The LRU list flush, which is designed to write out the dirty page at the end of the LRU list, frees up enough free pages, and when the BUF pool is full, the user can immediately get the idle page without having to wait long; Flush List flush, The goal is to advance the checkpoint LSN, which allows the InnoDB system to recover quickly after a crash.
3). The LRU list flush, which writes out the dirty page, needs to be moved to the tail of the LRU list (prior to MySQL 5.6.2), or it is removed directly from the LRU list and moved to the free list (after MySQL 5.6.2 version). Flush list Flush, you do not need to move the page position in the LRU list.
4). The LRU list flush, which may be initiated by the user thread, already holds other page latch, so in the LRU list flush, it is not allowed to wait for the new page latch to be held, resulting in a latch deadlock, while the Flush list flush is initiated by the background thread , does not hold any other page latch, so you can wait for page latch while flush.
5). LRU list Flush, the number of dirty pages per flush is small, basic fixed, as long as the free pages can be released, flush list flush, according to the current system update busy level, dynamically adjust the flush dirty Pages in large quantities.

How the buffer pool free list works
Free link table is stored in the idle page, when the initialization of the application of a certain number of pages, in the process of use, each successful load page to memory, will determine whether free page is sufficient, if not enough, flush the LRU list and flush linked list to release page, which can satisfy other processes on the application page and make the system available.