Linux Database Server CPU Comparison
by Johan De Gelas on June 17, 2005 12:05 AM EST- Posted in
- IT Computing
Analyses and Conclusion
First of all, we would like to emphasize that we are well aware of our findings - they are only applicable to your database applications if you run a "read heavy, few writes" database server and the database is not too large, so the most used parts can run mainly from the RAM. As 1 GB DIMMs are very cheap now and with the introduction of 64 bit CPUs and 64 bit Linux 2 years ago, it is clear that making sure that your database has enough memory for its disposal should become a lot easier for many database administrators.There are a few interesting conclusions that we can make about the software side of things. First of all, DB2 8.2 scales fantastic when you add more CPUs. This makes the dual core Opterons very attractive: an Opteron 265 costs as much as an Opteron 252 or Xeon Irwindale 3.6 GHz, but it is clear that it will perform a lot better. It also offers a better upgrade path, since you can use up to four cores on relatively cheap motherboards - compared to the average price of a quad CPU motherboard - with two sockets.
The MySQL MyISAM benches make it clear that pure speed isn't everything. MySQL MyISAM allows you to get away with a single CPU system as it delivered up 300 queries per second, while DB2 was only capable of delivering a bit more than one third of that performance. The picture quickly changes when we need safe transactions too (even with few writes, this might be critical): the InnoDB engine is about 40% slower in our environment. MySQL remains very fast, but as we add more CPUs, the difference gets very small with DB2. While this article has no ambition to be a guide to the software part of database servers, it is clear that you should choose your hardware in function of the database server software that you select. With DB2, you get enterprise class database serving, and dual core CPUs are a very good solution for it. MySQL is excellent to save on your hardware costs, but if you expect the number of transactions/data mining queries to rise quickly, adding more than two CPUs will buy you little performance (10 to 20% boost).
The most surprising thing that we noticed while comparing our new findings on the 2.6 kernel with those of our previous report (32 bit, 2.4 kernel) is that the Xeon benefits a lot less from 64 bit and the new 2.6 kernel than the Opteron. While the 64 bit binaries run consistently (much) faster on the Opteron, the Xeon isn't too happy with them and runs them 4 to 10% slower. Hyperthreading isn't - in our case - helping either, with 1 to 10% lower performance.
Branch prediction penalties, due to the longer pipeline of Nocona/Irwindale, are not the problem. We noticed with Vtune and Code Analyst that the Branch Prediction Unit of the Xeon Nocona and Irwindale does a marvellous job and predicts between 96% (MySQL) and 97% (DB2) of the branches correctly, while the Opteron's BPU is about 93% and 94% correct of the time. MySQL consists of 20% branches, and DB2 has only 16% branches. The L2-caches also do a good job with only 2% of data demands being covered by the RAM, and a 98% hitrate on the L1 and L2-caches.
According to our research, we can assume that the 64 bit implementation of the new Xeon is simply not as powerful as the Opteron's. Intel has some catching up to do, especially when you look at the dual core Opterons. We already discussed AMD's elegant dual core architecture in detail, but in this review, we have seen very good indications that the design with the two cores connected by the SRQ does improve performance in real world applications and not only in our cache-to-cache tests.
This architecture together with AMD being six months ahead with their dual core server product gives AMD significant advantages in the server market today. The lack of mature server versions of Windows (2003) and the fact that only the latest kernels of Linux support the dual core Opteron might slow AMD a bit down, but not for long.
Facebook
Twitter
RSS
45 Comments
View All Comments
linuxnizer - Tuesday, July 19, 2005 - link
Late contribution...The article mentions that Linux didn't work well with AMD Dual Core. The reason could be this:
http://www.iwill.net/inews.asp?n_id=35
it says:
NVIDIA CK804 does not support dual core under Linux yet, only under Microsoft Windows.
Illissius - Saturday, June 18, 2005 - link
Nice article. I'd also be interested in PostgreSQL, being the "other" major open source database... specifically, whether it's any better at scaling with multiple CPUs. (Not that I have any practical use for this information, I'm just curious.)Viditor - Saturday, June 18, 2005 - link
Seriously, mickyb and elmo may be correct about the Intel compilers (I frankly don't have a clue what's used in most shops)...The real problem is that it's a virtual impossibilty to create a "level playing field", but I have to say to the critiques of the article that Johan has done a stellar job of coming as close as possible!
Viditor - Saturday, June 18, 2005 - link
"They aren't testing compilers"Oh sure...just throw REALITY into the mix why don't you...!
;-)
Icehawk - Saturday, June 18, 2005 - link
They aren't testing compilers.Viditor - Saturday, June 18, 2005 - link
mickyb - Thanks for the input! Fair enough...maybe Johan could use both the Pathscale compiler (which is optimized highly for Opteron) and the Intel optimized compiler on his next series of tests?mickyb - Saturday, June 18, 2005 - link
I dissagree with the comment that a large number of people don't use the Intel compiler. I (other developers and IT shops) only use Intel compiler's for Linux. It is the fastest one out there for x86 and Itanium.If you are running a large database that requires a large server (compared with a desktop loaded with RAM to run a personal blog site) like this article is testing, you will be setting up the environment with a trained IT professional that will use the compiler that is fast and stable.
When we build our product for all the UNIX platforms, we always use the vendor compiler instead of gnu. gnu works great and is free, but it is not optimized nearly as much.
This is like saying the same audience won't recompile Linux on the platform they are going to install it on. This is the first thing you should do....and with an Intel compiler. There should be no real reason why one vendor Linux is faster than the others except for compile options and loaded modules. You cannot run Linux out of the box, it doesn't come in a box where I get it. :)
DonPMitchell - Saturday, June 18, 2005 - link
We need to see TPC-C benchmark results for MySQL and other new database systems. Why won't they step up and allow themselves to be compared to the major commercial systems?Viditor - Saturday, June 18, 2005 - link
ElMoIsEviL - "as much as I am un-biased"C'mon mate...anybody who has read your posts knows you're heavily biased towards Intel, just as people who have read mine know that I am biased towards AMD. The important thing is to try and set aside the bias to look at things from both sides...I do try, but admittedly don't ALWAYS succeed. :-)
I imagine you probably posted before you read the explanation of what a query cache is...understandable.
As to not using an Intel specific compiler, I suppose that if it HAD been used I would be complaining as well. We have to rely on Johan and Anand (who frankly know a Hell of a lot more about this than either of us) to choose based on what the market actually uses...if you can site impartial industry sources that show otherwise, I'm sure we would all (especially the AT staff) would love to see them.
I do know that over the years, Johan and Anand have shown themselves to be quite unbiased in their articles (you should go read some of them on Aces as well)!
There are certainly things that I could pick apart as well..e.g. when he states
"In the second half of 2004, already one million EM64T Xeons were shipped"
Yes they were shipped, but that doesn't mean they were sold. The majority of those shipments were probably to OEMs for inventory buildup. Remember that Intel had a huge inventory write-off at the same time, and this was most likely a shift in inventory.
Regardless, none of this has to do with the validity of the article which is excellent and makes sense. If you think about it, it should have been expected...the only for AMD to have increased their marketshare in servers is by performance. They certainly don't have the budget or marketing clout that Intel has!
S390 guy - Saturday, June 18, 2005 - link
About ISAM and DB/2... ISAM (Indexed Sequential Access Method) is NOT a database! It has no referential integrity nor rollback/commit features (although those can be activated on mainframe). ISAM was popular on mainframe when there wasn't any database (or rather when database was a too massive application to run!) and even there they were superseeded by VSAM. They're not much different from DOS random access files (an index file pointing to the relative record number on the main file).And it's no suprise that DB/2 scales well: mainframes rarely feature a single CPU, at least as far as I know.... IBM have had some 20 years to practice on multi-cpu machines!