AWS RDS Slow After Restore

AWS RDS database that was recently restored from an RDS snapshot will be slower than a usual database. In a restored database, a query that would normally take 1-2 seconds can run for more than a minute and even time out. Why does this happen, and what to do about it? See below.

Why?

The reason is that RDS snapshots are stored in S3 and when you restore a database from a snapshot AWS doesn’t copy the data from S3 to the RDS DB’s underlying EBS volume. Instead, the data stays in S3 until the database tries to access it. Only when you run a query for the first time does the queried data get copied to the RDS DB. This lazy loading is what causes the slowness.

More info in AWS docs here and here.

What to do?

To get rid of the huge delays we need to get all the data from S3 to RDS. We can let the lazy loading work, and sometime, eventually the data will get copied over. However, that is often not acceptable. Luckily, we can take matters into our own hands and force data loading by querying all of it.

You may run a set of SELECT * FROM table queries. Alternatively, with PostgreSQL, for example, you can run a vacuum analyze which will perform full-table scans on everything:

vacuum(analyze, DISABLE_PAGE_SKIPPING);

For smaller databases (<100GB) this won’t take much time. However, for bigger ones, a vacuum may take many hours. For example, for a db.m5.2xlarge DB instance with 600GB of data, it takes about 8 hours to complete. Once it finishes, the slowness and delays disappear.

Good or bad?

When I discovered this behavior, it was an unpleasant surprise for me, and it motivated me to write this post. I’d appreciate it if AWS advertised it a little more.

I wasn’t sure what to think about this design at first. However, later I figured that this was a good feature. AWS lets us create a DB with hundreds of GBs of data within just 10-15 minutes and start using it. Yes, the DB is slow, but it’s working nonetheless. If we were to do the classic restore with the DB’s native tools, it would take far more than 15 minutes in most cases.