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!

Post navigation

3 thoughts on “Resizing the InnoDB Buffer Pool Online”

Hello, since this is good sum-up post about online buffer pool resize, I translated into Japanese. If you have any problem, please let me know.
分かりやすい仕組みの解説ありがとうございます。記事を日本語に訳してみたのですが、ライセンスの問題等ありましたら削除など対応しますのでお知らせください。