As part of our tinkering in the cloud, we decided to run some benchmarks to figure out how well MySQL really performs on Amazon EC2. Some data has been posted here and there but no comprehensive results really gave us the answers we needed. So – we prepared a complex set of automatic benchmarks, ran them many times under various scenarios and analyzed the output. We’d like to share with you our findings  (so that you won’t need to work hard like we did…).

In this post and the following ones we will provide the highlights of our benchmarks of MySQL on EC2. Please feel free to join the conversation, ask questions and add your own findings!

In this first post, we’ll share our methodology – and surprising results that not all instances are equal!

The Benchmarks

Our primary questions were:

1. What is the difference in performance between EC2 instance types?
2. How do storage options impact MySQL I/O? (Local disk, EBS, EXT3 and XFS file systems, striping, etc.)
3. How do MySQL versions and configurations matter, and does MySQL 5.4 deliver on its promise?
4. What other EC2 characteristics affect MySQL’s performance?

We ran the following benchmarks using various configurations:

• sql-bench (click for more details)
  • Background: SQLBench is the standard benchmark suite that comes with MySQL server.
  • Tests used: all included tests both using the MyISAM engine and the transactional InnoDB engine
    • InnoDB command example:
      
      • sql-bench/run-all-tests –create-options=ENGINE=InnoDB
  • Main output: number of seconds the entire benchmark took (so lower is better)
• sys-bench OLTP (click for more details)
  • Background: sys-bench is a transactional database benchmark.
  • Tests used: both the read-only and read-write benchmarks with both MyISAM and InnoDB
    • InnoDB command example:
      • sysbench –test=oltp –mysql-table-engine=innodb –oltp-table-size=1000000 prepare
      • sysbench –num-threads=16 –mysql-table-engine=innodb –max-requests=100000 –test=oltp –oltp-table-size=1000000 –oltp-read-only run
      • sysbench –num-threads=16 –mysql-table-engine=innodb –max-requests=100000 –test=oltp –oltp-table-size=1000000  run
  • Main output: number of transactions per second (so higher is better)
• DBT2 (click for more details)
  • Background: DBT2 is an implementation of the standard TPC-C online transaction processing benchmark.
  • Tests used: a certain heavy configuration of DBT2, for 30 minutes
    • InnoDB command example:
      • datagen -w 20 -d /tmp/dbt2data
        
      • scripts/mysql/build_db.sh -uroot -f /tmp/dbt2data -d dbt2
      • scripts/run_workload.sh -n -c 20 -t 10 -d 1800 -w 20  -u root -z “my test”
  • Main output: number of transactions performed per minute (so higher is better)

Additional notes:

• Each test was performed several times for better accuracy, the results presented are the averages.
• We used Linux instances running Alestic’s Ubuntu 9.0.4 Amazon Machine Images.
• All tests were performed in the us-east-1a availability zone.

In case you are not familiar with EC2’s instance types, here’s a short summary:

Now, to the results!

Intel vs. AMD

It’s a virtual world. Amazon EC2 sells compute units. So you might expect that all units are the same. Well, they are not!

You don’t know in advance which processor your virtual instance will be running. Even for a specific instance type, Amazon use both Intel and AMD processors, of varying types and clock rates. Why is that important? Well, better CPUs give better performance. We found consistently that the Intel-based instances gave better performance than the AMD-based ones – on the same instance type (i.e., same price!)

Intel Xeon 2.66GHz CPUs performed 30% better than AMD Opteron 270 CPUs using DBT2:

The conclusion: in the EC2 “roulette”, you should hope to get an Intel Xeon!

Below you can see the distribution of CPU vendors per instance type. It is interesting to notice that high-CPU instances always got Intels and small instances almost always got AMDs.

Overall we ran into the following CPU types on EC2:

• Dual Core AMD Opteron Processor 270
• Dual-Core AMD Opteron Processor 2218 HE
• Intel Xeon CPU E5345 2.33GHz
• Intel Xeon CPU E5410 2.33GHz
• Intel Xeon CPU E5430 2.66GHz

Inconsistent Performance

So we’ve learned that results vary according to CPU type. Unfortunately, EC2 has several such inconsistencies. We found that the exact same benchmark on the exact same instance type and processor type can still get different results. Most results will be similar but once in a while you might see a significant difference. I/O to the ephemeral (local) disk and to EBS is also inconsistent and is affected by various I/O parameters, network performance more. I realize that the fact that it’s a virtual machine can cause these side effects, for example your physical server is hosting instances that run heavy processes (such as MySQL benchmarks!).

Coming Up Next

Now that we understand the basic ingredients of EC2 and the consistency (or lack thereof) of the results, we can continue to research additional aspects of MySQL’s performance on EC2. In upcoming posts, we’ll share our results for various instance types, MySQL versions and configurations and much more.