Why are database queries running so much slower on AWS RDS?

Solution 1:

I took the advice from Tim in the comments and signed up for AWS Support so I could chat with someone about this. The explanation was:

Aurora is built and tailored for Highly concurrent workloads, Single-threaded are unlikely to result in the impressive results as also the concurrency levels in your benchmarks need to be verified.

So if we are in the works of 1 query, its highly unlikely that Aurora would perform as fast as your local machine

A bit more chatting back and forth, then I retried with RDS MySQL instead of Aurora, and it works! Almost exactly as fast as my local machine, with much cheaper hardware.

Solution 2:

In my case I noticed that the database indexes are not migrated to RDS. I added indexes and it’s working fast. Also MySQL query cache is OFF by default in RDS. This won't help the performance of your initial query, but it may speed things up in general. You can set query_cache_type to 1 and define a value for query_cache_size. I also changed the thread_cache_size from 8 to 24 and innodb_io_capacity from 200 to 1900 don't know if it helps you. (After adding indexes I turned off the query cache) Also creating AWS DB Parameter Groups helped me a lot with configuring and tuning DB variables. Here you can read more: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html