MySQL & #183; Performance Optimization & #183; about InnoDB buffer pool flush policies

Source: Internet
Author: User
Tags percona percona server

MySQL-Performance Optimization-about InnoDB buffer pool flush policies

Background

We know that InnoDB uses the buffer pool to cache data pages read from the disk to the memory. The buffer pool is usually composed of several memory blocks plus a group of control struct objects. The number of memory blocks depends on the number of buffer pool instances. However, in version 5.7, memory blocks are allocated by default in units of 128 MB (configurable) chunk, this aims to support dynamic online adjustment of the buffer pool.

Each memory block in the Buffer pool is allocated by mmap, so you will find that the virtual memory is very high when the instance is started, and the physical memory is very low. These memory blocks are divided into multiple frames based on 16 KB, which are used to store data pages.

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

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

  • The LRU linked list contains all data pages read into the memory;
  • Flush_list contains the modified dirty pages;
  • Unzip_LRU contains all extracted pages;
  • The Free list stores the currently idle blocks.


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

Generally, when we need to read a Page, we first find the corresponding buffer pool instance based on the space id and page no. Query the page hash. If the page hash does not exist, it indicates that it needs to be read from the disk. Before reading a disk, We need to allocate an idle block to the data page that will be read into the memory. When there is an idle block on the free list, you can extract it directly from the free list. If not, you need to evict the page from unzip_lru or lru.

Some principles need to be followed here (refer to function buf_LRU_scan_and_free_block, 5.7.5 ):

  1. First, try to unzip_lru To uncompress the extracted page;
  2. If no, try to evict the Page from the Lru linked list;
  3. If the idle block still cannot be obtained from Lru, the user thread will participate in dirty refreshing. Try to perform a single page flush, FLUSH a dirty PAGE from Lru separately, and then try again.

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

However, the dirty flushing policy based on the WAL principle may cause a problem: when the database write load is too high, the redo log generation speed is extremely fast, and the redo log may quickly reach the synchronization checkpoint. At this time, you need to dirty brush to promote Lsn. Because this behavior is triggered when the user thread checks that the redo log space is insufficient, a large number of user threads may fall into this inefficient logic, resulting in a significant performance inflection point.


Page Cleaner thread

In MySQL5.6, an independent page cleaner thread is enabled to flush lru list and flush list. It runs every second by default. version 5.6 provides a large 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

Here we will not introduce them one by one. In general, if you find that the redo log is advancing very fast, you can increase innodb_io_capacity_max to prevent the user thread from getting dirty, this parameter limits the maximum number of dirty pages refreshed per second. Increasing this value can increase the workload of the Page cleaner thread per second. If you find that the free list in your system is insufficient, you can increase innodb_lru_scan_depth as appropriate when you always need to evict dirty pages to obtain idle blocks. This parameter indicates the depth of scanning from the lru of each buffer pool instance. Increasing the value helps to release more idle pages and prevent the user thread from performing single page flush.

To improve scalability and dirty brush efficiency, multiple page cleaner threads are introduced in version 5.7.4 to achieve parallel dirty brush. Currently, Page cleaner is not bound to the buffer pool. Its model is a coordination thread + multiple working threads. The coordination thread itself is also a working thread. Therefore, if innodb_page_cleaners is set to 4, it is a coordinating thread, with three working threads added and the working method is producer-consumer. The length of the working queue is the number of buffer pool instances, expressed in a global slot array.

After determining the number of pages to be flushed and lsn_limit, the coordinating thread sets the slot array, sets the status of each slot to PAGE_CLEANER_STATE_REQUESTED, and sets the number of target pages and lsn_limit, then wake up the working thread (pc_request)

After a worker thread is awakened, it obtains an unused slot from the slot array and modifies its status, indicating that it has been scheduled and then operates on the buffer pool instance corresponding to the slot. It is not until all slots have been consumed to enter the next round. In this way, multiple page cleaner threads implement the concurrent flush buffer pool to improve the efficiency of flush dirty page/lru.


Optimization of InnoDB flush policy of MySQL5.7

