Benchmarks MySQL 4.0.18: Intel versus AMD

A Linux database server report would not be complete without the open source database MySQL. Many of our readers requested that we test with both MyISAM (default storage engine in MySQL 3.x) and InnoDB (default storage engine in MySQL 4.x), so we performed many more tests than last time.

It must be said that the MySQL results had a large margin of error (3% - 4%) compared to DB2, especially at high levels of concurrency.

Here is our MySQL configuration:

           Read_buffer=2GB
           Port=3306
           socket = /var/lib/mysql/mysql.sock
           skip-locking
           set-variable = max_user_connections= 2000
           set-variable = max_connections= 2000
           key_buffer=2G
           Read_buffer=2G
           table_cache=1024
           tmp_table=128M
           max_heap_table=256M
           read_rnd_buffer = 64M
           thread_cache=16
           net_buffer_length=16k

The " query cache" was off, as we wanted to test worst case performance. In some cases, the query cache was able to push a single Xeon to 1000 queries per second, and the CPU was still capable of doing more, as the CPU load was at 50% - 70%. At 1000 queries/s and more, other bottlenecks started to kick in, such as the latency of the network driver, the operating system and so on.

All numbers are expressed in queries per second. All concurrency tests below 5 are not reliable enough to make any firm conclusion as the margin of error is much higher.

Concurrency Dual Xeon (Gallatin)
with L3 cache
Single Xeon (Gallatin)
with L3 cache
Dual Xeon (Nocona)
with HT
Single Xeon (Nocona)
with HT
Dual Xeon (Irwindale)
3.6GHz with HT
Dual Core Intel
3.2GHz
Dual Opteron 250
2.4Ghz
Single Opteron 250
2.4GHz
Single Opteron 252
2.6 GHz
1 243 248 280 277 286 233 290 298 319
2 357 317 423 338 450 344 438 370 399
5 466 356 473 358 497 442 543 435 470
10 505 361 521 375 517 487 629 465 502
20 496 350 531 371 545 507 670 455 498
35 508 355 555 371 506 490 665 470 507
50 497 348 526 368 495 502 669 472 508
                   
AVG 494 354 521 368 512 486 635 460 497
MAX 508 361 555 375 545 507 670 472 508

Those were the raw numbers. Let us now analyse this...

Concurrency Dual versus Single Xeon Galatin Dual versus Single Xeon Nocona/ Irwindale Dual Opteron 250 vs Single
1 -2% 1% -3%
2 12% 25% 18%
5 31% 32% 25%
10 40% 39% 35%
20 42% 43% 47%
35 43% 50% 41%
50 43% 43% 42%
       
AVG 40% 41% 38%

MySQL ISAM is an incredibly fast database engine in our benchmark situation: it handles the same workload about twice as fast as DB2. I have to emphasize "our benchmark situation" because we cannot forget that our workload is mainly about reading the database and not writing. And of course, it must be said that the MySQL ISAM engine does less work on each query than DB2; it does not support transaction-safe (ACID compliant) commit, rollback, and crash recovery capabilities.

MySQL, as we have also noticed 6 months ago, doesn't seem to scale as well as DB2. At best, you get a 40% - 45% performance increase when the concurrency level is high enough. When we move to quad CPUs, we only get a 20% - 30% increase while DB2 still offers a 70% increase. The better scaling of DB2 means that with enough CPUs, it runs almost as fast as the MySQL ISAM engine, and offers all the transaction-safe capabilities as a bonus.

Let us check if the architectural differences between the CPUs make a difference . Again, don't pay too much attention to the results of the lower concurrency levels.

Concurrency Dual Xeon Irwindale versus Nocona (3,6 GHz) Xeon Nocona (3,6 GHz) vs Galatin (3,06) Opteron 2.6 vs Nocona 3.6 Opteron 2.6 vs Pentium-D Xeon Nocona 3,6 GHz vs Pentium-D
1 2% 12% 15% 37% 19%
2 6% 7% 18% 16% -2%
5 5% 1% 31% 6% -19%
10 -1% 4% 34% 3% -23%
20 3% 6% 34% -2% -27%
35 -9% 5% 37% 4% -24%
50 -6% 6% 38% 1% -27%
           
AVG -2% 4% 35% 2% -24%
MAX -2% 4% 36% 0% -26%

The bigger L2-cache of the Xeon Irwindale did nothing more than compensate for the slightly higher latency of the L2-cache. The Xeon Irwindale and Nocona perform alike.

MySQL, unless you get the special Intel Compiler optimized version, remains the stronghold of the Opteron. The fastest (single core) Opteron outperforms the best Intel CPU by a 35% margin. We didn't use the Intel compiler version as we have reason to believe that this version is not used a lot in the real world. We might try it out in a future article.

The relatively limited scaling also means that high clocked single CPUs can be an interesting option. This is illustrated by the Opteron 252 2.6 GHz, which outperforms the dual core Pentium-D 3.2 GHz by a small margin.

Benchmark Configuration Benchmarks (continued)
POST A COMMENT

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.
    Reply
  • 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.) Reply
  • 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!
    Reply
  • Viditor - Saturday, June 18, 2005 - link

    "They aren't testing compilers"

    Oh sure...just throw REALITY into the mix why don't you...!
    ;-)
    Reply
  • Icehawk - Saturday, June 18, 2005 - link

    They aren't testing compilers. Reply
  • 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? Reply
  • 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. :)
    Reply
  • 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?
    Reply
  • 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!
    Reply
  • 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!
    Reply

Log in

Don't have an account? Sign up now