MySQL Results: Scaling

Back to our main subject, our astute readers have probably already noticed a weird anomaly. Let us analyze this further. If you look closely at both our measurements, Quad-core and Dual-core x86, you'll notice that the scaling is negative. To make it more clear, we made an average of all concurrency numbers from 5 and higher.

MySQL Linux (Queries/s)
Sun T1
4/8 cores 1 GHz
MSI K2-102A2M
Opteron 275
Xeon 5160
Woodcrest 3 GHz
MSI K2-102A2M
Opteron 280
Average Dual-core
(T1: quad-core)
362 749 996 805
Average Quad-core
(T1: octal-core)
433 590 904 622
Speedup Dual to Quad 20% -21% -9% -23%


This is nothing short of amazing. It seems like an anomaly, but this is not the case. These benchmarks have been checked, verified and checked again. They are accurate. The x86 cores running on Linux perform better with two cores than with four cores, but the T1 running Solaris actually improves performance going from 4 to 8 cores.

So who is guilty? Linux or the Opteron system? We had to test with Solaris on the Opteron to be sure. However, the Serverworks chipset of our MSI 1U server was not supported by x86 Solaris. So we went back to our homebuilt server, based on the MSI K8N Master2-FAR.

MySQL Solaris (Queries/s)
Sun T1 4/8 cores 1 GHz Opteron 280 Solaris Opteron 280 Linux
Average Dual-core
(T1: quad-core)
362 456 799
Average Quad-core
(T1: octal-core)
433 605 625
Speedup Dual to Quad 20% 33% -22%


And this puts the performance of our UltraSparc T1 in a whole different perspective. First of all, it is clear that while MySQL might not be the most scalable database, the current kernel of Linux is not helping matters. We did tweak the Linux kernel two ways: the 2.6.15 kernel was optimized for either Intel's or AMD's architecture and the AMD architecture also got NUMA support.

So what is going on here? After talking to our MySQL guru (P. Zaitsev), it turns out that in some circumstances, MySQL might cause trouble for the Linux mutex (mutual exclusion) implementation: "mutex ping-pong". The mutex implementation makes sure that two threads cannot access data in the main memory that is locked by another thread.

It seems however more a MySQL problem than a Linux one, as other databases like DB2 scale very well in Linux. For DB2 under the same load we noticed a performance increase of no less than 80-85% when going from two to four cores. Also, with some loads, the bad scaling kicks in later than our "Select dominated" load. Intel's performance labs told us that they also ran into the same problem.

These issues are not as severe as the problems we encountered with MySQL in Mac OSX. Note that Apple seems to have recognized the problem and seems to offer a workaround. We'll report back with other MySQL workloads to investigate the MySQL scaling problem further.

PostGreSQL Results

PostgreSQL 8.0.7, another open source database, uses processes and not threads to deal with connections. The consequence is that the benchmark numbers are a lot more stable: once each core is busy with it's process, you almost get maximum performance. In other words, the results didn't change much from 5, 10 or 25 concurrent users. To keep things simple, we only list the numbers with 20 users, which results in peak performance. The queries per second numbers at 5 and 25 were only a few percent lower. We did not include the T2000 Sun Server as the optimal PostGreSQL configuration is still under investigation.

PostgreSQL 8.0.7 (Queries/s)
DL385 1 x Opteron 280 517
Intel 2 x Xeon "Irwindale" 3.6 GHz 448
MSI 1U 1 x Opteron 275 490
MSI 1U 1 x Opteron 280 524
Intel 1 x Xeon 5160 WC 3 GHz 673


Another clear victory for Woodcrest. On the Opteron, every 10% in clockspeed increase seems to result in a 7% performance increase. So if we extrapolate, an Opteron 3 GHz would arrive at 616 queries per second.

MySQL Benchmarks Performance Analyses
POST A COMMENT

91 Comments

