Shulou(Shulou.com)06/01 Report--

Background

As the most important cache of innodb, the cache hit ratio of innodb buffer pool will directly affect the performance of the database. Therefore, when the database changes, such as restart, master / slave switch instance migration, etc., innodb buffer pool needs a period of time to warm up, during which the performance of the database will be significantly affected. In addition, before mysql 5.7, the modification of innodb buffer pool cache size is not dynamic and the restart will not take effect. Therefore, the preheating of innodb buffer pool and the dynamic modification of innodb buffer pool size are good features for applications with high performance requirements. Let's take a look at the implementation of these two features.

Buffer pool preheating

Buffer pool prefetch is supported after MySQL 5.6, The following parameters have been introduced. For more information, please see the 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 into dump process and load process, both of which are completed by the backstage 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 specific operation is implemented by buf_dump_thread.

Dump process

Lock buf_pool traverses the LRU linked list, collecting (space, pageno) into the array to release the lock, and then writing the data to the innodb_buffer_pool_filename file.

Load process

Read the pages synchronously from the file read array and sort the data by (space,pageno) to buffer pool

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

You can view the status of dump/load through Innodb_buffer_pool_dump_status and Innodb_buffer_pool_load_status

In addition, performance_schema.events_stages_current is introduced to display load progress, and a progress information is updated for each load 32m.

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 represents the total number of page WORK_COMPLETED represents the current number of load page

The data format of the dump file is as follows

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

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

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.