CODEWITHSUNDEEP
mysqlamazon-web-servicesamazon-ec2rds
Bradleyo
Bradleyo

Reputation: 35

Strange delay with RDS over MySQL

I need some help or direction as to where to look for a strange problem related to our mysql on RDS.

We have two environments, one on EC2 with a local installation of Mysql and one RDS mysql instance. Both have similar hardware and have the same data.

in both cases we run the same stored procedure, both run from a third source (EC2 instance)

On the EC2 instance With profiling enabled the query runs in 0.33 secs Query OK, 0 rows affected (0.33 sec)

show profiles;

0.00012025 call crmDisplayDonorProfile(3139, 21)
0.000048 drop temporary table if exists tblProfile
0.022021 create temporary table if not exists donCodes (cd char(1))
0.0000755 delete from donCodes
0.000111 insert into donCodes (cd) values (substring(strParse,1, 1) ) 0.0041805 drop temporary table donCodes
0.02201925 create temporary table if not exists donCodes (cd varchar(2)) 0.00007275 delete from donCodes
0.0040465 drop temporary table donCodes
0.02208925 create temporary table if not exists donCodes (cd char(1))
0.0001155 delete from donCodes
0.00015625 insert into donCodes (cd) values (substring(strParse,1, 1) ) 0.00342425 drop temporary table donCodes
0.02178325 create temporary table if not exists donCodes (cd varchar(2)) 0.0001055 delete from donCodes
0.00390075 drop temporary table donCodes
0.00042475 select * from tblProfile

0.00571425 drop temporary table tblProfile

On the RDS Instance

Query OK, 0 rows affected (1.84 sec)

0.00010275 call crmDisplayDonorProfile(3139, 36)
0.00006275 drop temporary table if exists tblProfile
0.02370575 create temporary table if not exists donCodes (cd char(1))
0.00007175 delete from donCodes
0.00010025 insert into donCodes (cd) values (substring(strParse,1, 1) ) 0.0038815 drop temporary table donCodes
0.0226735 create temporary table if not exists donCodes (cd varchar(2)) 0.00006475 delete from donCodes
0.00182825 drop temporary table donCodes
0.0253065 create temporary table if not exists donCodes (cd char(1))
0.0001185 delete from donCodes
0.00015075 insert into donCodes (cd) values (substring(strParse,1, 1) ) 0.00297025 drop temporary table donCodes
0.0191115 create temporary table if not exists donCodes (cd varchar(2)) 0.00009825 delete from donCodes
0.00280375 drop temporary table donCodes
0.0002995 select * from tblProfile

0.00460175 drop temporary table tblProfile

When I add up the queries from the profiling, the are basically the same.

So the question is where would the extra delay be coming from?

Any thoughts or directions would be appreciated.

Upvotes: 0

Views: 509

Answers (1)

Naveen Vijay
Naveen Vijay

Reputation: 16482

I suggest you to further look into your notion of "Similar Hardware".

Given that the local EC2 instance has ZERO latency, however RDS might have bit more of the latency; nevertheless that latency effect has wouldn't contribute to the slower query performance.

Can you re-compare these factors in your local EC2 MySQL vs RDS

  1. Data Size ( Volume & Their Schema )
  2. MySQL Versions
  3. IOPS in the EC2 Instance vs the RDS
  4. RDS Volume size ( Bigger Provisioned Storage would improve the I/O )
  5. Multi AZ Setup for RDS ( would take further more additional overhead )

As it is speaking comparing your EC2 based locally run MySQL and RDS based MySQL is not straight forward Apple to Apple to comparison. It more like Comparing Green Apple and Regular (Red) Apple, both are apples but they are totally different fruits all together.

Upvotes: 2

Related Questions