0

I have a RDS MySQL Aurora DB(db.t2.medium) hosted in us-east region

A select query returning around 25k(5 MB) rows is taking around 3 mins.
(When executed by my back-end(same VPC as DB) and executed by me via MySQL workbench from India)

Same query on the dump of same db on my local is taking around < 2 seconds(using MySQL workbench) and is returning same no of rows(25k).

I've checked out the CPU utilisation metrics of my db instance and there are no spikes/surprises there.

My db is hosted in us-east region and I am executing the query from India using MySQL workbench.
One thing to note that when the query is executed by my backed on db(they are in the same VPC) still the query is exactly slow.

When i hit the query from India via MySQL workbench to my RDS instance the duration time is under a second but the fetch time is around 3 mins(with or without VPN). duration vs fetch

When i hit the same query on dump of same db on my local, the duration is almost same but the fetch time is around 1 second(same no of rows).

I am not sure why there is no much of difference is fetch time?

folan
  • 215
  • 1
  • 8
  • Please show us the Create Table Statement you use for the table and the sql-statement. My guess is either missing indexes or the IOPS are exhausted and you're being throttled. This is not enough information for any useful answer. – Maurice Dec 15 '21 at 08:17
  • Even if we're missing Indices then how come the results are faster on my local environment and Missing indices should increase the duration time(query execution) which is almost same for both queries. The delay is caused by the fetch time (date transfer) between client and db. How can i check if the IOPS are exhausted? – folan Dec 15 '21 at 10:12
  • Don't use Workbench for production work. It is designed to give you a few rows, not 25K rows. – Rick James Dec 15 '21 at 22:04
  • Since WHEN could you expect LOCAL to be as slow as 5,000 miles away? Please post TEXT results of A) SHOW CREATE TABLE table_name; and B) SHOW TABLE STATUS WHERE name LIKE 'tablename(s)'; and C) your query - for analysis from you LOCAL and AWS hosted data. – Wilson Hauck Dec 17 '21 at 02:28

0 Answers0