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

Short description

When your source database (DB) instance has a high workload during the snapshot, you might experience long snapshot restore times. If you activated binary logging on your source DB instance, then recovery and snapshot restore times might take longer.

When you restore a snapshot, Amazon RDS performs a recovery process and starts the MySQL DB engine 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 fully restores from Amazon Simple Storage Service (Amazon S3), you experience latency from lazy loading. For more information, see Restoring to a DB instance.

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

Reduce snapshot restore time

Take the following actions:

• Schedule a backup window, or take a manual snapshot of your DB instance during non-peak hours. Activities that you perform on the source DB instance increase 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, this status change occurs when you restore a DB snapshot as a General Purpose SSD gp2 or Provisioned IOPS SSD 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 gain more resources and reduce crash recovery times. After the snapshot restore completes, scale down the instance class.

Reduce snapshot restore time with binary logging activated

Take the following actions:

• Avoid large binary log files. Large binary logs increase binary log recovery time and snapshot restore time.
• Avoid large transactions. The MySQL engine writes large transactions to the binary log file all at once and doesn't split them between different files. As a result, the binary log file grows large and crash recovery time increases.
• Use the correct binary logging format. Binary logging formats 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.

Use faster lazy loading to reduce restore time

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, downgrade the instance class type and decrease the input/output operations per second (IOPS) and throughput value.

To mitigate the effects of lazy loading on tables that you require quick access to, perform operations that involve full-table scans such as SELECT *. This allows Amazon RDS to download all the backed-up table data from Amazon S3. For more information, see Restoring from a snapshot.