InnoDB is a storage engine for the MySQL database management system. InnoDB manages a buffer pool, which is a dedicated storage zone that’s used to cache data and indexes within memory. Because the data remains readily available in memory, this approach significantly accelerates retrieval of frequently accessed information, easily surpassing the time required for disk-based retrieval.
When a MySQL server stops or restarts, data cached in the buffer pool is lost. However, MySQL includes a new feature that enables you to dump the contents of the buffer pool before you shut down the server. When the server restarts, you can reload these cached contents into memory. You also have the flexibility of dumping the buffer pool at any given time to reload subsequently.
To view details regarding the buffer pool on your system, run the command:
SHOW ENGINE INNODB STATUS
A sample set of results from running this command appears in the following image:
In this instance, the output shows that the buffer pool consists of 84142 database pages.
Note that when you perform a buffer pool dump to disk, only the database pages are saved. When the server restarts, the information stored in these database pages is automatically reloaded into memory.
This blog post explores the effect on memory consumption of enabling and disabling parameters related to the buffer pool memory dump, including:
|
Parameter |
Purpose |
|
innodb_buffer_pool_load_at_startup |
Restores the buffer pool when starting MySQL. |
|
innodb_buffer_pool_dump_at_shutdown |
Saves the buffer pool when MySQL is shutdown or restarted. |
These parameters appear on the Server parameters page for your flexible server, as shown in the following image:
You can use these parameters to help during a during a restart when the buffer pool is still warm and contains all of the active buffers.
Scenarios
We’ll look at memory usage as we disable and enable the memory dump parameters in Non-High Availability (HA) and HA environments. This blog post evaluates different use cases, because there are scenarios in which we want to ensure that the buffer pool cache remains “hot” (the buffer pages remain in memory) even after a planned/unplanned restart of the database server.
In the first set of scenarios, I’ll disable and enable the buffer pool performance parameters innodb_buffer_pool_load_at_startup and innodb_buffer_pool_dump_at_shutdown to determine memory behavior in Azure Telemetry.
In the second set of scenarios, I’ll show that in HA environments, the buffer cache is always retained during any failover, regardless of whether the parameters are enabled or disabled.
Start up and shut down scenarios
In different working environments, production databases aren’t usually shut down because the resulting downtime can lead to problems such as performance issues. In addition, the buffers in buffer pool cache can be lost and may need to be restored from disk on demand, degrading application performance. MySQL now includes a parameter you can use to back up the buffer pool cache, which you can then restore after the database restarts. This capability can help for both planned and unplanned database instance restarts.
In the following sections, I’ll disable and enable the memory dump parameters to check how the memory in the buffer pool cache behaves and how it’s used in telemetry.
Parameters disabled
In this scenario, with the two buffer pool performance parameters (innodb_buffer_pool_load_at_startup and innodb_buffer_pool_dump_at_shutdown) disabled, the percentage of used memory drops from 45% to10% as the database server restarts.
This drop shows that the buffer pool is purged of all active pages and that the pool is refreshed and ready to accept pages based on the new active transactions.
Parameters enabled
In this scenario, with the memory parameters (innodb_buffer_pool_load_at_startup and innodb_buffer_pool_dump_at_shutdown) enabled, restarting the MySQL flexible server does not impact memory consumption, which remains at 45% regardless of the restart.
This means that the buffer pool is hot, and they have the buffer pages which were in memory even after DB Service restart.
High availability scenarios
In an HA environment, there are chances of failover to secondary DB if there are any issues in primary DB triggering a failover. An application would always want its buffer cache to be hot so that queries does not take time to pull buffers from disk. In the next few scenarios, we will check how memory behaves in HA (High Availability) enabled servers. We will perform a failover, making the memory parameters ON and again perform a failover.
Manual failover with parameters disabled
On an HA-enabled MySQL flexible server with the memory dump parameters disabled, after triggering a manual failover, memory usage remains at 45%, the same as before the restart operation.
This is because during failover, systems retain a backup of the memory regardless of whether the dump parameters are disabled.
Manual failover with Memory Parameter ON
In our last scenario, after setting the value of memory dump parameter to ON in previous step, we triggered another failover, but even afterward, there isn’t a change in memory usage. This confirms that regardless of whether the dump parameter set to ON or OFF, during a failover event, the buffer cache is always backed up, ensuring that applications never degrade in performance as a result.
Demo
Do watch the clip below from our Monthly Webinar of July 2024 where we demoed the behavior on Azure Database for MySQL - Flexible server with the changes to these parameters:
New Buffer Pool metrics
We have added following metrics to help you track buffer pool usage and adjust the buffer pool size and manage memory utilization of your Azure Database for MySQL - Flexible server better.
The buffer pool is divided into pages, typically 16 KB in size. These pages can hold multiple rows of data. When a page is accessed, it is loaded into the buffer pool. If the buffer pool is full, the least recently used pages are evicted to make room for new ones.
- InnoDB Buffer Pool Pages Data - This metric indicates the number of pages containing data (both dirty and clean).
- InnoDB Buffer Pool Pages Dirty - This metric shows the number of pages that have been modified (dirty pages) but not yet written to disk.
- InnoDB Buffer Pool Pages Free - This represents the number of free pages in the buffer pool.
- InnoDB Buffer Pool Read Requests - This metric counts the number of logical read requests made to the buffer pool. When a query needs data, it first checks the buffer pool. If the data is already in the buffer pool, it can be read directly from memory, which is much faster than reading from disk.
- InnoDB Buffer Pool Reads - This metric counts the number of physical reads from disk when a page is not found in the buffer pool. Essentially, it tracks the instances where the data had to be fetched from disk because it was not available in the buffer pool.
Significance
- A high number of Buffer Pool Read Requests indicates that the buffer pool is being effectively utilized to serve data from memory, reducing the need for slower disk I/O operations.
- Ideally, Buffer Pool Reads should be low compared to the Buffer Pool Read Requests. A high number of Buffer Pool Reads indicates that the buffer pool is not large enough to hold frequently accessed data, leading to more disk I/O and potentially slower performance.
Optimizing Buffer Pool
You can optimize the buffer pool and reduce the number of physical reads by:
- Buffer Pool Size: Allocating as much memory possible to the buffer pool to accommodate more data and reduce the need for disk reads while balancing the memory available for the normal operation of the other server components.
- Monitor and Tune: Regularly monitor these metrics and adjust the buffer pool size based on the workload and available overall memory.
- Efficient Queries: Optimize queries to access data more efficiently, reducing the overall load on the buffer pool.
Conclusion
Based on the information provided above, it’s clear that you can use the InnoDB Buffer Pool Parameters innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup to influence application performance. While both parameters are enabled by default, you can disable them, but there will be a significant impact on application performance as the buffer pool will be purged and the pages must be reloaded into memory, which is an expensive operation. It is recommended to ensure that these parameters are always enabled to ensure that your buffer pool retains the required buffer pages, even after a server restarts.
If you have any feedback or questions about the information provided above, please leave a comment below or email us at AskAzureDBforMySQL@service.microsoft.com. Thank you!