MySQL RDS reads slow?

If you are experiencing slow sql reads from your RDS instance such as the below count(*) query:

mysql> select count(*) from customers;

  

+----------+

| count(*) |

+----------+

|   500000 |

+----------+

1 row in set (14 min 53.62 sec)

Then check your RDS Cloudwatch metrics on "Read IOPS". If the chart looks like below:-

 

then most likely you are using standard EBS volumes for your RDS instance. Typically, standard EBS volumes only support 100 iops/sec and as we can see from the above, 2000 count/sec is way over the limit. Some of the larger instance types such as m1.xlarge support EBS optimized IOPS which results in a dedicated channel from EC2 instance to RDS. If you are using such instances then you can improve the read performance by modifying the instance and enabling provisioned IOPS such as below:

NOTE - as best practices demands, always make a DB snapshot before you modify the RDS instance. Also, note that "Provisioned IOPS" value is multiple of "Allocated Storage". So if your allocated storage is 100G then IOPS limit is 1000.

Once you make the above change, you will see the count(*) query time go down as below:-

mysql> select count(*) from customers;

  

+----------+

| count(*) |

+----------+

|   500000 |

+----------+

1 row in set (1 min 22.5 sec)