MySQL Configuration

As our loyal readers know from our previous MySQL adventures, the MySQL database is a highly tweakable but somewhat badly scaling database. Most workloads scale well from one to two cores, but from two to four cores scaling is very mediocre, and in the "SELECT intensive" workload that we benchmark even negative. This has surprised quite a few people, but it is an issue that the InnoDB team is well aware of, and the issue will be resolved in one of the next releases of InnoDB. Until then, we compiled version 5.0.26 with Peter Zaitsev's Mutex patch. This Patch gives much better scaling and performance. Scaling is no longer negative, and we saw a 20% to 40% increase going from two to four cores. However, our workload still doesn't scale beyond four cores, so we tested all CPUs with two CPUs and four cores. That way we have at least an impression on how the different server CPUs compare.

All testing was thus done with InnoDB as our storage engine in MySQL 5.0.26. We optimized for a server with 4GB of RAM. Here is our MySQL configuration:

MySQL Configuration
default-storage-engine InnoDB
skip-external-locking
skip-locking
key_buffer 256M
.
table_cache 64
max_allowed_packet 1M
thread_stack 128K
.
sort_buffer_size 2M
read_buffer_size 2M
innodb_buffer_pool_size 1G
.
thread_concurrency 16
innodb_thread_concurrency 16
innodb_additional_mem_pool_size 8MB
read_rnd_buffer_size 8MB
thread_cache 64
max_heap_table 256MB
tmp_table 128MB
.
innodb_log_file_size 250MB
innodb_table_locks 0
innodb_flush_log_at_trx_commit 0
max_user_connections 2000
max_connections 2000

The "query cache" was off, as we wanted to test worst case performance. Our test database is still the same ~1GB database. The workload consists of more than 90% selects, mostly a "read intensive" workload.

MySQL results

All numbers are expressed in queries per second (Y-axis), and the X-axis shows the number of concurrent accesses.


On average is the Xeon DP 5160 is about 22% faster than the Opteron. That means that the Opteron is clock for clock as fast as the Xeon 5160, which is not bad news for AMD at all, although Woodcrest currently has the raw clock speed advantage. Considering the HP DL585 can only use DDR-333 with the Opteron 880, the picture might even get better with the DL885 which can use DDR-400.

There is little doubt that MySQL is not the favorite application of the Xeon MP: the Opteron 880 beats Xeon MP by 20% to 30%. We have seen this before as the Opteron has always outrun "NetBurst" based CPUs in MySQL. The good news for Intel is that the new Core architecture is no less than 52% faster in MySQL when we compare the 3 GHz Xeon DP with the 3.2 GHz Xeon MP.

We also noted something strange: the Xeon MP performs better with hardware prefetch disabled. Below you can see our findings. All numbers are expressed in queries per second served by the server (Y-axis); and the X-axis shows the number of concurrent accesses.


Hardware prefetch lowers performance by about 1% to 4%, while Hyper-Threading allows the Xeon MP to make better use of its potential and increases performance by 7% to 9% at the higher concurrencies.

Secure Socket Layers RSA Performance Analyses, Power, and Conclusion
POST A COMMENT

33 Comments

