RightScale Blog

Cloud Management Blog
Cloud Management Blog

MySQL Performance on Amazon EC2

Spurred by Morgan Tocker, I ran some sysbench MySQL performance benchmarks on EC2 instances. This is just the first round, more to follow.

The Setup

On a small instance, I reformatted /mnt with LVM2 and creates a 140GB xfs filesystem. In the my.cnf the important InnoDB settings I chose are:

innodb_buffer_pool_size = 1G 
innodb_additional_mem_pool_size = 24M 
innodb_log_file_size = 64M 
innodb_log_buffer_size = 8M 
innodb_flush_log_at_trx_commit = 2 
# Write to log but don't flush on commit (it will be flushed every "second")

On a large instance, I created /mnt using LVM2 and striped across both drives to get a 200GB xfs filesystem. The my.cnf settings were:

innodb_buffer_pool_size = 4500M
innodb_additional_mem_pool_size = 200M
innodb_log_file_size = 64Minnodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2 
# Write to log but don't flush on commit (it will be flushed every "second")

The Benchmark

I then ran the sysbench OLTP test as follows:

mysqladmin -u root create sbtest 
sysbench --test=oltp --oltp-table-size=1000000 --mysql-socket=/var/lib/mysql/mysql.sock --mysql-user=root prepare sysbench --num-threads=16 --max-requests=100000 --test=oltp --oltp-table-size=1000000 \ --mysql-socket=/var/lib/mysql/mysql.sock --mysql-user=root --oltp-read-only run 
sysbench --num-threads=16 --max-requests=100000 --test=oltp --oltp-table-size=1000000 \ --mysql-socket=/var/lib/mysql/mysql.sock --mysql-user=root run

The Results

OK, now to the results, all numbers are transactions per second printed by sysbench.

_Update:_ Morgan asked about the MySQL version and I realized I was using stone-aged-5.0.22. So I reran with 5.0.44 from the CentOS5-Testing repository. I also ran the benchmarks on an x-large instance, with /mnt striped across 4 drives, once with my.cnf unchanged from large instance (4.5GB buffer pool) and once with 12GB buffer pool.

MachineMySQLread-onlyread-write
EC2 small5.0.22227, 228, 230, 241115, 116, 119
EC2 large5.0.22466333
EC2 small5.0.44227, 229, 229115, 115, 115
EC2 large5.0.44420, 428, 462277, 310, 319
EC2 xlarge 4.5GB5.0.44620, 630, 637463, 483, 495
EC2 xlarge 12GB5.0.44593, 598, 620453, 481
AMD Sempron 645.0.22383, 394220, 225
iMac5.0.??253144
All numbers are transactions per second as printed by sysbench. A range or multiple values indicate values from multiple benchmark runs.

The iMac is a dual-core, 2.16Ghz, 2GB box with MySQL installed somehow and the machine was not 100% idle. The Sempron 64 is single-core, 3400+ (2Ghz), RAID-1 7200rpm drives, 2GB RAM, not 100% idle (I really have gotten spoiled by EC2 and the ability to launch instances at a whim). These tests are just meant as a ballpark point of comparison.

The benchmarks certainly confirm that the write performance on the small instances is, shall we say, lacking. I had expected a bigger improvement overall for the large instances. I guess for the read-only benchmark we're seeing two disks vs. one disk, and on the read-write side we're seeing two disks vs. "a problem." With a real application load the large instance will often show a greater improvement over the small instance than shown here because the buffer pool increase can really make a huge difference. Time to grab an x-large instance and try that... NB: Note that Morgan's blog entry referenced at the top uses myisam tables while I used InnoDB tables.


Archived Comments

Morgan Tocker InnoDB should scale better than MyISAM with more cores/cpus. Do you mind commenting on what version of MySQL you are using – is it 5.0.30+ ?

Thorsten crush… using 5.0.22 … rerunning using 5.0.44 … results soon

Thorsten Well, dunno whether 5.0.44 is slower than 5.0.22 or whether the CentOS5-Testing version is badly compiled, but the performance is no better.

Ian Any ideas why the xlarge instance is slower with a 12GB buffer than with 4.5GB?

Thorsten Dunno why the large buffer makes it slower. If that much cache is not actually productively used, there may be less locality resulting in poorer L1/L2 cache performance. But that’s just a general guess. Given that the performance is not really that different, I wouldn’t get hung up on it. If your queries can benefit from the larger buffer pool, then this will a much more significant improvement than any differences seen here.

Frédéric Sidler Do you really compare apples with apples with Morgan Tocker. Everything between the two posts is completely different (OS, version, memory, database engine). As everything is different, I’m asking if these results can be compared. The only thing that frightens me is that mysql should run on an extra-large instance (15 GB memory, 8 EC2, 4 virtual cores with 2 EC2 Compute Units each, 64-bit) to accomplish the same numbers Morgan Tocker is giving on his post with his single computer (AMD64 3000 (I think), Ubuntu 7.10, 7200RPM drive, 1G RAM)

Thorsten Frédéric, yes, i am comparing apples and oranges, but I’m not really comparing nor do I claim I’m comparing. His post got me interested in running some benchmarks on my own, and I selected the components that interest me, hence the differences, specially my use of InnoDB vs. his use of MyISAM. We only do InnoDB, so his numbers weren’t useful to me. Incidentally, I need to rerun some of the benchmarks. It turns out a lot of what I ran is entirely cpu bound, which is interesting, but I also really would like to run stuff that is I/O bound.

Comments

Greg, I haven't seen "hard data" on this. There are variations, which are pretty tight nor normal use, but for benchmarking it can be challenging.
Dave, I don't have benchmarks I can publish for this. My rules of thumb are: if you need bandwidth use the local drives striped. If you need I/O ops (i.e. random access) use EBS but watch the cost. You can stripe across a couple of EBS volumes and I know of people who do that, but I'd work really hard at somehow splitting up the load to avoid it, it just seems like pushing the envelope too far.
The community here might like to see some of our own results benchmarking MySQL on EC2 - including a fascinating finding on how CPU types affect the performance you get! http://www.infibase.com/blog/2009/07/mysql-on-amazon-ec2-part-1/
I have found that the CPU performance is irregular at best. My finding is that running a CPU based benchmark on EC2 instances varies, and you will not receive consistent CPU performance over time. The variations in performance were very evident across all instance types. If you are CPU bound, make sure you understand how irregular the performance of EC2 really is.
Nick, this is very interesting. Do you have any data to back up your claim, or is it just a rant? I'm sure you've done some benchmarking, perhaps you care to share?
Response by Greg (not verified)   Ι   November 09, 2009   Ι   06:05 PM
We're looking to set up automated performance testing of our service and wondered whether we could run such benchmarks on EC2 instead of our own controlled boxes. I'm really curious, is there any hard data either way on whether instances provide consistent performance? Thanks
[...] OTLP benchmark and with both it’s read-write and read-only tests. There is some existing, but rudimentary benchmark numbers using Sysbench’s OTLP from Rightscale. So we could at least know that we were on the right [...]
RE: bottlenecks - I'd also be interested to see the results of I/O bound benchmarks. We're in the process of migrating a dedicated server that's currently I/O bound to EC2, would be nice to see someone else's conclusions.
[...] OTLP benchmark and with both it’s read-write and read-only tests. There is some existing, but rudimentary benchmark numbers using Sysbench’s OTLP from Rightscale. So we could at least know that we were on the right [...]

Post a comment