The InnoDB buffer pool acts as a powerhouse for MySQL, caching frequently accessed data and index pages in memory to accelerate query performance. In this blog post, we will go through the process of InnoDB buffer pool resizing online, covering why it is important to monitor its progress and how to monitor it.

Importance of monitoring the InnoDB buffer pool resize

Since MySQL 5.7.5, we have been able to resize the InnoDB buffer pool online, which allows administrators to adjust the buffer pool size dynamically without requiring a server restart, offering a flexible solution for optimizing system resources.

You can dynamically change the buffer pool using the following command:

In short, we will review what major steps are taken while doing the buffer pool resize. InnoDB’s background threads take care of making this change.

When the buffer pool size is increased, the following steps are considered:

  •   Adds pages in chunks (chunk size is defined by innodb_buffer_pool_chunk_size)
  •   Converts hash tables, lists, and pointers to use new addresses in memory
  •   Adds new pages to the free list

While these operations are in progress, other threads are blocked from accessing the buffer pool and queries will be paused.

When the buffer pool size is reduced:

  •   Defragment the buffer pool and withdraws (frees) pages
  •   Removes pages in chunks (chunk size is defined by innodb_buffer_pool_chunk_size)
  •   Converts hash tables, lists, and pointers to use new addresses in memory

Of these operations, only defragmenting the buffer pool and withdrawing pages allow other threads to access the buffer pool concurrently.

Since the operation is not causing 100% downtime but is a blocking type, it becomes important for us to know the status of the operation.

Monitoring buffer pool size changes

Prior to MySQL 8.0.31, we can do this by checking the innodb_buffer_pool_resize_status after modifying the innodb_buffer_pool_size. This status variable reports a string value indicating buffer pool resizing progress.

Starting from MySQL 8.0.31 monitoring the online innodb_buffer_pool resize operation is even better and introduces exciting improvements, particularly in monitoring the progress of online buffer pool resizing operations.

Starting from 8.0.31, two new variables were added, which are innodb_buffer_pool_resize_status_code and innodb_buffer_pool_resize_status_progress. These variables report the numeric values, making it easier for administrators to keep a close eye on the progress of buffer pool resizing operations.

Query to monitor InnoDB buffer pool resize status:

Using PMM to track the InnoDB buffer pool resize status:

The Percona Monitoring and Management tool also collects and tracks the related configuration variables which enable us to visualize the same. The following is a sample PMM dashboard that shows the multiple buffer pool resize operations during my tests.

PMM Buffer Pool

Buffer pool resizing operation status codes

Buffer pool resize monitoring in action

Let’s dive into a practical example to demonstrate how to leverage these variables. Consider a scenario where you want to resize the InnoDB buffer pool to accommodate a growing dataset.

Step 1: Check current status

Before initiating any changes, let’s understand the current status of the buffer pool resizing operation by executing the following query:

This query provides a snapshot of the current status and progress of the buffer pool resizing operation. From the above output, we can confirm that there is no buffer pool resize operation in progress.

Step 2: Initiate resizing

Let’s change the InnoDB buffer pool from default 128MB to 1G.

Step 3: Monitor progress

After resizing the InnoDB buffer pool online, execute the monitoring query periodically to track the progress of the resizing operation. Keep an eye on the innodb_buffer_pool_resize_status_code and innodb_buffer_pool_resize_status_progress values. The status code provides insights into the current state of the resizing operation, while the progress value indicates the completion percentage.

Important: MySQL error log provides additional information which includes the percentage progress value of each status code. This is available only for the log_error_verbosity=3.

Conclusion

With MySQL variables like innodb_buffer_pool_resize_status_code and innodb_buffer_pool_resize_status_progress from MySQL 8.0.31, administrators can effectively monitor online InnoDB buffer pool resizing and manage its online operations.

Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!

 

Try Percona Distribution for MySQL today!

Subscribe
Notify of
guest

1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Jignesh Chauhan

Very detailed and informative insight,Brijesh. Thanks for sharing this information.

1
Reply