Shulou(Shulou.com)05/31 Report--

This article mainly explains the "MySQL performance optimization InnoDB buffer pool flush analysis", the content of the article is simple and clear, easy to learn and understand, now please follow the editor's ideas slowly in depth, together to study and learn "MySQL performance optimization InnoDB buffer pool flush analysis" bar!

Background

We know that InnoDB uses buffer pool to cache data pages read from disk into memory. A buffer pool usually consists of several blocks of memory plus a set of control structure objects. The number of memory blocks depends on the number of buffer pool instance, but in version 5.7, memory blocks are allocated by default in 128m (configurable) chunk units to support online dynamic resizing of buffer pool.

Each block of memory in Buffer pool allocates memory through mmap, so you will find that virtual memory is high and physical memory is low when the instance starts. These large blocks of memory are divided into multiple frame according to 16KB, which are used to store data pages.

Although buffer pool stores data pages in 16KB in most cases, there is an exception: when using compressed tables, you need to store both compressed and decompressed pages in memory, and for compressed pages, use the Binary buddy allocator algorithm to allocate memory space. For example, if we read a compressed page of 8KB, we take a block of 16KB from buffer pool, take the 8KB, and put the rest of 8KB on the free linked list; if we read the compressed page of another 4KB into memory, we can split the 4KB from this 8KB and put the remaining 4KB on the free linked list.

To manage buffer pool, each buffer pool instance is managed using the following linked lists:

The LRU linked list contains all data pages read into memory

Flush_list contains dirty pages that have been modified

Unzip_LRU contains all extracted pages

The currently idle block is stored on the Free list.

In addition, in order to avoid scanning LRU when querying data pages, a page hash is maintained for each buffer pool instance, and the corresponding page can be found directly through space id and page no.

In general, when we need to read a Page, we first find the corresponding buffer pool instance according to space id and page no. Then query page hash, if it is not in page hash, it means that you need to read from disk. Before reading the disk, we first need to allocate a free block to the data page that is about to be read into memory. When there is a free block on the free list, you can extract it directly from the free list; if not, you need to expel the page from the unzip_lru or lru.

Here you need to follow certain principles (see function buf_LRU_scan_and_free_block, 5.7.5):

First try to expel the extracted page from unzip_lru

If not, try to expel Page from the Lru linked list again

If the free block is still not available from the Lru, the user thread participates in scrubbing, tries to do a SINGLE PAGE FLUSH, removes a dirty page from the Lru alone, and then tries again.

After the page in Buffer pool is modified, it is not written to disk immediately, but is written regularly by the background thread. Like most database systems, the write disk of dirty pages follows the principle of log WAL first, so a recently modified Lsn is recorded on each block. When writing data pages, you need to make sure that the current redo written to the log file is not lower than this Lsn.

However, the dirty brushing strategy based on the WAL principle may bring a problem: when the write load of the database is too high, the speed of generating redo log is very fast, and the redo log may soon reach the synchronous checkpoint point. At this point, you need to scrub to promote the Lsn. Because this behavior is triggered by user threads when they detect that there is not enough redo log space, a large number of user threads are likely to fall into this inefficient logic, creating an obvious performance inflection point.

Page Cleaner thread

In MySQL5.6, a separate page cleaner thread is opened to brush lru list and flush list. Running every second by default, version 5.6 provides a number of parameters to control the flush behavior of page cleaner, including:

Innodb_adaptive_flushing_lwm, innodb_max_dirty_pages_pct_lwminnodb_flushing_avg_loopsinnodb_io_capacity_maxinnodb_lru_scan_depth

In general, if you find that redo log is advancing very fast, in order to prevent user threads from getting dirty, you can solve this problem by increasing innodb_io_capacity_max, which limits the maximum number of dirty pages to be refreshed per second. Increasing this value can increase the workload of Page cleaner threads per second. If you find that there is not enough free list in your system, and you always need to expel dirty pages to get free block, you can appropriately increase the innodb_lru_scan_depth. This parameter represents the depth of scanning from the lru of each buffer pool instance, and increasing this value helps to free up more free pages and prevent user threads from doing single page flush.

In order to improve scalability and cleaning efficiency, multiple page cleaner threads are introduced in version 5.7.4 to achieve the effect of parallel cleaning. At present, Page cleaner is not bound to buffer pool, its model is one coordination thread + multiple worker threads, and the coordination thread itself is also a worker thread. So if innodb_page_cleaners is set to 4, it is a coordinator thread, plus three worker threads, and works in a producer-consumer manner. The length of the work queue is the number of buffer pool instance, represented by a global slot array.

