Home / server / Questions / 667269
Accepted
David542
Asked: 2015-02-13 15:05:35 +0800 CST

Sending Data extremely slow or RDS

  • 772

I have the following two profiles for a basic SELECT STATEMENT:

select count(*) from mturk_completion;

Here are the two profiles:

# My Local machine, using a local db
Status                Duration
starting              0.000045
checking permissions  0.000006
Opening tables        0.000015
init                  0.000011
System lock           0.000006
optimizing            0.000004
statistics            0.000011
preparing             0.000009
executing             0.000002
Sending data          0.034015 ########
end                   0.000012
query end             0.000006
closing tables        0.000011
freeing items         0.000036
cleaning up           0.000010

And then here it is on my AWS ec2 large instance using an RDS mysql db:

starting                0.000068
checking permissions    0.000016
Opening tables          0.000028
init                    0.000024
System lock             0.000018
optimizing              0.000015
statistics              0.000022
preparing               0.000022
executing               0.000012
Sending data            0.446171 #########
end                     0.000036
query end               0.000018
closing tables          0.000023
freeing items           0.00009
cleaning up             0.000013

Most of the numbers are comparable except the Sending data part is more than ten times slower on the RDS instance!! What could account for that, and how would I fix this?

Here is the RDS instance information:

enter image description here

networking

1 Answers

  1. Best Answer

    Sending data

    The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.

    http://dev.mysql.com/doc/refman/5.0/en/general-thread-states.html

    I suspect disk access speed is the difference.

    In your first test it seems you have a local machine connecting to a local DB server with a local hard disk. In your second test you're connecting to a remote DB server with a remote hard disk (ie. EBS).

    EBS (which is what RDS uses for storage) is significantly slower than instance storage, which I imagine might still be slower than an unloaded local disk on your workstation (especially if you have an SSD).

    However in exchange for the performance loss you gain a number of benefits enabled by the abstracted nature of ELB:

    • The ability to take snapshots of your instance and launch new instances from your snapshot
    • The ability to resize your disk
    • The ability to change the performance of your disk (via provisioned IOPS)
    • The ability for your RDS instance to be transparently moved to a new host which might occur during a reboot or instance type change

    Which is why most people accept the performance penalty.

    If the performance loss is significant then you can try a few things:

    • provisioned IOPS
    • Run your own MySQL instance on EC2 using instance storage. I don't recommend this though as it would be very difficult to avoid data-loss if your instance gets stopped, and you won't be able to resize your disk if your data grows.
    • Run your own MySQL instance on EC2 using EBS in RAID
    • Scale horizontally, add read replicas if the IO on your master is becoming a bottleneck
    • Implement data caching within your application
    • 3