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

This article mainly introduces "how to use buffer pool to dynamically resize the new features of innodb". In the daily operation, I believe that many people have doubts about how to use buffer pool to dynamically resize the new features of innodb. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to use buffer pool to dynamically resize the new features of innodb." Next, please follow the editor to study!

Buffer pool dynamic resizing is supported. Each buffer_pool_instance consists of the same number of chunk (chunks array), and each chunk memory size is innodb_buffer_pool_chunk_size (actually larger than 5%, which is used to store block information in chuck). Buffer pool increases and shrinks dynamically in terms of innodb_buffer_pool_chunk_size. Innodb_buffer_pool_size should always be a multiple of innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances before and after adjustment.

The same buffer pool dynamic resizing is returned immediately by the backstage thread buf_resize_thread,set command. You can view the running status of the adjustment through InnoDB_buffer_pool_resize_status.

Resize process

If AHI is enabled, disable AHI

If it's shrinking memory,

Start resize

If AHI is disabled, it is enabled at this time.

Lock all instance's buffer_pool,page_hash

Shrink pool: frees memory to be shrunk in units of chunk

Clear withdraw list buf_pool- > withdraw

Increase pool: assign a new chunk

Reassign buf_pool- > chunks

If you change / shrink more than 2 times, the page hash will be reset and the bucket size will be changed.

Release the buffer_pool,page_hash lock

If you change / shrink more than 2 times, it will restart the memory structure related to buffer pool size, such as lock system (lock_sys_resize), AHI (btr_search_sys_resize), data field (dict_resize), etc.

Calculate the number of chunk to be shrunk, and delete the specified number of chunk from the tail starting with chunks.

Lock buf_pool

Remove the page of the chunk to be deleted from free_list and put it in the list buf_pool- > withdraw

If the page of the chunk to be deleted is a dirty page, brush it dirty

Reload the page to be deleted in LRU, remove it from LRU, and re-get page's old page from the free list and put it in the list buf_pool- > withdraw

Release the buffer pool lock

If the chunk pages to be shrunk is not fully collected, repeat 2-6

As can be seen from above, it is relatively easy to expand memory than to reduce memory. When shrinking memory, if you encounter a transaction that has not been committed and occupies the page to be shrunk, causing the shrink to keep retrying, error log will print this retry information, including transaction information that may refer to the problem. To avoid frequent retries, the interval between each retry increases exponentially.

In the above steps, the resize phase buffer pool will not be available, this phase will lock all buffer pool, but this phase is a memory operation, the time is relatively short. The shrinking memory phase can be time-consuming and has some impact, but each time it is locked in units of instance. Generally speaking, buffer pool dynamic resizing has little impact on the application.

Impact of reloading pages to be deleted in LRU

During the search process, the page saved by the btr cursor may have been reloaded, or the root page saved by the adaptive hash may have been reloaded and need to be read again.

At this point, the study on "how to use buffer pool to dynamically resize the new features of innodb" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.