As described in the MySQL 5.7 manual here, we can now resize the buffer pool without restarting the mysqld process starting with MySQL 5.7.5.

You can now use the "SET GLOBAL innodb_buffer_pool_size = xxxx" command which causes a resizing job to begin in background. We can monitor the progress and state of the resizing work using the Innodb_buffer_pool_resize_status variable and the server error log.

In order to minimize the impact of the resulting memory allocation/freeing process, thus avoiding huge memory copy tasks which would then block other transaction processing related tasks, the resizing is done in fixed size chunks. And because InnoDB is designed to use equally sized buffer pool instances, the total buffer pool size is (upper) aligned to a multiple of [innodb_buffer_pool_instances × innodb_buffer_pool_chunk_size].

All allocate/free/search calls for pages within the buffer pool are only blocked during the critical path in the resizing operation. The critical path consists of the following parts:

• Allocating memory from the OS and freeing memory back to the OS
• Resizing internal hash tables (page_hash, zip_hash), if needed

The length of the time for the critical path is largely dependent on the OS memory manager’s performance.

The preparation phase for a resizing operation that shrinks the buffer pool may potentially take a long time, but it doesn’t block other concurrent transaction processing related tasks. The preparation phase consists of the following parts:

• Reduce the number of pages in order to fit within the new smaller size
• Relocate the pages from chunks that must now be freed

Naturally if there will now be too many dirty pages for the now smaller buffer pool then they will need to be flushed and then freed. This work could cause significant disk IO and thus could potentially indirectly interfere with other transaction related tasks while it runs.

Also of note, if a transaction has locks on any of the pages in the chunk to be freed, then the relocation of those pages should wait for the transaction end. So high transaction throughput or long running transactions can potentially block the buffer pool resize operation. Any resulting long waits are logged to the server error log file along with information about the suspected transactions that are causing the operation to wait/block.

Lastly, there is also one limitation to note. During the buffer pool resizing process the adaptive hash index (AHI) is disabled. This is done because the AHI uses buffer pool pages and when active it’s very difficult to relocate the necessary pages.

That’s all for now. I look forward to your feedback on this new feature! If you have any questions or encounter any bugs, please do let us know by opening a support ticket or filing a bug. Thank you for using MySQL!