One of the last works in which I’m involved is the creation of a webserver to handle a dozen of websites. This task can be done in different ways: you can grab the best and pay many (many many) dollars or you can try to understand what is the best way to accomplish it. I prefer the second solution.
So, the first thing to understand is where we can do things better: a simple webserver is set-up with an HTTP server, PHP and a database. In this article I’ll analyze the database part and I’ll try to find the best price/quality ratio on Amazon AWS to plug-in it into the webserver.
How was done the proof
The tool I’ll use to make these tests is SysBench:
“SysBench is a benchmark tool for evaluating OS parameters that are important for a system running a database under intensive load […] to quickly get an impression about system performance.“
The OLTP benchmark that can be performed by SysBench, emulates a simple transaction-processing workload and is one of the most useful tools to test the performance of MySQL.
The budget isn’t very high, so I’ll use micro, small and medium Amazon EC2 instances and the RDS webservice. In each EC2 instance (Ubuntu 12.04) I’ve installed only mysql-server and sysbench. Then I’ve created a table `sbtest` and I’ve launched the following sysbench command to fill the database tables:
sysbench --db-driver=mysql --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=dbuser --mysql-password=dbpassword prepare
Then I’ve fired-up the following sysbench command:
sysbench --db-driver=mysql --num-threads=16 --max-requests=100000 --test=oltp --oltp-table-size=1000000 --oltp-test-mode=complex --mysql-user=dbuser --mysql-password=dbpassword run
Each proof was done two times but avg results are more or less the same.
To view full results it’s better to see them in a Google doc spreadsheet: link.
fig1 – Transactions per sec (higher is better)
fig2. – Per-request statistics (lower is better)
As you can see, I’ve also tried a RAID0/10 option to see if it’s possible to speed-up read/write operations speeding up the disk in which the mysql data directory is holded. Results are a big fail on micro instances, probably because the CPU speed is not enough; but we can improve a little bit other instances types when RAIDing: tests has been made only on medium instances so, on small/High-CPU medium it’s probably the same (maybe even better in the medium High-CPU).
In the medium/RAID tests we can see an improvement of the +28%, respect of the EBS root but we can also see that it’s better to move MySQL folder into an external virtual drive (row 23) even if is not in a RAID array (+25%). Instead in the row 24 we can see a degradation when using – relative – small volumes with a fixed IOPs: to take some advantages from IOPs we should use at least an hard drive of 30 GB (or more) and in this way we can set up a x10 IOPs factor reasonable.
To reach my hard drive performances (and this performances aren’t the top of the top!) we need a Medium High-CPU instance (2×2,5 GHz more or less like my old Intel I5). So the tests confirm that as higher the CPU as higher the read/write operations can be done. But remember: this is a stress-test on MySQL and it’s good to know how to create the global architecture.
RDS talking, I’ve picked up a medium instance (without IOPs because it needs at least 100 GB of space and I don’t need this quantity of space) and results are not very good: in fact, the medium instance is comparable with the EC2 small instance but with a higher price; for that reason RDS is not good.
“chco” in this post writes this:
“RDS is MySQL, but you don’t have as much control as you would with MySQL. There are certain peculiarities in RDS’ MySQL implementation that can cause a lot of frustration. […] A disk is always slower than memory. If you run your own MySQL using local disks, that’s slow as well. But using disk-based operations in RDS is just horrible.”
If you have more experience than me and “chco” in RDS and you can prove that I’m wrong, feel free to comment out this post.
So, my verdict – without analyzing HTTP servers and PHP – is to use a medium instance with an external volume.
- High Performance MySQL: Optimization, Backups, Replication, and More – the definitive guide to building fast, reliable systems with MySQL.