Spurred by Morgan Tocker I ran some sysbench MySQL performance benchmarks on EC2 instances. This is just the first round, more to follow…
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")
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
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.
|EC2 small||5.0.22||227, 228, 230, 241||115, 116, 119|
|EC2 small||5.0.44||227, 229, 229||115, 115, 115|
|EC2 large||5.0.44||420, 428, 462||277, 310, 319|
|EC2 xlarge 4.5GB||5.0.44||620, 630, 637||463, 483, 495|
|EC2 xlarge 12GB||5.0.44||593, 598, 620||453, 481|
|AMD Sempron 64||5.0.22||383, 394||220, 225|
|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.