I have a MySQL database hosted on an AWS RDS instance. The database has many tables and I observed the following:
I picked a table with about 1-2 million records that is indexed on a created_at datetime column. When I execute a SQL query something along the lines of picking records between a specific start date and end date on this column, the query takes a long time to complete(about 10 mins).
However, when I take/restore an AWS RDS instance from a snapshot, it hardly takes 5-10 mins. I'm curious how taking/restoring from snapshots are so fast? Does is not have to read through the whole data in the database to take/restore snapshots?
Forgive me if my question is naive. I'm a newbie. Thanks for the input 🙂
Best Answer
In this specific case, it's because the sequential restore of a blob of data is faster than the traversal in order that MySQL does it in, and because MySQL could be CPU bottlenecked during some parts of the operation. Additionally, snapshot restores in AWS may not be constrained by the storage IOPS you are paying for.
In general, restoring a snapshot can be incredibly quick - with ZFS a rollback to a snapshot doesn't require restoring any data at all, it involves only marking newer data blocks as discarded. This takes milliseconds to seconds, rather than minutes.