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

The set-up

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:

<code>innodb_buffer_pool_size = 4500M</code>
<code>innodb_additional_mem_pool_size = 200M</code>
<code>innodb_log_file_size = 64Minnodb_log_buffer_size = 8M</code>
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 re-ran with 5.0.44 from the CentOS5-Testing repository. I also ran the benchmarks on an xlarge 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.

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 ball-park 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 2 disks vs. 1 disk, and on the read-write side we’re seeing 2 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.