TvE 2100

At 2100 feet above Santa Barbara

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 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:

innodb_buffer_pool_size = 4500M
innodb_additional_mem_pool_size = 200M
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")

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.

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 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.