InnoDB buffer Pool Related features

Background

InnoDB buffer pool As the most important cache of InnoDB, its cache hit rate will directly affect the performance of the database. Therefore, changes in the database, such as restart, primary and standby switch instance migration, and so on, InnoDB buffer poll need a period of time to warm up, during the performance of the database will be significantly affected.
In addition MySQL 5.7 before InnoDB buffer pool cache size modification is not dynamic, restart to take effect. Therefore InnoDB buffer pool preheating and InnoDB buffer pool size dynamic modification is a good feature for applications with higher performance requirements, let me look at the specific implementation of these two features.

Buffer pool preheating

The buffer pool Preheat function is supported after MySQL 5.6. The following parameters are introduced, the specific meaning of the parameters see official documentation

innodb_buffer_pool_load_nowinnodb_buffer_pool_dump_nowinnodb_buffer_pool_load_at_startupinnodb_buffer_pool_dump_at_startupinnodb_buffer_pool_filename

Buffer pool preheating is divided between the dump process and the load process, which is done by the background thread buf_dump_thread.
For example, a user initiates a set command

set global innodb_buffer_pool_dump_now=on;set global innodb_buffer_pool_load_now=on;

The SET command returns immediately, and the operation is implemented by Buf_dump_thread.

• Dump process

  Lock Buf_pool
  Iterates through the LRU list, collecting (space, PageNo) the array first
  Release lock
  Then write the data to the Innodb_buffer_pool_filename file.

• Load process

  Reading from a file into an array
  Sort data by (Space,pageno)
  Synchronously reads the page into buffer pool

The dump process is generally faster, while the load process is relatively slow.

Through Innodb_buffer_pool_dump_status , Innodb_buffer_pool_load_status you can see the status of the Dump/load

Another 5.7 introduced performance_schema.events_stages_current to show load progress, and each load 32M updates a progress message

select * from performance_schema.events_stages_current;THREAD_ID       19EVENT_ID        1367END_EVENT_ID    NULLEVENT_NAME      stage/innodb/buffer pool loadSOURCE  buf0dump.cc:619TIMER_START 33393877311000TIMER_END 33398961258000TIMER_WAIT 5083947000WORK_COMPLETED 0WORK_ESTIMATED 1440NESTING_EVENT_ID NULLNESTING_EVENT_TYPE NULL

work_estimated = Total Page Count
Work_completed indicates the current load page number

The data format of the dump file is as follows

#cat ib_buffer_pool |more0,70,10,30,20,40,110,50,6

The dump file is relatively simple, and we can edit this file to preload the specified page, which is more flexible.

Buffer pool Dynamic Resizing

5.7 started to support buffer pool dynamic resizing, each buffer_pool_instance consisting of the same number of chunk (chunks array), each chunk memory size innodb_buffer_pool_chunk_size (5% is actually large, used to hold the Block information in Chuck). Buffer pool to innodb_buffer_pool_chunk_size dynamically grow and shrink for the unit. You innodb_buffer_pool_size should always keep the innodb_buffer_pool_chunk_size multiple of * before and after innodb_buffer_pool_instances the adjustment.

The same buffer pool is dynamically resized by the background thread buf_resize_thread , and the SET command returns immediately. InnoDB_buffer_pool_resize_statusYou can view the running state of the adjustment.

• Resize process

  • If Ahi is turned on, you need to disable AHI
  • If you are shrinking memory
    1. calculate the number of chunk that need to be shrunk, the tail delete from chunks Fixed number of chunk.
    2. Lock Buf_pool
    3. Remove the page from free_list to be deleted chunk the linked list Buf_pool->withdraw
    4. if the page that you want to delete chunk is dirty, brush dirty
    5. reload the page to be deleted from the LRU, remove from LRU, retrieve page from the free list The old page is placed in the Buf_pool->withdraw link
    6. release buffer pool lock
    7. if the chunk pages that need to be shrunk are not collected all, repeat 2-6
  • Start resize
    1. lock All instance Buffer_pool,page_hash
    2. Shrink Pool: Release the memory to be shrunk in chunk
    3. empty W Ithdraw list Buf_pool->withdraw
    4. Increase Pool: Assign a new chunk
    5. reassign buf_pool->chunks
    6. If you change/Shrink super Twice times, resets the page hash, changes the bucket size
    7. releases the Buffer_pool,page_hash lock
    8. If the change/shrink is more than twice times, the memory structure associated with the buffer pool size, such as the lock system, is restarted. _sys_resize), AHI (btr_search_sys_resize), data fields (dict_resize), etc.
  • If AHI is disabled, the

As can be seen from the above, it is relatively easy to enlarge memory rather than shrink memory. When you reduce memory, if you encounter a transaction that has been uncommitted and occupies a page that is to be shrunk, causing the contraction to retry, error log prints this retry message.
Contains transaction information that may reference this issue. To avoid frequent retries, the time interval for each retry is exponentially increased.

The Resize stage buffer pool is not available in the above steps, which locks all buffer pool, but this stage is memory operation, and the time is relatively short. Shrinking the memory phase can be time-consuming and may have some impact, but each time it is locked in instance.
In general, the dynamic resizing of the buffer pool has little effect on the application.

• Reload the effect of the page to be deleted in the LRU

  The page that the BTR cursor saved during search may have been reloaded, and the root page of the adaptive hash save may have been reloaded and will need to be read again.

Summarize

The buffer pool is preheated and the buffer pool is dynamically resized, which complements the two functions. Buffer pool dynamic resizing is only applicable if the instance is upgraded locally on the host, and if the user modifies the buffer pool size and involves a cross-machine migration, the buffer pool preheating feature will be useful.
In addition, buffer pool dynamic adjustment as far as possible in the business of low front.

InnoDB buffer Pool Related features