Mike Levin SEO

Future-proof your technology-skills with Linux, Python, vim & git... and me!

Quad CPU Dual Core Server: MySQL or SQL Server?

by Mike Levin SEO & Datamaster, 07/16/2006

So, I’m building an app where I want amazing database performance, and I’m probably going to start out with a quad-processor dual core system, so I get eight execution threads. This is like getting your first 8 nodes in a clustering solution, without the multi-PC requirements of clustering. Yes, I know there failover advantages to clustering, and I will be moving towards that. But for the first server, Going with AMD Opterons’ with Window 2003 Server x64 with SQL Server 2005 seems the most logical choice, in terms of maturity and performance. Can you believe it outperforms DB2 and Oracle for price/performance? And since SQL Server 2005 takes full advantage of multithreading, I can’t wait to see what it can do on these quad CPU / dual core motherboards.

The big question is what about when the app becomes so popular that I need to go to true clustering? It can get very expensive very fast on the Microsoft platform, and clustering is actually MORE mature, and certainly cheaper on MySQL. But where are the benchmarks? So far, I’ve found no comprehensive apples-to-apples comparisons between state-of-the-art SQL Server (2005) to state-of-the-art MySQL (version 5.1). You can find MySQL vs. itself. The best comprehensive benchmarks, were the ones from eWeek several years ago, when SQL Server didn’t support clustering, and the results were skewed by bad JDBC drivers. The times have very much changed, and I think to SQL Server’s advantage.

That is of course unless MySQL can kick ass on the type of hardware I’m specing out to defer the clustering issue for a little while. If it can, I would very much like to move to MySQL today. But it opens up a can of worms. I’m worried about all the gotcha’s and pitfalls that may lie in that direction. The image of working a combination lock to get just the right combination comes to mind. Which version of 64-bit Linux? How well will a 64-bit version of MySQL run on it? Will I have to start compiling it myself? Will it take advantage of multithreading? Which MySQL database engine should I choose? After answering all these questions, and gambling that I got it right, couldn’t I have been up and running several weeks prior?

There are no such choices or worries going the Microsoft route. SQL Server will take advantage of multithreaded multiprocessors. There’s only one 64bit OS to choose right now (until the server version of Vista arrives… but 2003 x64 is mature). There’s no “engine” to choose… SQL Server uses its own internal optimization engine, which when combined with the index fine tuning wizard, tweaks amazing performance out of your hardware, even on a bad day. That’s not to say it excuses you from writing inefficient code, but when you do, it steps in and does a pretty amazing job of preventing bottlenecks. And if it can’t there are built-in Analysis Services and the usage-based optimization wizard.

Now, it’s not that I need to be hand-held. But I find that tools like this allow me to tweak out as much performance from my apps as if I hired an expensive database admin. It essentially turns database administration into a part time job for my lead IT person, or even myself. Can I expect anything like this with MySQL? Maybe I can. Maybe everything I describe is available in the Open Source world, with enough research, diligence and experimentation. I don’t know yet. MySQL gurus are welcome to answer. I’d like the answer to be yes, because with quad-processor, dual-core servers, you can get into some sticky licensing issues with SQL Server, and it only gets worse when you move to clustering. And I shudder to think of the licensing issues when I add virtualized server Nodes into the cluster. Is it best just to get off of Microsoft Server now?

Yes, Open Source to the rescue, I totally believe that. But in under a few days? With Microsoft, the answer is most certainly yes, from the day your server arrives, to configuring your RAID to installing a 64-bit OS to hardening the OS and reducing surface area, to installing the database app and doing the same, to migrating over existing databases, to testing and going live with a new production server, and getting the most out the hardware that you paid for. The answer to all of this is yes, if I go the Microsoft route–in a day or two with very few gotchas or pitfalls.

Can someone lay out a rough roadmap where I can go the Linux Open Source route with similar confidence? I want to do my own homework, and I’ve Googled galore on this topic, but haven’t been able to come up with a path that is as clear and confidence-building as it is on the Microsoft side. It may be my lack of familiarity with Linux and BSD, but this article is a step towards remedying that.

Feel free to post resources you feel are critical for me to look at in the comments.