MySQL Cache Warming for Amazon RDS

Among many other responsibilities, a relational database system must make efficient use of main memory (RAM) for buffering and caching purposes. RAM is far faster and easier to access than SSD or magnetic storage; a properly sized and tuned cache or buffer pool can do wonders for database performance.

Today we are improving Amazon RDS for MySQL with support for InnoDB cache warming. When an Amazon RDS DB instance that is running MySQL is shut down, it can now be configured to save the state of its buffer pool, for later reloading when the instance starts up. The instance will be ready to handle common queries in an efficient fashion, without the need for any warm up.

This feature is supported for RDS DB instances that are running version 5.6 (or later) of MySQL. To enable it, simply set the innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup parameters to 1 in the parameter group for your DB instance.

Users of MySQL version 5.6.19 and later can manage the buffer pool using the mysql.rds_innodb_buffer_pool_dump_now, mysql.rds_innodb_buffer_pool_load_now, and mysql.rds_innodb_buffer_pool_load_abort stored procedures.

Once enabled, the buffer pool will be saved as part of a normal (orderly) shutdown of the DB instance. It will not be saved if the instance does not shut down normally, such as during a failover. In this case, MySQL will load whatever buffer pool is available when the instance is restarted. This is harmless, but possibly less efficient. Applications can call the mysql.rds_innodb_buffer_pool_dump_now stored procedure on a periodic basis if this potential innefficiency is a cause for concern.

DB Instances launched or last rebooted before August 14, 2014, will need to be rebooted to gain access to this new feature. However, no action is required for DB Instances launched or rebooted on or after August 14, 2014. To learn more, take a look at MySQL InnoDB Cache Warming in the Amazon RDS User Guide.

— Jeff;