View All Comments

  • duploxxx - Monday, November 13, 2006 - link

    Its nice to say that the new Intel system's have the RAS support and the AMD one not, however keep in mind that you are using an old opteron socket (you can say you have the latest revision 2006).

    AMD's Opteron 800/200-series (1207-pin, Socket F). The 1207-pin Socket F "Santa Rosa" core AMD Opteron CPU features DDR-2 memory support and Virtualization technology, in addition to Memory RAS security.
    Reply
  • Slappi - Sunday, November 12, 2006 - link

    Please sell your Intel stock and then rewrite the article please.


    Thank you,

    Slappi
    Reply
  • LuxFestinus - Tuesday, November 14, 2006 - link

    quote:

    The first problem with the review is credibility. The author, Johan De Galas, is the same person who did the worst server comparison I've ever seen: http://www.anandtech.com/IT/showdoc.aspx?i=2772&am...">http://www.anandtech.com/IT/showdoc.aspx?i=2772&am....

    This latest review has a lot of comments and odd arrangements that suggest bias.

    Page 1:

    TDP numbers are mentioned without stating that Intel's are not the same as AMD's nor mentioning that for Intel you have to include both the Northbridge memory controller and the higher power draw of FBDIMM.

    At the bottom of this page we do see the biggest reason for doing a pro-Intel server review:

    The lucrative 4 socket market was and is still dominated by the 8xx Opteron, which managed to capture up to 50% of the market share

    Page 2:

    Displaying comparison graphs directly from Intel. Where are the graphs from AMD? This makes me wonder if Intel co-authored this review.

    Trying to downplay AMD's huge gains in 4-way servers by suggesting that AMD only gained share because Paxville was so bad.

    Giving Intel praise for improvements to a poor design:

    Thanks to the shared and inclusive nature of the L3-cache coherency traffic between the four CPUs is significantly reduced.

    Yes, it is reduced but FSB cache coherency along with MESI is inferior in every way to AMD's cache coherency.

    Page 3:

    We start off by using a lower Opteron machine:

    The HP ProLiant DL585 available in the labs was not the recently introduced DL585G2 which features DDR2, the new AMD Opteron socket F

    Criticizing a superior design with a trivial (and incorrect) comparison to a worse design:

    The quad dual core configuration generates more cache coherency traffic, as the 8 cores of the Opteron have to keep 8 L2 caches coherent while the Xeon MP has to keep track of 4 L3 caches.

    This is false. Cache coherency between L2 pairs on the same chip do not use HyperTransport. The chip to chip coherency would be the same as Intel if AMD used MESI and had the same size cache. However, since AMD uses MOESI there is an improvement. And, since AMD's cache is much smaller there is again much less cc traffic. Finally, this still completely misses the point that 100% of Intel's cache coherency traffic still travels over the FSB while AMD's does not. This is about as biased or technologically ignorant of a comparison that anyone can make.

    Page 4:

    Finally at the bottom of the server overview page (where it is less likely to be seen) the author admits that Intel's TDP is not the same as AMD's and admits that FBDIMM draws more power. However, he fails to give any actual numbers to see what the real comparison is. In other words, on page 1 the stated numbers favor Intel, however the correction on page 4 contains no actual numbers to see who is really ahead in power draw. The author is either trying to favor Intel or is incredibly unprofessional.

    Page 5:

    At the top of this page the author tries to suggest that Anandtech's poor reviews are due to a lack of support from the manufacturers.

    In case you're wondering why we chose to use the fastest Xeon DP, the second fastest Xeon MP, and the second fastest Opteron, the reason is simple: those were the CPUs that were made available to us.

    Sorry but the biases in this review are not related to processor speed. Also, the 2.4Ghz Opteron is not the second fastest; the Opteron 854 runs at 2.8Ghz. 2.4Ghz is actually the third fastest Opteron.

    The AMD server has slower 333Mhz DIMMs while Intel gets 400 and 533 Mhz.

    Page 6:

    The Woodcrest SpecInt rate numbers are quite good for dual core. However, it isn't clear how much the numbers might be boosted by large cache. Nor does it make any sense to include these with quad numbers since Woodcrest might not scale 100%. However, we do see another common graph cheat where IBM's Power numbers were included to prevent AMD from having the top spot in the chart. Also, the AMD number is low at 160. It should be about 176. It should also be noted that Spec is in a bad position at the moment. No new numbers are being added to update the old 2000 database and there are not yet enough 2006 numbers for a good comparison. I have to wonder if Anandtech will still be quoting Spec numbers in the second half of 2007.

    Page 7:

    Curiously, the Xeon name is shown prominently in the graph whereas the Opteron machine is simply referred to as an HP.

    The SpecJbb test shows what a big difference proper compiling makes. In the second graph the quad opteron is almost identical to the quad xeon. However, the author tries to downplay this:

    This is good and bad news for AMD: it means that the Opteron 880 can compete with the more expensive Xeon MP, but it also means that the Opteron requires more "manual" optimization than the Xeon MP. The Xeon MP performs at the same level with 4 instances as it does with one.

    There is no mention of the fact that Xeon's I/O requires performance stealing software patches to match what Opteron does natively. Nor is there any mention of the fact that two Intel Xeons have to share the same bus which cuts performance in half when the memory bandwidth is saturated.

    And, at the bottom of the page the author makes sure to mention higher numbers for Intel that are not in the graph. Apparently, a tie is not acceptable. The new numbers suggest a whopping 27% lead for Intel. However, if we divide the Intel numbers by the difference in DIMM speed Intel's lead drops to a much smaller 6%.

    Page 8:

    Although Opteron does well overall and demolishes the P4 Xeon this test is suspect. The graph increases faster from 4 to 8 threads than it did from 2 to 4. This pretty much goes against every theory of processor operation. The test code has a problem of some kind. A normal graph would show either the same rate of increase or more commonly a slight dropoff.

    Page 9:

    The MySQL numbers are a waste of time. First of all the test is improperly configured:

    We optimized for a server with 4GB of RAM.

    Why optimize for 4GB's when the servers have 8 and 16 GB's of memory? It would be rare to find someone using a database server with only 4 GBs. Remember that the hardware can handle 64 GB's of memory with the slow DDR 333 that they are using in these tests.

    We also see an anomalous increase in the Woodcrest graph between 50 and 100. This is not normal and again shows some problem with the test code.

    Page 10:

    The server comparison is ridiculous. Why compare an older chipset and processor to Intel's newest? A socket F comparison would be much more professional and unbiased. Remember that the recent RAS features for Opteron (which are important in a high level server) came with Revision F.

    We see grudging admissions of unrealistic cache based results:

    In applications where the large L3 cache doesn't play a big role, the relatively poor server performance of the "NetBurst" architecture becomes visible again

    And, a grudging admission that this server chip is still inferior to Opteron:

    In a nutshell, the new Xeon MP will have a hard time convincing people who are leaning towards an Opteron server or want the best performance/watt.

    Very true. Now, of course, the author has to try to salvage the review by making a completely false claim:

    But on the other hand, the decent performance and superior RAS features will keep the customers who desire high availability in the Intel camp

    Or they could just buy a real Opteron system that does have these RAS features. This one server does not represent the entire Opteron server market.


    Taken from Scientia's post here:http://www.amdzone.com/index.php?name=PNphpBB2&...">AMD Forum Board
    Reply
  • Kiijibari - Sunday, November 12, 2006 - link

    They are misleading as it is unclear what you mean with "mem bandwidth".
    Is it FSB bandwidth ? System memory bandwidth ? CPU bandwidth ... ?

    It is correct that Intel can deliver 21 GB/s from the memory, however one CPU so far can "just" can handle ~11GB/s. So why should 1 Xeon DP have a memory bandwidth of 21 GB/s ? That statement is not valid, if you limit it to one CPU.

    Obviously, you meant the System memory bandwidth, but then I really wonder about your Opteron Socket-F numbers ...

    First it would be only fair to write the system bandwidth for a 2P System(or whatever compares to the Intel configuration), too. This would be then ~21 GB/s, too, for a 2P Opteron System, 42 GB/s for a Quad System.

    Then I wonder how you calculate that 8.5 GB/s mentioned with the Socket-F Opterons.

    As far as I know, these chips support DDR2-667 and that means 10.6 GB/s, not 8.5. Please be fair and correct at least that obvious error ...

    cheers

    Kiijibari
    Reply
  • spaceoddity - Saturday, November 11, 2006 - link

    Hi Johan,

    Thank you very much for doing some Linux benchmarks. They are not easy to come by. There are virtually no Linux benchmarks for desktops (perhaps understandable, but frustrating for us Linux users), but for servers, which is where Linux has a sizeable presence, they are always welcome. I hope Anandtech continues to provide good Linux/UNIX benchmarks, and doesn't abandon them for more windoze benchmarks, which are everywhere anyway.

    Cheers!
    Reply
  • JohanAnandtech - Sunday, November 12, 2006 - link

    Well I firmly believe the marketshare of linux servers can only grow and that therefore linux benchmarking will only get more important. A colleague of mine pointed out that Novell has launched e-directory: a very solid alternative to MS Small business server with the same functionality and ease of use, but much cheaper per connection, and with the ability to grow with the enterprise.

    It is just yet another reason why Linux on servers is so attractive besides much lower cost and much more control over your own IT infrastructure
    Reply
  • Justin Case - Saturday, November 11, 2006 - link

    In the OpenSSL 1024-bit signs, the quad Opteron has an almost 40% advantage over the Xeon when using 8 threads (in fact, that advantage rises to more than 90% when using optimized binaries), and is still the best of the bunch at 16 threads (32 wasn't tested), and yet the article text completely fails to mention this.

    It mentions the point where the (more expensive and more power-hungry) Xeon has its biggest advantage (4 threads, with a whooping 9% advantage over the Opteron), and the point where the Sun server (even more expensive) has its biggest advantage (32 threads, but the Opteron wins if using optimized binaries), but completely ignores the Opteron's trouncing of all the competition at 8 and 16 threads, and the fact that the Xeon 5160 cannot scale past its 4-thread peformance at all.

    http://images.anandtech.com/reviews/it/2006/tulsa-...">http://images.anandtech.com/reviews/it/2006/tulsa-...

    So the fact is the Opteron can handle a load of 10000 signs per second (over 12000 with optimized binaries), while the Xeon can't even reach 6000 (6200 with optimized binaries).

    http://images.anandtech.com/reviews/it/2006/woodcr...">http://images.anandtech.com/reviews/it/...rest-lin...

    And yet, according to the article, "the Opteron no longerbeats the Xeon". Huh? A 40% advantage isn't enough to win? Who compared the scores, Diebold?

    So what if the Xeon performs better when you cripple the Opteron by reducing the number of threads? In any real-world situation, the server admin is going to use the number of threads that delivers the best performance (and is going to use the optimized binaries, of course, if he's competent). Just because the Xeon tops out at 4 threads doesn't mean the (better) results delivered by the Opteron should be discarded.

    If this was a "normal" Anadtech article, I wouldn't be surprised by the bias and "selective reporting", but I never expected Johan to "tow the party line" like this.
    Reply
  • JohanAnandtech - Sunday, November 12, 2006 - link

    From the article:

    quote:

    Still, our previous conclusion stands: clock for clock, the Opteron is quite a bit better at this than the Xeon "Core" architecture (Xeon 5160) and a lot better than the Xeon "NetBurst" architecture (Xeon MP 7130).


    Yeah, I am really doing Intel a favor here, pointing out one of the weaknesses of their core architecture and showing yet another very weak point of Netburst.

    quote:

    the fact that the Xeon 5160 cannot scale past its 4-thread peformance at all


    Again from the article:
    quote:

    One thread of OpenSSL Signing per core is optimal

    More than one thread per core doesn't give any performance advantage (unless you have a multithreaded CPU) so of course a Dual Xeon 5160 doesn't scale beyond 4 threads, just like a Dual Opteron. As openSSL scales almost perfectly, The important thing here is performance/core, as you don't want to pay for multi socket machine if you don't want to.


    quote:

    but I never expected Johan to "tow the party line" like this.


    You should definitely read more carefully. "Selective reporting" would not include the MySQL, Power consumption or even the NUMA specjbb results as they are favorable for the Opteron.
    Reply
  • Justin Case - Monday, November 13, 2006 - link

    The fact is, the quad Opteron box reviewed (DL585) _can_ sustain higher performance than the Xeon 5160 (close to 90% higher, using optimized binaries), correct? So, unless the Opteron box costs twice as much as the 5160 box (identically supported and configured, apart from the CPUs / MB), it delivers more bang for the buck.

    Is this a server test or a CPU core test? It's filed under "IT / Computing", not under "CPU / Chipset", so I have to assume it's supposed to be the former.

    So what if one server has twice (or 100 times) as many cores as the other? You might as well argue that the servers must be compared at the same clock speed, with the same amount of on-die cache, or with the same type of memory. All those things might be relevant when comparing CPU architectures (then again...), but not when you're comparing complete systems. The whole point of a server comparison is to see what kind of performance you get for the price. If one server is 70% more expensive but 80% faster, it's still a better deal for people who need the extra performance. That extra performance can be due to a higher clock speed, more CPUs, more cores per CPU, better memory bandwidth, a dedicated coprocessor, magic imps, whatever. But it doesn't make any sense to "compensate" for those variables (or for one of those variables) and ignore the fact that server X can and does deliver better performance than server Y when both make full use of their resources.

    At 4 threads, the 5160 is the fastest system of those tested. So what if it has a 20% clock speed advantage? It's still the fastest, right? You're not going to artificially cripple its clock speed to match the others; doing that wouldn't make any sense (because, in the real world, no buyer / server admin would do that). So why cripple the other systems by limiting the number of threads they are running? In that test (with unoptimized binaries), the Sun box reaches the highest performance, period. With optimized binaries, the Opteron box manages to pull slightly ahead. Of course, then you have to take price into account, and maybe for a lot of people the 5160-based server will be the better deal, but you can't say it performs better when, objectively, it does not.
    Reply
  • nah - Saturday, November 11, 2006 - link

    Great job Johan---as always Reply

Log in

Don't have an account? Sign up now