AWS benchmark of MySQL 5.5 RDS vs EC2

MySQL benchmark of AWS RDS vs EC2 with complete results and instructions on how to recreate on your own for under $0.25.

Update 9/15/2013 – Added bar charts to compliment tables.

I found a couple blog posts with benchmark data specifically about MySQL on EC2 instance vs RDS.

• http://getasysadmin.com/2011/02/mysql-benchmark-rds-ec2-performance/
• http://blog.observu.com/2011/05/rds-vs-mysql-on-ec2-benchmark/

Both posts use a tool call sysbench, a great tool for benchmarking MySQL performance on cloud systems.

The blog posts above don’t specify the MySQL version being tested. To add to the patchwork of the internet, I went ahead and ran my own set of tests against an AWS EC2 m1.small Instance of Ubuntu 12.04 64-bit running MySQL 5.5 vs an AWS RDS small instance of MySQL 5.5.

Tests:

1. The first test is with a relatively small database (228MB) that can be entirely loaded into memory.
2. The second test is with a database that is ~ 2.2GB, too big to fit into memory on a small instance.
3. I then applied a standard tuning setting to the EC2 instance to see how that compares (set innodb_buffer_pool_size=1200M).

Both tests use 25 threads. At first I repeated each test twice to check consistency but was satisfied the results are consistent test to test from sysbench.  Complete configuration and command notes are listed below, but first, let’s get to the results!

Results:

In the case of Transactions/sec and Read/write request/sec – higher numbers are better. In the case of (ms) stats – lower numbers are better.

Conclusions:

• A small RDS instance, out of the box is ~15% faster on a transaction basis than an m1.small EC2 instance even with tuning.
  • The observu blogger reported performance differences as high as 240% against a 50GB database with 50 threads. This test was unable to replicate that much of a difference, however, this test was done with much a smaller database and half the threads.
• There is no noticeable difference from tuning on a small database. That makes sense because the entire DB was in memory even without tuning.
• The 95% percentile response time numbers are interesting. RDS keeps the lid down 11% better against the small db, and 33% better with the large db.

Why is RDS faster? My thought is RDS is running on better I/O hardware than a vanilla m1.small instance backed by EBS. This effect may become more prominent when running against a very large database, like a 50GB database where most of it cannot be loaded into memory.

On an RDS instance, Amazon sets the innodb_buffer_pool_size to DBInstanceClassMemory*3/4 of the system memory by default, but this looks closer to 70%:

SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
> innodb_buffer_pool_size = 1179648000

RDS gives you other nice to have features, like automated backups and free minor upgrades (but at the cost of a 4 hour per week maintenance window).

It is always fun to ‘put in a quarter’ with EC2 and spin up some test servers.

Complete setup notes for recreating this experiment on your own:

This experiment used a single EC2 small instance as the test initiator.  All servers are in the same availability zone to minimize network effects. Make sure to separate the load test machine from the target machine.  If you are running sysbench from the same box that MySQL is running on, that is not a fair test. Otherwise the load test threads will compete directly against the database process for system resources which leads to skewed results.

1) On Load Test Machine – install sysbench and mysql client:

sudo apt-get install sysbench mysql-client-core-5.5

2) Create a 5 GB Small RDS instance of MySQL 5.5.27

# all done through AWS control panel.

3) EC2 MySQL Server:

Standard m1.small instance, 1.7GB RAM, Ubuntu 12.04 64-bit, root device is EBS, no attached EBS storage. Make sure to setup the security group so 3306 is open to the load test server.

sudo apt-get install mysql-server
#Edit my.cnf for TCP/IP settings
sudo vi /etc/mysql/my.cnf
#set bind-address = 0.0.0.0
# left everything else at default
sudo service mysql restart

Grant the user access – NOTE: this is convenient and REALLY INSECURE, hey this is a load test.

#login to the server as root:
$ mysql -u root -p
GRANT ALL ON *.* TO root@'%' IDENTIFIED BY '****';
FLUSH PRIVILEGES;

4) On both the RDS instance and the EC2 MySQL instance create a new database called ‘sbtest’.

5) Prepare for Test 1:

Sysbench first ‘prepares’ the test database by creating a single table with as many rows as you like. Our test will have 1 million rows in the test database. This results in about a 228MB database.

The prepare command:

sysbench --test=oltp --mysql-host=host --mysql-user=root --mysql-password=**** --mysql-table-engine=innodb --oltp-table-size=1000000 --max-time=180 --max-requests=0 prepare

(run this against both the RDS and the EC2 machine)

The run command:

sysbench --num-threads=25 --max-requests=100000 --test=oltp --mysql-host=host --mysql-user=root --mysql-password=**** --mysql-table-engine=innodb --oltp-table-size=1000000 --max-time=180 --max-requests=0 run

The default sysbench test is ‘complex’ which does a lot more than simple SELECT statements. For comparison with other benchmarks, I left the settings at the default.

6) Prepare for Test 2:

To run test 2, repeat the prepare and run commands, but up the table size to 10M.  You need to drop the sbtest table first. It took awhile to load the 10M rows…

7) Run Test 2:

Same as before, just higher –oltp-table-size.

8) Test with EC2 ‘tuned up’:

For the 2nd set of tests on the EC2 instance, I increased innodb_buffer_pool_size to 1200MB in the my.cnf file and restarted mysql. I also made sure the number of rows was correct for each test.

innodb_buffer_pool_size=1200M