View All Comments

  • duploxxx - Monday, June 19, 2006 - link

    2 weeks have past the way, still no word from anand about the microsoft benches? (i recieved a coment that it should only take 1 week to finish......

    reason????? don't make us guess why you don't post these benches
    Reply
  • severian64 - Tuesday, June 13, 2006 - link

    I've be reading Anandtech articles for a long time and i have to say that this article is so biased that i think it should be retracted. I can't wait for the next pro intel article.

    The MySQL and Sun combination attained a result of 712.87 SPECjAppServer2004 JOPS@Standard running a 64-bit version of MySQL 5.0 and SJSAS 9.0 on Sun Microsystems' Sun Fire(TM) X4100 servers powered by Dual-Core AMD Opteron(TM) processors(1). The result demonstrates superb scalability of the whole solution, as compared to the previous result of 266 SPECjAppServer2004 JOPS@Standard that was achieved with Single-Core AMD Opteron processors (2). This solution also demonstrated the best database performance, measured in SPECjAppServer2004 JOPS@Standard per database core (SPECjAppServer2004 JOPS@Standard /DB core), of any competitive submission using less than 20 total cores in database and application tiers. MySQL's SPECjAppServer2004 JOPS@Standard /DB core metric surpassed an Oracle-powered result by over 30 percent (3).

    MySQL Helps Set World Record in Java Application Server Benchmarks; High-Speed Open Source Software Blaze Past Proprietary Solutions
    CUPERTINO, Calif.--(BUSINESS WIRE)--June 12, 2006--A popular application server benchmark, featuring a complete open source software stack with MySQL 5.0 database, the Solaris(TM) 10 Operating System, and Sun Java(TM) Systems Application Server 9.0 Platform Edition (Project GlassFish(SM)) has shattered the competition by offering up to 8.6 times lower cost of acquisition than the comparable solution (1,4), according to the benchmark test results published at http://www.spec.org/jAppServer2004/results/jAppSer...">http://www.spec.org/jAppServer2004/results/jAppSer....

    Maintained by the Standard Performance Evaluation Corp. (SPEC(R)), the SPECjAppServer(R)2004 test is a recognized industry standard benchmark used to measure performance of Java EE application server platforms and each of the components that make up the application environment -- including hardware, database software, JDBC drivers, JVM software and the system network. It is designed to model a real-world automotive dealership application, including manufacturing, supply-chain management and an order/inventory system.

    "Open Source software can provide dramatic benefits for enterprise IT applications - especially in terms of real performance and TCO," said Ethan O'Rafferty, director of Strategic Alliances for MySQL AB. "We are proud that Solaris 10 is an ideal deployment platform for MySQL 5.0."

    The MySQL and Sun combination attained a result of 712.87 SPECjAppServer2004 JOPS@Standard running a 64-bit version of MySQL 5.0 and SJSAS 9.0 on Sun Microsystems' Sun Fire(TM) X4100 servers powered by Dual-Core AMD Opteron(TM) processors(1). The result demonstrates superb scalability of the whole solution, as compared to the previous result of 266 SPECjAppServer2004 JOPS@Standard that was achieved with Single-Core AMD Opteron processors (2). This solution also demonstrated the best database performance, measured in SPECjAppServer2004 JOPS@Standard per database core (SPECjAppServer2004 JOPS@Standard /DB core), of any competitive submission using less than 20 total cores in database and application tiers. MySQL's SPECjAppServer2004 JOPS@Standard /DB core metric surpassed an Oracle-powered result by over 30 percent (3).
    Reply
  • ChuaChua - Saturday, June 10, 2006 - link

    I'm confused about the charts.
    What are the numbers on the X and Y axes?
    Reply
  • JohanAnandtech - Sunday, June 11, 2006 - link

    "To interpret the graphs below precisely, you must know that the X-axis gives you the number of demanded requests and the Y-axis gives you the actual reply rate of the server. The first points all show the same performance for each server, as each server is capable of responding fast enough. "

    http://www.anandtech.com/IT/showdoc.aspx?i=2772&am...">http://www.anandtech.com/IT/showdoc.aspx?i=2772&am...
    Reply
  • JohanAnandtech - Friday, June 09, 2006 - link

    1. "you use workstaion/budget motherboard against the intel server board. use a sun galaxy or hp proliant. "

    No, we do not. We used the MSI K2-102A2M for ALL opteron testing except the one where we tested MySQL with Solaris as the serverworks chipset was not supported by Solaris x86.
    Note that this server performed better than the HP DL385, which uses slower memory timings. Using the HP proliant would have resulted in slightly LOWER Opteron numbers not faster!
    I don't get that a few people make a big fuzz about the MSI K8N Master2-FAR, as we only use it once, out of necessity as it worked under Solaris. Know that Solaris x86 supports only a limited amount of x86 hardware.

    2. About our testing methods: yes, we use our own benchmarks. We'll add some industry standard benchmarks to the mix later. However, Industry benchmarks are what manufacturers optimize for, while our benches come straight out of the realworld, and are what real people are using. The same tests showed the Opteron beating the old Xeon by a pretty big margin, check our previous MySQL results. I don't see why now all of a sudden our tests should be changed

    If you feel there are other issues, feel free. I will definitely try to answer any concerns you have.
    Reply
  • duploxxx - Friday, June 09, 2006 - link

    hmm thx for the reply. thats clear now. seeing all the reactions here on your review.
    it seems that the way its build up is far from structured and people do have problems reading it.

    you didn't answer my question why some benches are single sock some dual sock... but i quess you are rather busy.

    the way you talk as this core is the best thing (not released yet) against a new platform from competition that will be launched at same time... still does make me wonder but anyhow.

    your own benchmarks and rather strange OS for benches (with personal tweaking) is still not relevant. Giving results on a far more used platform would be much nicer to compare... but i already have a good idea of the few benches you will be showing in this review on a wintel OS (let's hope i am wrong). youre benches on linux might be straight out of real world, but impossible to verify.

    The only comparisson you make are 2 Spec benches and they were probably done also on that nice linux platform.... looking at the figures. but how comes that for one or another reason the opteron is clocked now at 2800 while you have 2400 and 2600 systems?
    Reply
  • JohanAnandtech - Friday, June 09, 2006 - link

    Ok, addressing the other issues:

    1. Why no dual socket, quadcore in some benches:
    the reason is that with the LAMP tests we ended up with a limit in our httperf benchmark: it couldn't measure anything above more than 3000 req/s for some reason. So there is another bottleneck kicking in. So we avoided the bottleneck for now by not testing with quad core. This was happening on both the Opteron as the Woodcrest system.

    2. The Gentoo numbers of the previous review that gained 9-10% was a comparison between 1 Dualcore and two dual single Core CPUS. Note that the same review shows only 38% performance increase from one to two CPUs.

    3. A few people try to discredit this review in every way possible, I well aware of that. However, even though these benchmarks can't be repeated by other people for obvious reasons (the databases are not available to the public), the benches are in line with what other people have found.

    http://www.mysqlperformanceblog.com/2006/06/08/int...">http://www.mysqlperformanceblog.com/200...el-woodc...

    P. Zaitsev is one of the most respected people when it comes to MySQL performance and is head of performance tuning of MySQL.
    Reply
  • ashyanbhog - Monday, June 12, 2006 - link

    <Quote>The Gentoo numbers of the previous review that gained 9-10% was a comparison between 1 Dualcore and two dual single Core CPUS. Note that the same review shows only 38% performance increase from one to two CPUs.</Quote>

    Correct me if I am wrong. Follow the link below to Anandtech's own earlier benchmarks. Goto the last table on the page and check results of "Dual Dual Core 875" and "Dual Opteron 248" from 5 Concurrencies onwards. The increase is slight, but there is definetly no performance degradation. The earlier review too uses Opterons+Linux+MySQL+InnoDB, the same as this setup used. Why do you get totally different results sets this time around?

    http://www.anandtech.com/IT/showdoc.aspx?i=2447&am...">http://www.anandtech.com/IT/showdoc.aspx?i=2447&am...

    In the next page of the same review, DB2 shows fanastic gains from 5 Concurrencies onwards when going from Dual Cores to Four Cores. Check the first table under "Benchmarks IBM DB2: Single core versus Dual core". Note results of "Dual Opteron 2.2 hz" and "Dual Dual Core AMD 2.2 Ghz". This too is on linux. DB2 is definetly better suited than MySQL to reflect gains when moving from two core to four core setup.

    Was publishing the obviously wrong MySQL results that you got this time necessary?

    Despite choosing Gentoo, for the optmization capabaility, you have not chosen to publish the optimizations options used. Gentoo gurus would have verified that the optimizations for each of the processors where fairly chosen. This is not a allegation that you have taken sides, but why hold back some specific info when they are not secretive or proprietery in nature? And yes, I know you have used Gentoo in many previous benchmarks without specifiying the optimizations, but as this review seems to have become conterversial, it will help clear the air a bit. Specifying the options used when you have deviated from the default settings will surely increase credibility of review.articles.

    Using a standard, preconfigured and widely available package like Debian, RHEL or SLES in their default settings was another option to ensure a neutral platform.

    Cant comment about other parts of the review as I was only interested in the database performance

    <Quote>A few people try to discredit this review in every way possible, I well aware of that.</Quote>

    Why cant readers of Anandtech question the process used in a review? Afterall its their page views / site visit that brings the ad revenue. Readers tend to have limited time to go thru and comment if they want to. Exaggration may happen when we dont have time to express our doubts in detail. Your comment above was cheap shot.

    Still, nice to see that Intel finally has something that can be compared to a Opteron. Good to have a choice, but the 3 year wait was too long.

    And thanks for replying about the hardware used.
    Reply
  • zsdersw - Monday, June 12, 2006 - link

    quote:

    the 3 year wait was too long.


    Apparently you have no idea how long it takes to bring a totally new chip to market. This generally takes approximately 5 years.
    Reply
  • BasMSI - Saturday, June 10, 2006 - link

    Hahahaha, what balony.....

    Aceshardware didn't have problem reaching above 8000 requests on the Dual 844 back in 2003.

    The article can be found here: http://www.aceshardware.com/read.jsp?id=60000279">>>Click here<<

    And you where unable?
    Get real.

    If you don't know how to setup a server, then stay away from trying to do such.
    Reply

Log in

Don't have an account? Sign up now