Why is it taking a long time to perform a point-in-time recovery of my Amazon RDS for MySQL instance?
Last updated: 2021-08-16
I've initiated a point-in-time recovery (PITR) in Amazon Relational Database Service (Amazon RDS) for MySQL, and it's taking longer than expected. Why is this happening?
Point-in-time recovery (PITR) is the process of restoring a database to the state that it was in at a specified date and time. When you initiate a PITR, transactional logs are applied to the most appropriate daily backups to restore your DB instance to a specified time.
Best practices for avoiding a long point-in-time recovery
To avoid a long point-in-time recovery, follow these best practices:
- Minimize snapshot restore times. Snapshot restores are initiated as part of the point-in-time recovery process. A longer snapshot restore can contribute to a longer point-in-time recovery session. For more information, see Why is it taking so long to restore a snapshot of my Amazon RDS for MySQL DB instance?
- A log apply process can take more time depending on the number of logs to be applied. To reduce the number of logs to be applied, consider taking a manual snapshot in-between the automated backups. As point-in-time recovery automatically selects automatic or manual snapshots created near the PITR time, having intermediate manual snapshots can reduce the number of logs to be applied. If you're dealing with a large volume of changes, take a manual snapshot every 3-4 hours.
- Use smaller transactions and run the COMMIT command more frequently.
- To run a large transaction, take a snapshot before and after the large transactions. However, note that transactions that are larger than the max_allowed_packet parameter cause PITR to fail.
- Create a disaster recovery strategy.
Point-in-time recovery failures
The following scenarios will cause point-in-time recovery to fail:
- DB instances that are in a "storage full" state.
- Using MyISAM tables in MySQL. MyISAM storage engines do not support reliable crash recovery processes. The MyISAM tables prevent the point-in-time recovery process from working properly.
- Using temporary tables, distributed transactions, and in-memory tables.