MySQL Cache Warming for Amazon RDS

By Jeff Barr

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_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
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
InnoDB Cache Warming
in the Amazon RDS User Guide.


: MySQL Cache Warming for Amazon RDS