In earlier versions, because there may be multiple threads simultaneously operating the buffer pool to fl the page (buffer pool mutex will be released when the cache is dirty ), each time a page is refreshed, it must be traced back to the end of the linked list, so that the time complexity of scanning the bp linked list is at least O (N * N ).

In version 5.6, the Flush list scan is fixed. A pointer is used to record the page currently being flush. After the flush operation is complete, check whether the pointer has been modified by other threads. If modified, it will be traced back to the end of the linked list; otherwise, no backtracking is required. However, this fix is incomplete. In the worst case, the time complexity is still unsatisfactory.

Therefore, this issue is 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 the LIST, there are several categories based on different purposes:

  • Flush_hp: used for batch flush list refreshing
  • Lru_hp: Used to batch brush LRU LIST
  • Lru_scan_itr: Used to evict a replaceable page from the LRU linked list, always starting from the end of the last scan, instead of the end of the LRU
  • Single_scan_itr: when there is no idle block in the buffer pool, the user thread will separately evict a replaceable page or FLUSH a dirty page from the flush LIST, always starting from the last scan, not the end of LRU.

The last two types of hp are called by the user thread when trying to obtain the idle block. Only when pushing to a buf_page_t :: when old is set to true (about the page from the end of the Lru linked list to the 3/8 position of the total length), the pointer is reset to the end of the Lru.

These pointers are allocated when the buffer pool is initialized. Each buffer pool instance has its own hp pointer. When a thread performs operations on the page in the buffer pool, such as removing the Page from LRU, if the current page is set to hp, update hp to the previous Page of the current page. After the flush operation of the current page is completed, use the page pointer stored in hp to perform the next flush operation.


Community Optimization

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

  • Optimize the LRU process buf_flush_LRU_tail
    This function is called by the page cleaner thread.
    • Native logic: flush each buffer pool instance in sequence. The depth of each scan is configured using the innodb_lru_scan_depth parameter. Each instance is divided into multiple chunks for calling;
    • The modified logic is: flush the LRU of a buffer pool each time, only one chunk is flushed, and then the next instance is flushed. After all instnace is refreshed, a chunk is flushed back to the front. In short, the concentrated flush operations are dispersed in order to distribute the pressure and avoid concentrated operations on an instance, giving other threads more access to the buffer pool.
  • You can set the timeout time for flushing LRU/flush list to prevent other threads (for example, user threads trying to perform single page flush) from stall. When the timeout time is reached, the page cleaner thread exits flush.
  • Avoid user threads from participating in buffer pool refreshing
    When a user thread participates in the buffer pool refreshing, the number of threads is uncontrollable, which leads to serious competition overhead. For example, when the free list is insufficient, single page flush is performed, and when the redo space is insufficient, the dirty page flush will seriously affect the performance. Percona Server allows the page cleaner thread to do this. The user thread only needs to wait. For efficiency, you can also set the cpu scheduling priority of the page cleaner thread.
    In addition, after the Page cleaner thread is optimized, you can know that the system is currently in the synchronous refresh state, and you can perform more intense dirty flushing (furious flush), where the user thread participates, it may only be counterproductive.
  • The page cleaner thread, purge thread, io thread, master thread CPU scheduling priority can be set and the mutex of InnoDB can be obtained first.
  • Use a new independent background thread to fl the LRU linked list of the buffer pool and strip the workload from the page cleaner thread.
    In fact, the code for flushing LRU is directly transferred to an independent thread. From the previous Percona version, the background threads are constantly being reinforced, so that user threads are less involved in dirty/checkpoint operations.

MySQL InnoDB table-Basic BTree Data Structure

Optimization of the count (*) function in the InnoDB Storage engine of MySQL

MySQL InnoDB Storage engine lock mechanism Experiment

Startup, shutdown, and restoration of the InnoDB Storage Engine

MySQL InnoDB independent tablespace Configuration

Architecture of MySQL Server layer and InnoDB Engine Layer

InnoDB deadlock Case Analysis

This article permanently updates the link address:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.