Mysql benchmark: RDS vs EC2 performance

the setup: 1 m1.small ec2 instance vs 1 db.m1.small rds instance, tests are being run from the m1.small instance. The goal is to determine how the site will perform when moving the database from localhost to a remote instance.

I used sysbench for mysql benchmarks. On a linux server running ubuntu 10.04 you can simply install it with the following command(it’s obvious but just in case):

sudo apt-get install sysbench

The first tests performed were m1.small EC2 instance running mysql-server 5.1.41-3ubuntu12.8 VS RDS instance type db.m1.small running mysql server 5.1.50. The test database had been set to 10 000 records, number of threads = 1, test oltp.

sysbench --test=oltp --mysql-host=smalltest.us-east-1.rds.amazonaws.com --mysql-user=root --mysql-password=password --max-time=180 --max-requests=0 prepare
sysbench --test=oltp --mysql-host=smalltest.us-east-1.rds.amazonaws.com --mysql-user=root --mysql-password=password --max-time=180 --max-requests=0 run

The results

m1.small EC2 instance db.m1.small RDS instance
OLTP test statistics:
queries performed:
read: 263354
write: 94055
other: 37622
total: 395031
transactions: 18811 (104.50 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 357409 (1985.56 per sec.)
other operations: 37622 (209.01 per sec.)
Test execution summary:
total time: 180.0044s
total number of events: 18811
total time taken by event execution: 179.7827
per-request statistics:
min: 4.04ms
avg: 9.56ms
max: 616.04ms
approx. 95 percentile: 38.42ms
OLTP test statistics:
queries performed:
read: 188230
write: 67225
other: 26890
total: 282345
transactions: 13445 (74.67 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 255455 (1418.74 per sec.)
other operations: 26890 (149.34 per sec.)
Test execution summary:
total time: 180.0573s
total number of events: 13445
total time taken by event execution: 179.9174
per-request statistics:
min: 9.08ms
avg: 13.38ms
max: 904.58ms
approx. 95 percentile: 20.99ms

As you can see the EC2 can perform 40% more transactions than the RDS instance. Nothing unexpected so far.

Time to move on and increase the number of threads to 10

m1.small EC2 instance db.m1.small RDS instance
OLTP test statistics:
queries performed:
read: 264866
write: 94545
other: 37818
total: 397229
transactions: 18899 (104.97 per sec.)
deadlocks: 20 (0.11 per sec.)
read/write requests: 359411 (1996.22 per sec.)
other operations: 37818 (210.05 per sec.)

Test execution summary:
total time: 180.0462s
total number of events: 18899
total time taken by event execution: 1799.9289
per-request statistics:
min: 4.08ms
avg: 95.24ms
max: 2620.70ms
approx. 95 percentile: 445.91ms

OLTP test statistics:
queries performed:
read: 343812
write: 122772
other: 49109
total: 515693
transactions: 24551 (136.18 per sec.)
deadlocks: 7 (0.04 per sec.)
read/write requests: 466584 (2588.13 per sec.)
other operations: 49109 (272.41 per sec.)

Test execution summary:
total time: 180.2788s
total number of events: 24551
total time taken by event execution: 1801.8298
per-request statistics:
min: 13.41ms
avg: 73.39ms
max: 1126.02ms
approx. 95 percentile: 143.83ms

In this test the small RDS instance is faster than the EC2, 136 vs 105 transactions per second. I’ve also benchmarked a large RDS instance (the next one available after db.m1.small) and it got 185 transactions per second. Quite good, but the price is 4x higher.

The next test was performed vs a 10 million records, 16 threads. This time I only benchmarked a small and a large RDS instance. The large instance managed to do 228 transactions per second while the small one got a decent score of 127 transactions. One thing I noticed during this test is that the small instance started to use it’s swap, while the large one did not have this issue. This is probably due to the fact that 10M records db is aprox 2.5GB and the small RDS only has 1.7GB of RAM.

So if you are planing to grow and want an easy way to do it, switching your database to its own RDS is one of the first things you should consider. One of the immediate effects you will notice is that the CPU usage on the EC2 instance will be greatly reduced, leaving more power for the web server. You can easily increase the size and capacity of the RDS instance with just a few clicks. The backups are done automatically, which is great considering how many times I had to recover databases.

Diogo Gonçalves

Hi there… I’m curious about your my.cnf.
I’m using a m1.small running apache and mysql server but it is incredible slow.
On htop I can see the problem is the over-usage of CPU and most of time is the mysql using it.
Could you share your configuration with us?
Thank you.

Octavian

Hi,

The benchmarks are almost 2 years old, I do not have the my.cnf file from that time. As far as I can remember I didn’t make any changes to my.cnf, I think I’ve used the default. CPU usage is a big issue with small instances, even micro might perform better in this regard. Try to enable slow logs, see processlist, reduce the number of max connections and use mysqltuner.pl to optimize the server a bit.

Hope this helps!

Vincent Kellers da Silveira

Great article. I wasn’t quite sure on what application to use in order to perform the mysql benchmark, and sysbench seems spot-on.

Quora

Is there any reason to use Amazon RDS when setting up WordPress in Amazon EC2 Cloud Hosting?…

When there is enough money, I would always use RDS. It is completely hassle-free, and gives you a lot of power. (The price of RDS is 125% the price of equivalent EC2 hardware.) Deepak already mentioned how easy it is to scale. And the performance is re…

Quora

Amazon RDS Large DB instance is costly and Multi-AZ deployment it costlier which makes Relational Database as a Service from Amazon is the costliest offering for SMBs?…

RDS is dirt-cheap, if you ask me. Building a MySQL solution where you can switch from ‘normal’ to highly-available in minutes, scale up and down whenever you want and have live backups with restore-to-point-in-time functionality is VERY expensive. (A…

Your email address will not be published. Required fields are marked *