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.

As an offshoot on another thread someone asked for some feedback on development with Rails. They pointed out that there are plenty of pro-Rails resources on the net but were curious to hear some of the other side of experience with Rails.

This post is an edited version of the private reply I sent. To be honest I initially wasn’t going to put this up in public at all for risk of the wrath of the fan-boy legion, but I thought it was worth sharing a few of these thoughts.

First of all, let me say that Rails isn’t bad, and many Rails developers are fine developers. One of the smartest guys in the world I know loves Rails for all the right reasons and uses it very effectively.

I’d classify my caution about starting a project in Rails in three buckets-

Immature

Rails has progressed a bunch since I worked with it, but its still very immature compared to the other environments. Any Rails deployment that has more than a minor load (needs more than a shared hosting environment or on the order of a few thousand visits a day) will typically require a bunch of time dealing with infrastructure issues. When you install just about any modern copy of linux you are pretty well setup to run PHP or other more mature environments out of the box. Sophisticated people can tweak some stuff, but you get apache, mod_php and you are ready to go.

For Rails you don’t hit this much during development since everything is running on your machine with their nice little single threaded local server. But the minute you have any real load (lets call that 5 people are hitting your site at once) it can be a problem. I’m sure since my experience these things have improved a lot, but I still hear all the time from people about the pain of dealing with Mongrel and other deployment issues. What other major web app runtime doesn’t have an apache module? There are some of the core-Apache folks who I would listen to if they told me that Mongrel was really the right way to build this, but I haven’t heard from them yet.

Furthermore lots of the infrastructure is still coming along. There are some debuggers now, but they are still very new and given how much Rails does for you under the covers (more on this later) it can be really hard to figure out what went wrong. What it comes down to is do you really want to invest your resources into running Rails or into running your business? There are some very successful bigger sites that are built on Rails but they tend to have a dedicated staff of Rails experts, many of whom contribute to the Rails codebase. If you don’t have that guy, you need to hire that guy (not the one who thinks Rails is cool and picked up a book on it 6 months ago, but the one who really gets the details of the architecture and deployment) or you should be investing somewhere else.

Abstraction

For me, Rails typically does not operate at my favorite level of abstraction. All computer programming is about working in different levels of abstraction (unless you are at Intel working on the inners of the CPU and even there most of those folk work in abstractions, rather than individually manipulate individual logic gates). Picking the right level is the key. Rails really pushes you to a certain level of abstraction that makes rapid development easy, but often you don’t realize what is really going on under the covers since it handles it all for you. It is really easy to accidentally write a loop that does a database query through every iteration, but you don’t realize that is what is happening since it just looks like safe object references. Its easy to have too nested loops each doing more queries and just trash your performance.

All these things can be fixed by careful tuning and after all you have all the sources to this stuff. But Rails sets people up to work at that other layer and I’ve rarely met Rails developers who actually built a log of every SQL query that their app did to tune their performance. Again, this can be invisible if you only have a few users hitting your site every minute or for demos, but falls over quickly (and can be lots of work to fix) later.

This issue isn’t unique to Rails. .NET users can fall into the same traps if they just use all the existing libraries although the standard libraries from Microsoft tend to be a bit careful about making this stuff explicit. Any environment can have a framework that causes this problem although Rail’s ability to do late binding on objects can make it even harder to see what is happening. For example, I’ve done some work lately with Drupal, a framework written in PHP and it typically suffers from similar issues.

What’s The Big Deal

My personal reaction to the RoR hype is “what’s the big deal?” A talented developer with the right architectural expertise can do almost equivalent rapid development of web apps in PHP, .NET, Python, Java or Rails. They all have great tools and if you take the right approach they can all be pretty equivalent. The advantage of Rails is that it “puts you on rails”. Often, web-developers working with PHP, .NET or Java can build themselves a mess of either hacky spaghetti code or over-architected, unnecessarily complex stuff. I’ve seen both. Rails is very proscriptive about how you build an app so if your app fits into their model (typical data-driven web site stuff), most developers will be more likely to get it almost right.

Beyond that there are other stylistic things. Personally, I prefer C-style syntaxes over Smalltalk-style, so I’m more comfortable in C#, PHP, or Java. Other people really love the Ruby language.

There are some actual differences in the runtimes that you can poke at, putting some Pros and Cons in various columns but they are mostly a wash for general web-apps. There are some specialized apps that require certain perf characteristics that would steer me towards .NET or Java (where you get compiled performance and the ability to keep objects in local RAM in-between requests). Certain other apps have different characteristics that would steer me towards PHP, but for most it doesn’t really matter.

Again, Rails isn’t bad and the last thing in the world I would want to do is imply that I don’t approve of someone’s skills because they prefer Rails (or even that I would avoid a Rails project, despite it not being my first choice). I’m not a fan of some of the hype/fan-boy attitude surrounding Rails and I would advise folks that work in Rails to invest the time into understanding the details of what goes on in their environment, especially with respect to how it impacts database performance.

There has been quite the kerfuffle on various blogs and mailing lists about various approaches to work. It appears to have mostly started off as an exchange between 37signals and Jason Calacanis, but it spread to various nasty exchanges on some mailing lists as well as other posts like this one from my friends at Jackson Fish.

At some point I made the following post on the Seattle Tech Startups mailing list (edited slightly)-

Not every startup is the same.

Not every person is the same.

Some people are not at all suited for any startup. They thrive with a certain stability only a big company will give. Not that you can necessarily be complacent at a big company, but there is a stability there that no startup will ever have.

Some people just love to work. They will thrive in a startup that has that culture.

Some people take a more balanced approach towards live and work. They probably bring useful things to the table beyond lots of hours.

What is important to realize is that every startup will have a culture. And people who don’t fit with it will probably be miserable. If your startup has a “go to coffee twice a day and every chat is over a long lunch” culture, the guy who just wants to sit and crank out code all day will be frustrated with their colleagues. And they will be frustrated with that guy (who doesn’t spend as much time getting in sync with everyone else).

At the same time, if you have a culture where people just crank on stuff all the time, the person who takes the different approach will not be appreciated very much either.

Go figure.

Some businesses will be a better fit for one or the other. Its probably hard to get a new release out every week or two on the more relaxed/thoughtful plan. If you are in a market that requires that (because the competition is going to do that and will beat you if you don’t) you had better figure out how to make that your company culture or else go for a different market.

Other markets have very different expectations. They won’t go for a quick to market but unpolished product and they don’t want new releases (and thus change and more training) all the time anyway.

Fit your culture to your business (or vice versa). Fit your people to your culture. Don’t try to force a fit where its not going to happen because its not going to work.

And be understanding that one size doesn’t fit all. Stop calling people slave-drivers because they enjoy the driven culture and build a business that fits that, and start calling other people lazy because they aren’t putting in 80 hour work weeks. Each has its place.