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.
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 AskAzureDBforMariaDB@service.microsoft.com. Thank you!