How do I reduce the snapshot restore time of my Amazon RDS for MySQL instance?

Short description

Long snapshot restore times are typically caused by long database recoveries. The database recovery time is based on the instance workload when the snapshot occurs. If you activated binary logging on your source DB instance, then recovery and snapshot restore time might take longer.

When you restore a snapshot, Amazon RDS performs a recovery process and the MySQL DB engine is started on a new DB instance. Based on the recovery session time, the new DB instance can take up to a few minutes to start. For more information, see InnoDB crash recovery on the MySQL website.

Note: Until the volume is fully restored from Amazon Simple Storage Service (Amazon S3), you experience some latency or lazy loading. For more information, see Restoring from a DB snapshot.

For a point-in-time recovery (PITR), see Why is it taking a long time to perform a point-in-time recovery of my Amazon RDS for MySQL instance?

Resolution

To reduce the snapshot restore time, use the following best practices:

• Schedule a backup window, or take a manual snapshot of your DB instance during non-peak hours. When you take a snapshot, activities that you perform on the source DB instance affect the database and snapshot recovery times.
• If a source instance uses the magnetic storage type during a snapshot, then the restored instance is in the Modifying status. For example, volume change occurs when you restore a DB snapshot as a General Purpose SSD gp2 or Provisioned IOPS storage type. During this time, you can connect to an Amazon RDS instance, but you might experience performance degradation.
• Temporarily restore your instance to a higher DB instance class. When you upgrade the DB instance class, you temporarily gain more resources and crash recovery times lessen.  After the snapshot restore completes, you can scale down the instance class.

To reduce the snapshot restore time for snapshots with binary logging turned on, use the following best practices:

• To decrease binlog recovery times, avoid large transactions and large binlog files. Binary logs with a large amount of data experience an increased binlog recovery time. Also, snapshot restore times increase.
• Use the appropriate transaction size. Large transactions are written to the binary log file at all at once and aren't split up between different files. As a result, the binary log file is large and the crash recovery time increases.
• Use the appropriate binary logging format. Binary logging formats can affect the size and efficiency of the crash recovery. For more information, see Usage of row-based logging and replication on the MySQL website. For more information about binary logging formats, see Advantages and disadvantages of statement-based and row-based replication on the MySQL website.

Note: To complete lazy loading faster, use an Amazon RDS instance type and Amazon Elastic Block Store (Amazon EBS) volume with higher throughput. After lazy loading completes, you can downgrade the instance class type and decrease the IOPS and throughput value.