I’ve been working on a project lately where we are trying to design a site to handle some really high traffic volumes. As an interactive data-driven site, the database is going to be the scalability limit- the site is designed so its easy to add more front-end web machines behind load balancers, but scaling-out your database (via replication and partitioning techniques) can be much more complicated. So the question at hand is how much load can MySql be expected to handle on commodity hardware. I was a bit surprised to not find good resources for this information on the Internet so we did some analysis ourselves.

First of all, I should define commodity hardware. Today you can buy an incredible amount of computing power for really reasonable prices. You can get a 1U machine with dual-Xeon 5430 CPUs packing 8 cores at 2.66ghz each and 4 SAS drives on a nice RAID controller for under $3000. It is possible to get a machine with more CPU (quad Xeons, etc) but as you move up from here, the prices go up dramatically- jump to 4x quad-core Xeons and you will quickly be spending at least $10,000.

The first round of tests are very simple and are just aimed at asking two very simple questions-

What is the maximum number of queries per second I can expect from MySql on this hardware under ideal conditions?

Does MySql 5.1 scale better than MySql 5.0 on this 8-core system? I’d read various rumors that MySql 5.0 has a hard time fully using 8-cores, but MySql 5.1 hasn’t had a final “general availability” release yet, so I’d be reluctant to use it unless it is a lot faster.

The test is just a tight loop of a PHP file doing 100,000 queries for random user-records. 1-64 of these PHP scripts are run at the same time to test more clients hitting the server. This test is unrealistic in many ways- first of all there are no writes, so the MySql query cache should never get cleared on this test. Because the data-set is fairly small this test should involve NO disk I/O- the entire database table will be cached in RAM and so this is a pure test of the maximum number of queries.

There were a few challenges running this test. First of all we saw some really strangely poor performance at first with the MySql we had installed via a RPM. Re-compiling MySql to make sure it matched with our exact kernel and processor solved that problem.

There was another test that was being limited by network bandwidth- I had thought the client machine was on gigabit Ethernet, but it turned out its port was only 100mbps and we were filling that entire pipe. I found the issue by comparing the performance from the remote machine to running the test-client on the MySql box.

Results

With MySql 5.0 with one thread we were seeing about 7,000 qps (queries per second). Adding a second thread doubled that, but it maxed out at around 10 threads and 40,000 qps. The total CPU was about 540% (or 68% of the total CPU)- it was using more than 4 cores worth of CPU but wasn’t able to really use the full 8.

With MySql 5.1 on thread gave us the same 7,00 qps, and 16 threads gave us about the same 40,000 as MySql 5.0, but as the client-threads increased to around 30 it maxed out at 55,000qps. At this point it was using about 750% CPU (or 93% of the total) on the machine. You really aren’t going to see more than this other than a full compute-bound task (like numerical calculation, encoding, etc). So the conclusion is that MySql 5.1 DOES scale better on an 8 core machine, giving around 30% better performance.

Overall this is showing really good things for MySql performance and especially future expectations. I wouldn’t be eager to jump and go deploy MySql 5.1 yet considering that it isn’t “final” yet, but it should be a good choice when its finished. Also over the next year Intel is expected to upgrade the Xeon to 6-cores per chip and re-introduce hyper-threading giving each core 2 “virtual” threads worth of execution. Together these should give us another 100% performance boost given the right software.

There are a couple of next steps that I’m hoping to explore in future posts. It would be interesting to get a better picture of MySql performance with a mixed load that contains some insert/updates in addition to the queries. I’m also planning on doing a similar performance analysis of memcache. Finally I’m planning on writing a bit about how to use these numbers in creating a capacity plan for your web-site.

This entry was posted on Monday, March 24th, 2008 at 7:26 pm and is filed under Scalability. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.