After determining the number of page and lsn_limit needed for flush, the coordinator thread sets the slot array, sets the state of each slot to PAGE_CLEANER_STATE_REQUESTED, sets the target number of page and lsn_limit, and then wakes up the worker thread (pc_request)

After the worker thread is awakened, it fetches an unoccupied slot from the slot array, modifies its state to indicate that it has been scheduled, and then operates on the buffer pool instance corresponding to the slot. It is not until all the slot has been consumed before moving on to the next round. In this way, multiple page cleaner threads implement concurrent flush buffer pool, thus improving the efficiency of flush dirty page/lru.

Optimization of InnoDB flush Strategy of MySQL5.7

In previous versions, because there may be multiple threads operating buffer pool brushing page at the same time (buffer pool mutex will be released when brushing dirty), one page at a time needs to be traced back to the end of the linked list, making the time complexity of scanning bp linked list the worst.

In version 5.6, some fixes have been made for the scan of Flush list. A pointer is used to record the page currently in flush. After the flush operation is completed, check whether the pointer has been modified by other threads. If so, it can be traced back to the end of the linked list, otherwise there is no need to backtrack. But this fix is not complete, and in the worst case, the time complexity is still not ideal.

Therefore, this problem has been completely fixed in version 5.7. multiple pointers named hazard pointer are used to store the next target page to be scanned when you need to scan LIST, which can be divided into several categories according to different purposes:

Flush_hp: used as a batch brush FLUSH LIST

Lru_hp: used as a batch brush LRU LIST

Lru_scan_itr: used to expel a replaceable page from the LRU list, always starting at the end of the last scan, not the tail of the LRU

Single_scan_itr: when there is no free block in buffer pool, the user thread will separately expel a replaceable page or flush a dirty page from the FLUSH LIST, always starting at the end of the last scan, not the tail of the LRU.

The latter two types of hp are called by the user thread when trying to get the free block, and the pointer is reset to the end of the Lru only when a buf_page_t::old is set to the page of the true (approximately from the end of the Lru list to 3/8 of the total length of the Lru).

These pointers are allocated when the buffer pool is initialized, and each buffer pool instance has its own hp pointer. When a thread operates on a page in buffer pool, such as when it needs to remove Page from LRU, if the current page is set to hp, the hp is updated to the previous page of the current Page. When the flush operation of the current page is completed, the page pointer stored in the hp is directly used for the next round of flush.

Community optimization

As always, Percona Server has made a lot of optimizations for buffer pool flush in version 5.6, and the main changes include the following:

Optimize the process of brushing LRU buf_flush_LRU_tail

This function is called by the page cleaner thread.

Native logic: flush each buffer pool instance in turn, and the depth of each scan is configured by the parameter innodb_lru_scan_depth. And within each instance, it is divided into multiple chunk to call

The modified logic is: each time flush a buffer pool LRU, only one chunk, and then the next instance, after brushing all the instnace, and then go back to the front to brush another chunk. In short, centralized flush operations are dispersed in order to disperse the pressure, avoid centralized operations on an instance, and give other threads more access to the buffer pool.

Allows you to set the timeout for brushing LRU/FLUSH LIST to prevent other threads (such as user threads trying to do single page flush) from stall when the flush operation time is too long; when the timeout is reached, the page cleaner thread exits the flush.

Avoid user threads participating in browsing buffer pool

When user threads participate in browsing buffer pool, because the number of threads is out of control, there will be serious competitive overhead, such as single page flush when free list is insufficient, and dirty page flush when redo space is insufficient, will seriously affect performance. Percona Server allows you to choose to let the page cleaner thread do the work, and the user thread can just wait. For efficiency reasons, the user can also set the cpu scheduling priority of the page cleaner thread.

In addition, after the Page cleaner thread has been optimized, we can know that the system is currently in a synchronous refresh state and can do more intense furious flush, in which user threads may only play a counterproductive role.

Allows you to set the CPU scheduling priority of page cleaner threads, purge threads, io threads, and master threads, and give priority to InnoDB's mutex.

Use a new independent background thread to brush buffer pool's LRU linked list and remove this part of the workload from the page cleaner thread.

In fact, it is to transfer the code that brushes LRU directly to a separate thread. From the previous version of Percona, it is constantly strengthening background threads, so that user threads are less involved in time-consuming operations such as dirty / checkpoint.

Thank you for reading, the above is the content of "MySQL performance optimization InnoDB buffer pool flush analysis", after the study of this article, I believe you have a deeper understanding of MySQL performance optimization InnoDB buffer pool flush analysis, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.