Mysql-innodb-buffer Pool structure Analysis of "turn"

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

MySQL official website configuration description Address: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html

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: 
=====================================
...
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/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 (below the color map Memery-disk in the middle of buffer pool and TABLE.IBD is the read thread, write Thread), the default read thread, write thread is increased to 4, the default insert buffer thread, the log thread is still a thread, and the MySQL 5.5 version of the configuration, and no longer use innodb_ File_io_threads parameters, using the Innodb_read_io_thread and Innodb_write_io_thread parameters respectively, this parameter can be adjusted based on CPU cores, disk IO performance, if the Read The thread or write thread configuration is large but the actual server performance is not met, which can lead to a backlog of threads requests, 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_ Buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size size decision.

The data page types cached in the buffer pool are: Index page, data page, undo page, insert buffer (insert buffer), adaptive hash (Adaptive Hash index), InnoDB stored lock information (lock info), data dictionary information Dictionary), the buffer pool not only has data pages and index pages, but they account for a large portion of the buffer pool, innodb the memory structure of the storage engine such as:

A buffer pool is a buffer for storing various kinds of data, because the InnoDB storage engine works by always reading the database files by page (16K per page) to the buffer pool and then keeping the cached data in the buffer pool by the least recently used (LRU) algorithm. If the database file needs to be modified, the pages in the buffer pool are always first modified (the page is dirty after the modification), and then the dirty pages of the buffer pool are flushed (flush) to the file at a certain frequency.

Use show engine InnoDB status \g to view the specific usage of Innodb_buffer_pool (show engine InnoDB status is not in real-time InnoDB engine status, just an average of the previous period):

=====================================
Per second averages calculated from the last 17 seconds
-----------------
...
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 52747567104; in additional pool allocated 0
Dictionary memory allocated 1147940
Buffer pool size   3145727
Free buffers       3143088
Database pages     2630
Old database pages 950
Modified db pages  0

The buffer pool size indicates how many buffer frames are in total (buffer frame), and each buffer frame is 16K. The free buffers represents the currently idle buffer frame, databases pages represents the buffer frame already in use, and modified DB pages represents the number of dirty pages.

The log buffer puts the redo log information into this buffer first, and then flushes it to the Redo log file at a certain frequency, which generally does not need to be set very large, because the redo log buffers are flushed to the log file every second, so we only need to ensure that the amount of transactions generated per second is Innodb_log_ The Buffer_size parameter controls the buffer size within.

The extra memory pool is usually ignored, which is really important, and in the InnoDB storage engine, the management of memory is done in a way known as the Memory Heap (heap). When allocating memory to some data structures themselves, you need to request from an additional pool of memory, which is requested from the cache pool when there is not enough memory in the zone. The InnoDB instance requests space for the buffer pool (innodb_buffer_pool), but the framebuffer (frame buffer) in each buffer pool also has a corresponding buffer control object, which records such as LRU, Locks, waits for information, and the memory of this object needs to be requested from an additional pool of memory. Therefore, when the InnoDB buffer pool is large, the additional memory pool should also increase.

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

Find a XTRADB/INNODB kernel structure on the internet, xtradb at some points on the InnoDB did the optimization, but the principle is basically the same, the original image is Oschina above, the original resolution is very low, the approximate structure and corresponding parameters can be seen clearly.

Redo and Undo,undo ahead of redo

Redo can be understood as a backup of operational statements that need to be performed to ensure transactional integrity, saved in Ib_logfile

Undo can be understood as the original snapshot backup before data is executed by the action statement, used for rollback, saved in the Ibdata shared table space

MySQL Technology insider InnoDB Storage Engine-4.2.1 table space

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

For parameter options with innodb_file_per_table enabled, it is important to note that the table space in each table is only data, index, and insert buffers, data for other classes, such as undo information, system transaction information, two write buffers (double write Buffer), etc. or in the original shared table space.

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

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.

Configuring Innodb_buffer_pool_instances for multiple buffer pool

Official website description
For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages. This feature is typically intended for systems with a buffer pool size in the multi-gigabyte range. Multiple buffer pool instances are confi gured using the innodb_buffer_pool_instances configuration option, and you might also adjust the innodb_buffer_pool_size value.
When the InnoDB buffer pool is large, many data requests can be satisfied by retrieving from memory. You might encounter bottlenecks from multiple threads trying to access the buffer pool at once. You can enable multiple buffer pools to minimize this contention. Each page that is stored in or read from the buffer pool is assigned to one of the buf fer pools randomly, using a hashing function. Each buffer pool manages its own free lists, flush lists, LRUs, and all other data structures connected to a buffer pool, and is protected by its own buffer pool mutex.
To enable multiple buffer pool instances, set the innodb_buffer_pool_instances configuration option to a value greater than 1 (the default) up to 64 (the maximum). This option takes effect only when you set the innodb_buffer_pool_size to a size of 1 gigabyte or more. The total size you specify is divided among all the buffer pools. For best effici ency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1 gigabyte.

LOG buffer Brush disk mechanism modified Innodb_flush_log_at_trx_commit

If the value of Innodb_flush_log_at_trx_commit is 0, the log buffer was written out to the log file once per second and the Flush to disk operation was performed on the log file and nothing was done at a transaction commit. When the value was 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush To disk operation was performed on the log file. When the value was 2, the log buffer is written the-the file at each commit, but the flush to disk operation is not perf Ormed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the Once-per-second flushing are not 100% guaranteed to happen every second, due to process scheduling issues. The default value of 1 is required to full ACID compliance. You can achieve better performance by setting the value different from 1, and then you can lose up to one second worth of Transactions in a crash. With a value of 0, any mysqld process crash can eraseThe last second of transactions. With a value of 2, only the operating system crash or a power outage can erase the last second of transactions. InnoDB ' s crash recovery works regardless of the value. Duty is 0,log_ Buffer log buffers (the bottom portion of the color map above the Memery-disk chart) writes out every second to the log file ib_logfile that is flushed to disk and does nothing when a single transaction commits.

The value is 1 (the default), and the log buffers are written to log files that are executed at each transaction commit and flush to disk operations log files.

When the value is 2, the log buffer is written out to the file at each commit, but does not perform a flush-to-disk operation on it. However, flushing in the log file occurs once per second and is also a value of 2. Note that once per second flushing does not guarantee that 100% of the situation occurs every second because of scheduling problems to be handled. Requires a full acid-compliant default value of 1. You can achieve better performance by setting this value from 1 different, but you may lose as many trades as a crash for a second. With a value of 0, any mysqld process crash can erase the last second of a transaction. A value of 2, only one operating system crashes or loses power when the last second of the erasable transaction is available. InnoDB's crash recovery works, regardless of value.

Physical Structure

Mysql-innodb-buffer Pool structure Analysis of "turn"