This is a read-only archive. Find the latest Linux articles, documentation, and answers at the new Linux.com!

Linux.com

Feature

Comparing MySQL performance

By Tony Bourke on February 09, 2005 (8:00:00 AM)

Share    Print    Comments   

With the introduction of the 2.6 Linux kernel, FreeBSD-5-STABLE, Solaris 10, and now NetBSD 2.0, you might be wondering which of them offers superior database performance. In my previous article, I discussed the tools I chose to test these venerable operating systems and the methodology by which they were tested. The result is this MySQL performance comparison between OpenBSD 3.6; NetBSD 2.0; FreeBSD 5.3 and 4.10; Solaris Express (build 69); and Linux 2.4 and 2.6 (Gentoo-based). Read on for the results.

Before you skip ahead to the results section, bear one thing in mind: the question this article asks is "how would MySQL perform on these operating systems?" It is not, "Which is the best operating system?" In any benchmarking test, there will winners and losers, but the highest performer in one category for a limited set of tests does not a "best" operating system make. Each operating system has its strengths and weaknesses, and a single benchmark can only show a single strength or weakness with a very limited set of parameters.

This article has no agenda other than to show what performance one might expect from MySQL on a number of different operating systems. Such information can be helpful to determine what kind of performance benefit or detriment to expect when running a favored operating system, and allow one to adjust accordingly.

Super Smack 1.2

The Super Smack tests are both CPU/OS-bound, since the data set is relatively small and thus easily cached. Still, as you can see, there was quite a difference in performance between the operating systems. The table type is MyISAM. I invoked Super Smack with the following options:

super-smack /usr/share/smacks/select-key.smack 10 10000

super-smack /usr/share/smacks/update-select.smack 10 10000

For the select-key test, NetBSD 2.0 bested all the other operating systems. Linux 2.4 and 2.6 also did very well, and we see OpenBSD 3.6 beating the other FreeBSD results.

v2graphs_1-CPU-select-key.gif

When moving to 2-CPUs, the results change dramatically. Linux is the only operating system to effectively scale for these results, holding a commanding lead. OpenBSD and NetBSD's dominance fell off, and we can see a weakness in FreeBSD 4.11's libc_r.

v2graphs_2-CPU-select-key.gif

For the update-select tests, Linux 2.4 and 2.6, along with NetBSD 2.0 again took the lead. FreeBSD 5.3 had conspicuously lower showings, and OpenBSD 3.6 and FreeBSD 4.11 with linuxthreads were about average.

v2graphs_1-CPU-update-select.gif

As with the select-key tests, only Linux 2.4 and 2.6 kept their leading positions when going to two CPUs. NetBSD 2.0 dropped off as it had before -- but not quite as dramatically -- and barely holds onto second place behind the Linux results.

v2graphs_2-CPU-update-select.gif

SysBench 0.3.1 1M Rows

The SysBench 1M rows provided a good CPU/OS-bound test case. Since I performed one test run to prime the system, almost all of the data was cached by MySQL, so there was little or no disk access.

I used the InnoDB tables for the SysBench tests. SysBench was invoked with the following options:

1M Rows:

To setup:

sysbench --num-threads=10 --test=oltp --mysql-host=172.16.3.7 --mysql-user=root --mysql-password=mysql --oltp-table-size=1000000 prepare

To run:

sysbench --num-threads=10 --test=oltp --mysql-host=172.16.3.7 --mysql-user=root --mysql-password=mysql --oltp-table-size=1000000 run

To cleanup:

sysbench --num-threads=10 --test=oltp --mysql-host=172.16.3.7 --mysql-user=root --mysql-password=mysql --oltp-table-size=1000000 cleanup

10M Rows:

To setup:

sysbench --num-threads=10 --test=oltp --mysql-host=172.16.3.7 --mysql-user=root --mysql-password=mysql --oltp-table-size=10000000 prepare

To run:

sysbench --num-threads=10 --test=oltp --mysql-host=172.16.3.7 --mysql-user=root --mysql-password=mysql --oltp-table-size=10000000 run

To cleanup:

sysbench --num-threads=10 --test=oltp --mysql-host=172.16.3.7 --mysql-user=root --mysql-password=mysql --oltp-table-size=10000000 cleanup

Here we can see both the 2.4 and 2.6 Linux kernels on top, with Solaris 10 and FreeBSD 4.11 with linuxthreads nipping at its heels. NetBSD 2.0 and FreeBSD 5.3 with KSE and linuxthreads performed average, and FreeBSD 4.11 with libc_r and OpenBSD 3.6 performed dramatically worse.

v2graphs_1-CPU-1M-rows.gif

With the addition of a second processor for the 1M rows test, the landscape changes somewhat dramatically. Solaris 10 gets closer to the performance of Linux 2.4 and 2.6, and NetBSD 2.0 drops off significantly. FreeBSD 4.11 with linuxthreads scales relatively well.

v2graphs_2-CPU-1M-Rows.gif

SysBench 0.3.1 10M Rows

The SysBench OLTP test with 10M rows creates a 2.6 GB InnoDB file, which can't be cached (by MySQL or the OS) in the 512 MB of physical memory. As a result it's a very I/O bound test, and the transaction rate drops across the board.

For the single-CPU tests, Linux again takes the lead with both kernels. Solaris, FreeBSD 5.3 (linuxthreads and KSE), and FreeBSD 4.11 (linuxthreads) score about the same. FreeBSD 4.11 with libc_r performs badly, as does OpenBSD 3.6. The real surprise is how far behind NetBSD 2.0 fell. It had done very well in the other tests, but in this I/O bound test, the results were the worst in the group. I tried several combinations of file system tuning (softdeps, no softdeps, noatime, etc.) but I wasn't able to get the transaction rate any higher.

v2graphs_1-CPU-10M-Rows.gif

Since the 10M row test was I/O bound, adding an extra CPU wouldn't theoretically help, since there's only one hard drive spindle to service the MySQL workload -- and that's pretty much how it worked out.

v2graphs_2-CPU-10M-Rows.gif

Scalability: overview

Since the results are shown for both single and dual-CPU modes, we can see how well these operating systems scale with MySQL by taking a look at the percentage of delta between the runs. Different workloads lend themselves to CPU scaling better than others. Read operations of course benefit since there's no table-locking for a read, but that's only if the data is cached. Write operations are limited by table locks or log writes as well as disk drive spindles, so scalability is limited. By graphing the percentage difference between single and dual-processor results, we can get an indication of how well these operating systems scale with MySQL.

Scalability: Super Smack

For the select-key tests, Linux 2.4 and 2.6 did very well, getting almost double performance with the addition of a second processor. FreeBSD 5.3 with KSE did fairly well too, with FreeBSD 5.3 with linuxthreads and FreeBSD 4.11 with linuxthreads doing so-so. FreeBSD 4.11 with libc_r had no benefit from the second CPU. OpenBSD 3.6 and NetBSD 2.0 suffered as a result of adding a CPU, with NetBSD 2.0 seeing a dramatic drop in performance.

The update-select tests were more subdued due to the write-nature of the tests, but Linux again showed the most gains. FreeBSD 5.3 with both the linuxthreads and KSE threading libraries did fairly well, and surprisingly FreeBSD 4.11 with linuxthreads showed almost no improvement. NetBSD 2.0 again showed a performance decrease, as did OpenBSD 3.6, although to a lesser extent.

v2graphs-Delta-Super-Smack.gif

Scalability: SysBench 1M Rows

For the 1M Rows tests, you can see that Linux 2.6 and Solaris scaled particularly well, seeing a bit over 50% increase in performance with the addition of a new processor. Linux 2.4 and FreeBSD 5.3 (both linuxthreads and KSE) scaled fairly well, as did FreeBSD 4.11 with linuxthreads. NetBSD 2.0 and FreeBSD 4.11 with libc_r scaled poorly, and OpenBSD 3.6 ended up with a performance loss.

v2graphs_Delta-1M-Rows.gif

For the 10M Rows, the operations were I/O bound (and thus bound to the single disk), and didn't see any significant performance difference for any of the operating systems between 1 and 2 CPU runs, topping out at +/-2%. The exception was NetBSD 2.0, which suffered a 9% decrease in performance with the addition of another processor, a drop-off which is consistent with the other test results.

v2graphs_Detla-10M-Rows.gif

Scalability: overall

Overall, Linux 2.4, Linux 2.6, and Solaris scale the best with the tested MySQL operations. FreeBSD 5.3 (KSE and linuxthreads), and FreeBSD 4.11 (linuxthreads) also scale fairly well. FreeBSD 4.11 with the default libc_r threading, NetBSD 2.0, and OpenBSD 3.6 don't seem to benefit at all from the addition of multiple processors, and in some cases the results were even worse than the single-CPU configuration.

The Solaris issue

I ran into a strange issue with Solaris 10 for the 10M row SysBench tests. While Solaris had done very well for the 1M tests, it did extremely poorly in the 10M tests, getting the lowest score by far -- roughly 3.6 transactions per second, which is lower than even NetBSD 2.0's results. This was roughly one-seventh the Linux scores, and didn't seem to make any sense. I checked with Peter Zaitsev, and he put me into contact with Jenny Chen of Sun, and we proceeded to try to figure out what the cause of the bad performance was.

Chen recommended mounting the file system without logging, and to set the sticky bit (chmod +t) to the InnoDB data files and logs. None of those steps seemed to help, so I explored some more.

The final answer I found in the legendary book Sun Performance Tuning: Java and the Internet by Adrian Cockcroft and Richard Pettit. The solution was mounting the data partition with the forcedirectio option. This prevents the file system from being cached at all by Solaris. When I ran the test, the swap drive was completely idle, and the results were dramatic: 21 transactions per second versus 3.6 transactions per second. Oddly enough, setting the sticky bit actually hurts performance by about 1 transaction per second. I get about 20 with +t, and about 21 without +t. It doesn't make any difference in these tests if the file system is mounted with logging enabled or disabled either. The noatime option also had no effect.

Although forcedirectio isn't mentioned in the MySQL documentation, I see that the directio option is specifically recommended on page 161 of Sun Performance Tuning for situations with a large data file (the InnoDB data file was 2.6 GB for the 10M row test). While the book is from 1998 and only covers up to Solaris 2.6, it covers expertly the principles of performance tuning which would apply to all operating systems.

Among the operating systems tested, this caching scenario seems to be unique to Solaris 10. None of the other operating systems, including NetBSD 2.0 (which also had a bad showing), saw any swap activity during the 10M row tests.

Conclusion/final thoughts

Both Linux 2.4 and 2.6 had the strongest showing overall for these tests, dominating just about every benchmark no matter the workload. Scalability for both kernels was also excellent with addition of an extra processor. In fact, I was surprised how well 2.4 had done, as I had somewhat expected 2.6 to show at least a noticeable, if slight, increase over 2.4. Instead, they took turns besting each other from test to test -- and in scalability -- for a fairly even overall showing.

Solaris 10 had a very strong showing as well, having great speed as well as great scalability. I think the results show that Solaris 10 is a great platform for MySQL. Of course, I didn't have Super Smack results as I couldn't get Super Smack to port to Solaris (as detailed in the previous article), so bear that in mind.

NetBSD 2.0 also had a very strong showing, although it was tarnished by two issues. One, MySQL on NetBSD 2.0 doesn't scale with the addition of CPUs. The results would seem to indicate that it might be wise to run a uniprocessor kernel even if two processors are available. The other issue was the poor I/O performance for the 10M row SysBench test. The SMP scalability issue is easy to understand since, to be fair, this is the first NetBSD release to support multiple processors. The I/O issue is more of a mystery, however.

FreeBSD 5.3 did relatively well in both KSE and linuxthreads mode, although with all the work that's been done in the SMP and threading realms, I was a little disappointed with the results. Still, it seems that the native threading model for the production release of FreeBSD-5 is ready for prime time, and can replace the long-standing FreeBSD convention of using linuxthreads with MySQL.

For FreeBSD 4.11, however, linuxthreads definitely helped with performance (and in many cases outperformed FreeBSD 5.3). With libc_r, performance lagged far behind linuxthreads for many tests, and there was little scalability. I would say it's highly advisable to build your FreeBSD 4.11 MySQL binary with linuxthreads.

For all the time it took, I think the tests were worth it. I learned quite a bit about MySQL performance in general, and I'd like to again thank Peter Zaitsev for his methodology recommendations and input, as well as Jenny Chen from Sun for her input.

Share    Print    Comments   

Comments

on Comparing MySQL performance

Note: Comments are owned by the poster. We are not responsible for their content.

Solaris

Posted by: Anonymous Coward on February 09, 2005 10:49 PM
Benchmark the final release of Solaris 10 please! b69 is 3 months old and beta!

#

Gentoo unfair advantage

Posted by: Anonymous Coward on February 09, 2005 11:33 PM
This article was certainly interesting but I'd like to know more about the Gentoo installation that was used for the testing. The other OSes used come in a generic x86 compatible binary form whereas Gentoo is normally compiled for the exact sub-architecture making use of all the processors features and compiler optimizations. This will make Gentoo faster than even another Linux distro using the same kernel. I'd like to see this article updated w/ a version of Redhat or SuSE used.

#

Re:Gentoo unfair advantage

Posted by: Anonymous Coward on February 09, 2005 11:42 PM
Not necessarily. Gentoo likely has SSP and ProPolice turned on at the C compiler level, meaning just about all the OS is compiled with plenty of paranoia. And, since he did not mention what compiler codes he used, it was probably just a generic setup. That means, low optimization (essentially, -O2 IIRC).

A well optimized Gentoo setup with the latest C compiler, NPTL threading throughout (read: Linux 2.6 specific) and the paranoia opted out would probably perform considerably better than the benchmarks the author had, especially if he took the time to rebuild the toolchain a few times just to be sure that it was as good as it gets. I know it's just a guess, but I feel pretty confident about it.

#

Re:Gentoo unfair advantage

Posted by: Anonymous Coward on February 10, 2005 05:35 AM
Maybe you feel confident about your guess because you have no experience and are just making stuff up. Best case scenario, compiler optimizations will get you a minor performance boost, maybe 5% or so. Often this will come along with bugs and instability, as the generated machine code may not be correct. And rebuilding your toolchain with optimizations will not do anything. In theory the most it could do is make your compiler compile faster, not produce faster binaries. But in reality it doesn't even do that. Recompiling it multiple times is simply moronic, this is like believing in magic performance fairies or something equally rediculous, it makes no sense, and anyone who understands even the basics of unix would realize what a dumb idea that is.

#

Re:Gentoo unfair advantage

Posted by: Anonymous Coward on February 10, 2005 05:59 AM
Apparently your reality is different than ours. Recompiling the toolchain to take advantage of a different c compiler and/or libc does make a sizable difference. And so does compiler optimization.

True, CPU model specifics often amount to little, but -O2 to -O3 and generally fully optimizing all libraries, etc. that MySQL depends on as well as MySQL itself will make *huge* differences. Maybe 5% or so makes no difference to you... but if you need 5%, it does in the real world. P.S. going from GCC 3.3 to 3.4 alone can pay massive dividends to MySQL well beyond 5%.

Clearly, it's you out of the loop. Let this be a warning to you, you aren't as well read as you think.

#

Remove head from anus, then post.

Posted by: Anonymous Coward on February 10, 2005 06:08 AM
Recompiling the toolchain is not required to use a different C compiler, and the entire point here is benchmarking OS's, so keeping the same compiler is required. Recompiling GCC with --imafucking-retard --rice-me-out doesn't make gcc create faster binaries, it could only possible make gcc compile things in less time, which it doesn't actually do in reality.

And -O2 is used everywhere by default, and is the only consistant large performance gain. And 5% is irrelivant in a benchmark like this, and is not worth the risk of stability problems that come with trying to over-optimize everything.

Let this be a warning to you, you need to read posts before replying to them, otherwise you end up making up nonsense that has nothing to do with what you are replying to, like changing compilers.

#

Re:Gentoo unfair advantage

Posted by: Anonymous Coward on February 10, 2005 05:46 PM
I do not believe you have actually benchmarked this.

For modern processors, going to -O3 from -O2 is normally a performance penalty! The code size / speed tradeoff has a discrepancy where the cache kicks in. Try it!

That is a big tradeoff with Gentoo, not many maintainers actually try the software with different flags to see what's better. So you end up with using O3 and your performance drops. Compare that with Debian who has many very active maintainers doing that job for you. That is why Debian is faster in a stock installation. (According to the only public benchmark on the web.)

#

Re:Gentoo unfair advantage

Posted by: Anonymous Coward on February 10, 2005 02:05 AM
In <A HREF="http://software.newsforge.com/article.pl?sid=04/12/27/1238216&tid=152" title="newsforge.com">another article</a newsforge.com> the author explains how he installed gentoo. He does not mention whether processor specific optimizations where used. So saying he did is just speculation. He just mentions he used nptl. I agree more details about the options he used would be good.

It is my understanding that *BSD use ports which are similar to gentoo's portage and they also compile stuff from source. He can just as easilly enable processor specific optimizations (someone please correct me if I am wrong). So it seems to me that using gentoo is actually the fairest comparisson you can get against *BSD.

You are overestimating the improvements of compiling stuff for your processor . Normally it will give you around 2-5% if you are lucky. So even if he did use unfair optimizations, that would not change the benchmarks all that much.

#

Re:Gentoo unfair advantage

Posted by: Anonymous Coward on February 11, 2005 03:52 PM
Depends on how you install software in FreeBSD. In much the same way that you can get binary packages for Gentoo, so can you for FreeBSD. I suspect it's much more likely that the FreeBSD was installed from compiled packages because if you choose to install something during installation or from a nice graphical installation after installation it will get a precompiled package. So the "normal" routine is probably more balanced to packages, while the "normal" routine for Gentoo is decidedly compile yourself.

#

Re:Gentoo unfair advantage

Posted by: Anonymous Coward on February 11, 2005 04:40 PM
Well, a few things off the top of my head... a program compiled to support 386s compared to one compiled specifically for PPro+ will commonly result in 15-20% performance increases. 4.x included support for 386 CPUs in GENERIC... it also includes support for EISA, every SCSI and network device known to god or man, every RAID device, your Diamond Rio MP3 Player, TCP/IP over the parallel port, as well as numerous other usefull goodies all statically linked into the kernel. I haven't seen an x86 Linux distro do this in many many moons (I'm not even 100% sure you even CAN do this anymore, but I'd say it's 98% likely) Irregardless, I'm positive this was NOT done with the Linux install.

Further, he specifically did NOT use the ports version on any platform... this means that any OS-specific tweaks in the ports system didn't get applied.

But comparing ANYTHING with a BSD GENERIC kernel will yield somewhat biased results... even worse with the 4.x systems (5.x no longer allows 386 support in the same kernel which supports 486 CPUs to avoid such issues)

However, I wouldn't go so far as to say it's unfair to do this... I doubt he spent 30 minutes carefully selecting kernel build options for the Linux distro, just as he didn't do the same thing for the BSDs... if Linux happens to give a more optimized kernel "out of the box", that means more Linux systems will have an optimized kernel (But the fact that other distros flat out wouldnt' work with his hardware is telling)

#

Re:Gentoo unfair advantage

Posted by: Anonymous Coward on February 10, 2005 05:41 PM
No, it will *not*. Not with MySQL. They compile their official binaries with a proprietary Intel compiler, and go through with a lot of testing and stuff to see that the compiler didn't introduce any bugs or performance penalties. You *really* want to use the official MySQL build!

#

Re:Solaris

Posted by: Anonymous Coward on February 11, 2005 06:23 AM
And the final release is noticably faster than the early beta (b69) tested. I assume the beta versions had much debugging etc. turned on.

#

No OpenBSD 3.6 in conclusion?

Posted by: Anonymous Coward on February 09, 2005 10:54 PM
Why isn't OpenBSD 3.6 mentioned in the conclusion/final thoughts?<nobr> <wbr></nobr>:(

#

Re:No OpenBSD 3.6 in conclusion?

Posted by: Anonymous Coward on February 09, 2005 11:13 PM
Yes, that is odd that he mentions how NetBSD 2 is the first release for Net with SMP and doesn't mention it is the same with OpenBSD 3.6.

#

Re:No OpenBSD 3.6 in conclusion?

Posted by: Anonymous Coward on February 09, 2005 11:19 PM
Probably an oversight that he did not mention OpenBSD in the conclusion.

If he does edit the conclusion to add OpenBSD, I hope he has some thoughts on the effects of ProPolice (a stack guard) on the tests. The OpenBSD kernel is compiled with ProPolice enabled, and by default all programs will also be compiled with ProPolice (unless specifally disabled),

#

Re:No OpenBSD 3.6 in conclusion?

Posted by: Anonymous Coward on February 09, 2005 11:38 PM
>I hope he has some thoughts on the effects of
>ProPolice (a stack guard) on the tests. The

Actually, both Gentoo kernels he used for Linux are also very likely (almost certainly) using Propolice.

From my Gentoo box:

gcc version 3.4.3 20050110 (Gentoo Linux 3.4.3.20050110, ssp-3.4.3.20050110-0, pie-8.7.7)

#

Re:No OpenBSD 3.6 in conclusion?

Posted by: Anonymous Coward on February 13, 2005 01:43 AM
Except that propolice is not enabled by default, unless you select the hardened version with gcc-config (or "hardened" in your USE flags).

#

Solaris settings

Posted by: Anonymous Coward on February 09, 2005 11:54 PM
Actually mounting HFS under solaris without logging *decreases* IO throughput. Every document on DB scalability regarding Solaris recommends to mount FS *with* logging.

Another thing You have to check - if MySQL does caching (most databases do - but don't know abut MySQL) is if Your FS block size matches DB cache block size. If Not You can init Your HFS filesystem with block size equal (or multiply) to DB block size (in case of oracle it speed up heavy IO operations 2-3 times).

And You can use forcedirectio mount option for data/index files (but only for these files!) - so You flush Your data without any OS caching and baypassing kernel. (In case of oracle is speed up IO operations by factor 4(!)).

Finally please check Your throttle kernel parameter and adjust sd_max_throttle if needed.

#

hmmm

Posted by: Anonymous Coward on February 10, 2005 12:21 AM
freebsd 5.3+kse was an UTTER dissappointment. kinda throws out the idea that such invasive SMPng changes were worthwhile. Shame DragonflyBSD isnt 'stable'? enough to test. I would like to see some realworld numbers coming out of it v fbsd4+5...

I honestly expected better of fbsd5.3+kse..

#

Re:hmmm

Posted by: Anonymous Coward on February 10, 2005 03:45 PM
Nothing strange here, these "invasive SMPng changes" brought FreeBSD SMP code on the Linux 2.2 level, no more.
They are still struggling with giant kernel lock after all.

#

Re:hmmm

Posted by: Anonymous Coward on February 11, 2005 06:03 AM
FreeBSD 5.3 is an architectural mess. The code base
has been unmercifully hacked into something
resembling a plate of spaghetti. It was Matt Dillon
who said as much when he left to form
<A HREF="http://www.dragonflybsd.org/" title="dragonflybsd.org">DragonFly</a dragonflybsd.org>.
Matt recognized the the FreeBSD SMP code had
become unmaintainable, and incomprehensible.
Almost all the big name talent has migrated away from
FreeBSD: Hubbard, Smith, Dillon, Dyson, Lambert.
No big names left.

#

Re:hmmm

Posted by: Anonymous Coward on February 12, 2005 08:06 AM
I too was disappointed that DragonFlyBSD wasn't included. I'm quite interested in how the philosophy differences between FreeBSD-5 and DragonFlyBSD pay off in the real world.

#

How about Windows?

Posted by: Troels Arvin on February 10, 2005 03:24 AM
I think it would be interesting to see how MySQL performs on Windows, also.

#

Re:How about Windows?

Posted by: Anonymous Coward on February 10, 2005 05:41 AM
I agree. Lets see a comparison on win32.

#

Re:How about Windows?

Posted by: Anonymous Coward on February 10, 2005 06:04 AM
That would be an unfair comparison. MySQL was not design for windows, and it would probably run slower there. At the same time, if we used SQL server to test speeds on a windows and linux platforms, windows would easily outperform linux.

<A HREF="http://www.eweek.com/slideshow/0,3018,sid=0&s=1590&a=23120,00.asp" title="eweek.com">http://www.eweek.com/slideshow/0,3018,sid=0&s=159<nobr>0<wbr></nobr> &a=23120,00.asp</a eweek.com>

#

Completely worthless post.

Posted by: Anonymous Coward on February 10, 2005 06:21 AM
Thanks, but MySQL does run native, and your URL shows precisely nothing worthwhile to this test or discussion.

#

Re:Completely worthless post.

Posted by: Jeremy Akers on February 10, 2005 10:17 AM
His URL shows something worthwhile. It shows MySQL blwoing the socks off of SQL server. Which is just the opposite of what he said in his post.

-Jeremy

#

I'll explain it better...

Posted by: Anonymous Coward on February 10, 2005 07:08 PM
Well, you obviously didn't look carefully at that article. What it shows is this: First a comparison of several databases on a linux platform. MySQL and Oracle are the fastest, and SQL Server the slowest. But then they gave the chance to SQL to perform under Windows platform, and it increased it's performance dramatically (in fact it was faster than any other database on Linux).

So what I meant was this: If you get SQL Server to test the speed of Operating Systems (Linux and Windows), you'll come to the conclusion that Windows is much faster (Because SQL runs faster under Windows). So, I guess that if you use MySQL to test Linux and Windows performance, Linux will win easily (because MySQL runs faster under Linux).

I hope I made my point clear now.

#

Re:How about Windows?

Posted by: Anonymous Coward on February 10, 2005 07:31 AM
Whenever you will be able to run SQL Server on Linux, drop me a line..

#

Sorry the problems here.

Posted by: Anonymous Coward on February 10, 2005 02:44 PM
This is a unfair compare on one hand. That tests did not say what platforms they were done on. Note they all could have been done on windows. Ie Mysql is faster on linux and Mysql was updated to get more speed after the one that was tested.

#

Re:How about Windows?

Posted by: Anonymous Coward on February 10, 2005 05:49 PM
I disagree. MySQL is *very* mature on win32.

#

Re:How about Windows?

Posted by: Anonymous Coward on February 11, 2005 07:47 PM
I am always amazed at how closed minded some people are when it comes to Windows. It is unfair to draw any conclusions about Win32-based builds without doing the proper benchmarking. That is the purpose of benchmarking - to see how a system performs under certain conditions. It would be very useful to benchmark MySQL under Windows, perhaps even under different Windows versions instead of having people just post their opinions on the subject.

#

Horse's Mouth

Posted by: gatkinso on February 12, 2005 12:05 AM
Here is what they author had to say about Win32's absence:

"I didn't do Win32 because I don't have a copy (licensed or otherwise) of Windows 2000 or Windows 2003 server, pretty simple<nobr> <wbr></nobr>:) I don't know if Microsoft offers an evaluation version, but I don't have the money to spend on a licensed version, and I don't feel like doing a pirated version. The other operating systems are free, so that wasn't an issue."

So, no tin foil hat conspiracies or FUD machines in overdrive - simply a person who is M$ free (and probably loving it.)

#

Re:Horse's Mouth

Posted by: Anonymous Coward on February 12, 2005 08:13 AM
Yes, Microsoft does offer evaluation versions. I have a copy of Windows Server 2003 that came with a 360 day license for evaluation purposes.

I started at the "Get the Facts" site, but I'll bet there are many other ways to get it.

#

NetBSD performance

Posted by: Anonymous Coward on February 10, 2005 04:06 AM
I don't see it mentioned, so I'll ask. Did you enable PTHREAD_CONCURRENCY? You have to set that variable to the number of CPUs in your system, else you won't be able to run more than one thread at a time, even you have more than one CPU. No offence, but you seen to have good Linux knowledge and very little BSD-fu.

#

Re:NetBSD performance

Posted by: tokki on February 10, 2005 05:18 AM
I don't see it mentioned, so I'll ask. Did you enable PTHREAD_CONCURRENCY? You have to set that variable to the number of CPUs in your system, else you won't be able to run more than one thread at a time, even you have more than one CPU. No offence, but you seen to have good Linux knowledge and very little BSD-fu.

Sunofa. The $PTHREAD_CONCURRENCY environment variable wasn't set, as I had no idea it was an option. It wasn't documented anywhere that I can find on the NetBSD site, such as <A HREF="http://www.netbsd.org/guide/en/chap-whatsnew.html" title="netbsd.org">http://www.netbsd.org/guide/en/chap-whatsnew.html</a netbsd.org> or <A HREF="http://netbsd.gw.com/cgi-bin/man-cgi?pthread+3+NetBSD-current" title="gw.com">pthread(3)</a gw.com>.

It could very well be the issue. In the next few days I'll re-run the NetBSD tests with that set.

Now that I'm searching for it I do see it mentioned in the <A HREF="http://mail-index.netbsd.org/tech-userlevel/2004/10/22/0000.html" title="netbsd.org">mailing lists</a netbsd.org> as a <A HREF="http://mail-index.netbsd.org/netbsd-docs/2004/10/12/0000.html" title="netbsd.org">documentation issue</a netbsd.org>, so I wouldn't consider it a case of BSD-fu<nobr> <wbr></nobr>:)

#

Thank you.

Posted by: Anonymous Coward on February 10, 2005 06:26 AM
I look forward to seeing the re-run on NetBSD.

If you are up to it, some of us would be interested in seeing the Gentoo 2.6 test re-run with a complete toolchain recompile (twice to freshen it) using GCC 3.4.x and -O3 -march=somecpu -ftracer as the CFLAGS, with -fvisibility-inlines-hidden added as well to CXXFLAGS (and maybe without too, if you're up to it). Also if you really do this, please check to be sure that you aren't using the hardened version of GCC... and heck, no PIE or SSP if you can force it... I know it's going overboard but this is a similar setup to what I'm using. I use NPTL and turn off linux threads in LibC so as to expose NPTL to all apps. Performance on a FX-55 is awe inspiring with these procedures, in my experience.

If you aren't up to it, I understand.

#

Twice to freshen it?

Posted by: Anonymous Coward on February 10, 2005 06:39 AM
Please, if you lack the basic understanding to grasp that recompiling something "twice to freshen it" makes no sense and is along the same times as using voodoo to make your system faster, then don't suggest anything.

#

Re:Twice to freshen it?

Posted by: Anonymous Coward on February 10, 2005 06:59 AM
You're the same idiot above, I presume.

You change the C compiler, rebuild the toolchain (system) once to recompile all with the new compiler, and again to be absolutely sure that the entire toolchain is using the new features/headers/NPTL/compiler/etc. Rebuilding and rebooting into the kernel is also necessary to be sure that you're entirely up to date.

If you don't do this, you may miss something. In Gentoo, it's as easy as "emerge -e system ; emerge -e system" and come back to it in a day.

It does work. and it does make a difference. Go to gentoo.org and enter the forum, see for yourself. Obviously, this is not a procedure you'll do with a production server, though you might do it once.

And uh... please, stop making stupid and inflammatory comments, you bring them back at yourself.

#

You win the thread.

Posted by: Anonymous Coward on February 10, 2005 07:11 AM
Pointing to the gentoo forum as a place to find useful information from people who understand unix is honestly the funniest thing I have ever seen on the internet. Many combinations!

#

Re:Twice to freshen it?

Posted by: Jeremy Akers on February 10, 2005 10:21 AM
Yeah, and I like to do mine 3 times just to be absolutly sure everything got recompiled.

What a moron.

#

Re:Twice to freshen it?

Posted by: Anonymous Coward on February 10, 2005 05:53 PM
What is this? Some black art voodoo? Go back to your magic-reboot-cave, troll!

gcc produces the same code no matter what compiler or magic voodoo man produced *it*. If it doesn't, it is broken.

#

Re:Twice to freshen it?

Posted by: Anonymous Coward on February 10, 2005 12:41 PM
I will make one comment about this. (I don't know anything about gentoo) Recompiling twice isn't stupid if what he's recompiling is the compiler itself. The last time I updated gcc on Solaris, I recompiled it 3 times. I started out with gcc v272 and built gcc v295. The first generation gcc v295 that resulted from the build with v272 was one size. The second generation gcc v295 that I compiled from the first generation gcc v295 did not binary compare to the first generation gcc v295. The 3rd generation gcc v295 did compare identically to the second generation gcc v295.

#

Re:Twice to freshen it?

Posted by: Anonymous Coward on February 10, 2005 12:53 PM
The second generation gcc v295 that I compiled from the first generation gcc v295 did not binary compare to the first generation gcc v295.

Because you switched compiler version (first compile with 2.72 and then using 2.95), what do you expect?!

The 3rd generation gcc v295 did compare identically to the second generation gcc v295.

Because you did not change compiler (or version)

#

Re:Twice to freshen it?

Posted by: Anonymous Coward on February 10, 2005 06:05 PM
As someone who has never used Gentoo, I can appreciate how people think it is unecessary to compile your system twice. Why, most people would find it completely over the top to compile your system ONCE!

Regardless, I have rebuild gcc and toolchain twice on various systems (HP-UX, Solaris, Redhat), and found it made a difference.

That was a number of years ago, back when I used to compile important programs myself (apache, perl, mysql, samba, etc). Now I just use SuSE, and find the binaries fine.

That reminds me, didn't Redhat's gcc 2.96 suck! I remember wasting a lot of time trying to get a system back to a standard 2.95

#

Re:Twice to freshen it?

Posted by: Jeremy Akers on February 11, 2005 12:46 PM
Recompiling your compiler can only make that compiler run faster. It does not make the compiler produce better code. GCC 2.96 will produce the same code whether it's compiled with -01 or -03. It may compile code -faster- when it's been compiled under -03, but the binaries it produces will not be any faster.

#

Re:NetBSD performance

Posted by: Anonymous Coward on February 10, 2005 05:19 AM
Maybe he used the stock NetBSD smp kernel.

#

freebsd 5.3 could probably do better

Posted by: Anonymous Coward on February 10, 2005 05:22 AM
freebsd 5.3 has lots of fixes (several of them for performance improvement notably in threading) which are not in 5.3, it'd have been interesting to see 5.3 + the cvs branch which has the patches which will go in 5.4 (it's called stable-current?)

I don't expect it to do much better, but improvements for sure. Just wanted to point it out<nobr> <wbr></nobr>;)

#

Re:freebsd 5.3 could probably do better

Posted by: Anonymous Coward on February 11, 2005 04:49 PM
I think it would be more intersting to see a non-GENERIC based kernel... GENERIC statically links a lot of cruft into the kernel...

It would have been fascinating to see if polling would help fix the interrupt storming of the remote tests too... could make FreeBSD+polling the only sane remote MySQL server for ridiculously unrealistic non-real life SQL serving needs.

#

Why did you both with openbsd and freebsd libc_r?

Posted by: Anonymous Coward on February 10, 2005 05:28 AM
They don't scale well huh? You realize anyone could have told you that, right? They both use userland only threading, and therefore mysql is only ever running on a single CPU, no matter how many are in the system. And no, there is no reason to use a non-MP kernel because of this, what kind of retarded conclusion is that? Different processes will run on different CPUs just fine, postgresql for instance would see an increase in perfomance on these platforms.

#

Re:Why did you both with openbsd and freebsd libc_

Posted by: Anonymous Coward on February 10, 2005 05:58 AM
Yes, it worth to be mentioned in the comments inside the article ! The gain of SMP for OpenBSD and FreeBSD libc_r is effective for _forking_ (or multiples) applications, not multithreaded ones.

And the fact that, for OpenBSD the SMP support is actually very young (younger than NetBSD one: it appears with the 3.6 release, in november) and said by the authors as low-tuned (not for perfs) until they get sure of rock solid stability.

#

Re:Why did you both with openbsd and freebsd libc_

Posted by: Anonymous Coward on February 11, 2005 06:50 AM
Of course OpenBSD's SMP implementation is newer than NetBSD's - they had to wait for NetBSD to finish it before taking it. Was mentioned in some interview @ O'Reilly.

#

cflags setting?

Posted by: Anonymous Coward on February 10, 2005 05:48 AM
While no benchmark is completely fair, I believe there is one fatal flaw in this one.

Gentoo would of been compiled with optimized CFLAGS, with a minimum of i686, and possibly better. The BSDs by default come with i386 kernels and userlands. I believe all of the BSDs would show improved performance if the userland and kernels were recompiled to use the same clfags as Gentoo.

Another interesting comparison would be to add Debian, using only i386 binaries. I believe that would result in similiar results to the BSDs.

#

Re:cflags setting?

Posted by: Anonymous Coward on February 10, 2005 06:03 AM
See above posts. To quickly update you, Gentoo uses quite a bit of c compiler paranoia that can impact performance and most likely was installed without C compiler changes. Otherwise, it seems only fair that they would be noted.

#

Re:cflags setting?

Posted by: Anonymous Coward on February 10, 2005 02:32 PM
hahaha. you gentoo users are so precious.

#

Re:cflags setting?

Posted by: theBlackDragon on February 11, 2005 03:29 AM
It would be nice if you didn't generalize, otherwise I'd be allowed to say that Debian users are impolite unhelpful rtfm-yelling bastards, which a few of them probably are, but certainly not all of them.

I wish they'd educate new Gentoo users more about the impact of clfags, but well... I don't even bother adjusting the cflags anymore, I just set the arch to my cpu and I'm done with it.

On a further note: Gentoo's default cflags are the same ones useb by most distros, so if he used the default cflags there shouldn't be much of a performance difference with other distributions.

#

Take these "benchmarks" with a grain of salt...

Posted by: Anonymous Coward on February 10, 2005 02:37 PM
They were sponsored by O$TG, so the systems were probably rigged in a way that would allow the Linux systems to outperform all the others (which otherwise clearly wouldn't be the case.)

#

Re:Take these "benchmarks" with a grain of salt...

Posted by: Anonymous Coward on February 10, 2005 03:28 PM
>They were sponsored by O$TG, so the systems were
>probably rigged in a way that would allow the Linux
>systems to outperform all the others (which
>otherwise clearly wouldn't be the case.)

How is that clear to you? Please share your results. The methodology of these benchmarks was completely open, and anybody can test and refute them.

If you say Linux clearly couldn't outperform the other systems, you obviously must have some benchmarks that agree with your claim... because it isn't at all clear to me.

#

Re:Take these "benchmarks" with a grain of salt...

Posted by: theBlackDragon on February 11, 2005 03:31 AM
My guess is that he's being sarcastic about all the fuss that's been made about the benchmarks...

And honestly, I can't blame him<nobr> <wbr></nobr>;)

#

NetBSD vs 10M Rows test

Posted by: n-soda on February 10, 2005 05:55 PM
The poor result of 10M Row test on NetBSD may be because the
default of the VM tuning parameters on NetBSD 2.0 is not
suitable for this benchmark. (The default is relatively suitable
for a web server.)
How about changing the parameters to e.g. the following values?

$ sysctl -w vm.execmin=1 vm.execmax=3 vm.filemin=0 vm.filemax=1

(You can set these values in<nobr> <wbr></nobr>/etc/sysctl.conf, if you want set

    these by default)
Although the above values may not be suitable for the database
program either... These values are what I'm using on a squid
proxy server which caches files in its process image.
I'd like to see outputs of "vmstat 5" while the benchmark is running...

#

Re:NetBSD vs 10M Rows test

Posted by: n-soda on February 10, 2005 07:49 PM
> $ sysctl -w vm.execmin=1 vm.execmax=3 vm.filemin=0 vm.filemax=1


I forgot to mention one thing.
The above vm.filemax=1 setting doesn't mean that only 1% of
memory can be used as file cache, but it means file cache
has least priority to stay in the memory, when available
free memory becomes not enough.
Thus, you can use all memory for file cache, as far as there
is free memory, even with the above setting.


If you'd like to see how these values could be tuned. Please
read the following article by Arto Selonen:

<A HREF="http://www.selonen.org/arto/netbsd/vm_tune.html" title="selonen.org">Tuning NetBSD VM behaviour</a selonen.org>

#

Re:NetBSD vs 10M Rows test

Posted by: Anonymous Coward on February 10, 2005 08:45 PM
In this way, you would give executable and cached file data low priority. What remains are anonymous data (the data dynamically allocated by the program) which would get up to the 96% remaining, if necessary. Correct?

But, this couldn't help. Because if those anonymous data wouldn't fit in memory currently, they would be swapped out. But in the article there is clearly stated that NetBSD didn't swap during the problematic test. So there had to be enough room for anonymous data already, and reserving more memory for them should not help. The problem must lie elsewhere I suspect.

#

Re:NetBSD vs 10M Rows test

Posted by: n-soda on February 10, 2005 10:19 PM
> In this way, you would give executable and cached file data low
> priority.

Yes.

> What remains are anonymous data (the data dynamically allocated
> by the program) which would get up to the 96% remaining, if
> necessary. Correct?

Well, since vm.anonmax=80, only 80% of the memory in page queues
are guaranteed for the anonymous memory with my suggestion.
The page deamon can decide how the rest of the memory queues
(100-80-3=17%) will be used.

> But in the article there is clearly stated that NetBSD didn't
> swap during the problematic test.

Hmm. Which part of the article says so?

Perhaps I'm missing something, but if the anonymous memory of the
database benchmark takes 80% of the memory in the page queues,
it's possible that anonymous pages will be paged out.
Since 80% of page queues is probably less than 328MB (= 512MB
* 80% * 80%), and since innodb_buffer_pool_size=256M,
innodb_log_file_size=128M, and innodb_log_buffer_size=8M,
such situation may indeed happen (256+128+8 = 392MB > 328MB)
with the default setting of NetBSD-2.0.

Thus, reducing vm.filemax and vm.filemin does really make sense,
doesn't it?

#

Re:NetBSD vs 10M Rows test

Posted by: n-soda on February 10, 2005 10:49 PM
> (100-80-3=17%) will be used.

Well, I should write this as "(100-80-3-1=16%)", of course<nobr> <wbr></nobr>;-)

#

Re:NetBSD vs 10M Rows test

Posted by: n-soda on February 10, 2005 11:53 PM
> Since 80% of page queues is probably less than 328MB (= 512MB
> * 80% * 80%), and since innodb_buffer_pool_size=256M,
> innodb_log_file_size=128M, and innodb_log_buffer_size=8M,
> such situation may indeed happen (256+128+8 = 392MB > 328MB)
> with the default setting of NetBSD-2.0.

Hmm, perhaps innodb_log_file_size is not buffer size, but
just file size (I don't have clues about MySQL).
In that case, the database benchmark many only take 256+8=264MB,
but it's still possbile that the anonymous memory is paged out,
because the size of 80% of the page queues may be less than 328MB.

My estimation of the size of the page queues (512MB * 80%) is
really wild guess, and I actually saw some cases that the size
is less than 80% of physical memory on some 2.0 systems.
The NetBSD kernel is using more memory on such systems.

#

Re:NetBSD vs 10M Rows test

Posted by: Anonymous Coward on February 11, 2005 01:06 AM
> Hmm. Which part of the article says so?

"Among the operating systems tested, this caching scenario seems to be unique to Solaris 10. None of the other operating systems, including NetBSD 2.0 (which also had a bad showing), saw any swap activity during the 10M row tests."

> Perhaps I'm missing something, but if the anonymous memory of the
> database benchmark takes 80% of the memory in the page
> queues, it's possible that anonymous pages will be paged out.

But they are not paged out, see above.

#

Re:NetBSD vs 10M Rows test

Posted by: n-soda on February 11, 2005 01:38 AM
Ah, it's written in the Solaris issue section, I skipped
that section. Thanks for pointing out.

I'm not sure what "swap activity" means here.
If it only means activities against swap partition,
perhaps there was memory shortage in executable pages
(i.e. there were page-in activities against text section).
If so, changing vm.file{min,max}={0,1}, but not touching
exec{min,max} may make sense.

Anyway, it's most likely an I/O issue for this horrible
performance decrease. This is why I doubt page-outs at
first. Do you have any other idea as the cause of this?

#

Re:NetBSD vs 10M Rows test

Posted by: Anonymous Coward on February 11, 2005 05:14 AM
I don't think that memory shortage in text pages could be a problem - can the pagedaemon decide to evict text pages while keeping anonymous pages? This seems unlikely. Also, by default, vm.execmin is 5 - on a large memory machine, this should be enough unless mysqld has a pathologically large text section.

The I/O issue might be due to slow rewrite performance of UBC. IIRC, since UBC was introduced, rewritten blocks has to be read from disk first. Of course, this is suboptimal - if you rewrite in sufficiently large blocks, no reading should be necessary.

#

Re:NetBSD vs 10M Rows test

Posted by: Anonymous Coward on February 11, 2005 07:03 PM
> $ sysctl -w vm.execmin=1 vm.execmax=3 vm.filemin=0 vm.filemax=1

I should think that low file values and high anon values are a very bad solution, because I'd imagine that the database is not trying to hold the database in (anonymous) memory, but all the data is backed by files. Therefore increasing file values, and decreasing anon values should result in the file backed database data staying in memory better.

Increasing bufcache may also be necessary (or then not; it may be that it only affects meta data?)

#

Re:NetBSD vs 10M Rows test

Posted by: n-soda on February 12, 2005 06:05 AM
> I should think that low file values and high anon values are a very
> bad solution, because I'd imagine that the database is not trying to
> hold the database in (anonymous) memory, but all the data is backed by
> files.

IIUC, most database applications caches its data by itself
in its process address space (i.e. anonymous memory), so,
caching the data in kernel isn't necessary, because that
means caching same data twice. That's why direct I/O is
prefered for database applications.

> Increasing bufcache may also be necessary (or then not; it may be that
> it only affects meta data?)

It's not. bufcache only holds metadata on any OSes which support
UBC. (i.e. all OSes except OpenBSD)
BTW, increasing bufcache is pretty easy on NetBSD. What you need
is to increase sysctl vm.bufcache.

#

remeber, linux mounting filesystems async

Posted by: Anonymous Coward on February 10, 2005 07:20 PM
I guess all you know linux is mounting filesystems async by default.
so to do a fair test, mount freebsd database filesystem with async,nosoftupdates...
obviously i would never run a database on a filesystem mouted async.
chers,
Dennis

#

Only with ext2 filesystems

Posted by: Anonymous Coward on February 11, 2005 05:44 AM
The only widely-used Linux filesystem that is async is ext2. If you'd bothered to read the previous article you'd see that the Linux systems were using reiserfs v3, which by default journals metadata and orders disk writes such that the metadata such as pointers to disk blocks is only updated once the data has been successfully written to a new area of the disk.

This provides data integrity guarantees that are similar to what softupdates can offer - indeed, ordered writes are more or less the same as softupdates, but are better because no metadata corruption can occur if there is powerloss during a metadata update. This is because the metadata is written to the journal first and can be replayed to complete the transaction if the journal copy of the metadata is good or rolled back once the power comes back up if the journalled metadata is not complete, something softupdates does not offer.

However this does assume that the Linux systems were mounted using the default mount options for reiserfs, something which is not made clear.

#

Wrong. Linux ReiserFS is journaled

Posted by: Anonymous Coward on February 11, 2005 05:50 AM
Benchmark was with Linux Reiser journaled file system.
Async is irrelevant. Sorry, but you should read
the article more carefully before putting your
foot into your mouth.

#

Bit of an unfair advantage using ReiserFS...

Posted by: Anonymous Coward on February 11, 2005 04:17 PM
Re the reiser/ext3/ext2 thing:


I would say that using Reiser on Linux would have made a significant overhead difference - you would most likely find that ext2/3 would be quite a bit slower. Indeed, running Linux with Reiser filesystems could even be considered as giving them an unfair advantage. Why? ReiserFS ROCKS! It uses radically different thinking in the filesystem picture, and squeezes otherwise unbelievable performance out of many read/write operations. That's why (check out the benchmarks available at namesys.com)


I did a simple benchmark test with v3.6 some time ago, with full journalling turned on (kernel patch to allow that). The box was admittedly slow, and I used a simple script which looped to touch 100,000 files in a previously blank directory. With ext3 with full journalling turned on it took 3 days to complete. The same operation using Reiser took about 3 hours. It benchmarks consistently on par or faster than ext2 and ext3, depending on the nature of the operation, and in some cases a full order of magnitude better.


I didn't try to kill the ext3 filesystem, but suspect it would have been rock solid after sudden power loss. However, without full journalling, I would normally had to resort to recovery techniques to restore an Oracle database that was running on it at the time from its inconsistent state. Running with Reiser, well... I treated it very very badly. Start transaction... poweroff. And again, and again and again. I got into the habit of treating it like an old fashioned DOS machine - poweroff without shutdown when finished. The closest I could get to corrupting any data was having the reiser filesystem module tell me during boot that an occasional disk transaction here or there wasn't completed, which it rolled back for me. I could not corrupt any Oracle data on this crappy old machine without damaging the media.


I like the way the author answered his own question, methodical and not trying to let the audience make assumptions. However, dare I say it, I believe the if the author gets a chance the tests should be done just a couple more times on Linux using ext3 with metadata journalling. It's not a perfect system, but it's a more standard representative of a Linux system and I'd love to see the results. (IIRC, RedHat currently have no plans to support Reiser in their enterprise products).

#

Re:Bit of an unfair advantage using ReiserFS...

Posted by: Anonymous Coward on February 11, 2005 11:22 PM
First of all your test compared true data journalling (journal writes occur for every piece of data) from ext3 with the metadata-only journalling of Reiser. There's a huge performance penalty for data journalling, and if you didn't need it you shouldn't have turned it on (it's not on by default in ext3). If you did need it, your test is bogus, Reiser falls before the race starts.

To get the same performance as ReiserFS for huge directories (100 000 files is definitely huge) turn on the ext3 option "dir_index" when creating the filesystem. This stores a hash to locate files by name, instead of a linear list.

You also need a new enough (any recent 2.6 for example) kernel or a patched filesystem in order to use this feature to speed things up.

Whether ext3+dir_index would be faster, the same or a little slower than Reiser I don't know, but it would definitely have been more in the ballpark than the test you did.

#

NetBSD smp

Posted by: Anonymous Coward on February 11, 2005 12:53 AM
With NetBSD pthreads, the environment variable PTHREAD_CONCURRENCY determines the number of concurrent threads to be run. The default value is 1.

#

Re:NetBSD smp

Posted by: n-soda on February 11, 2005 01:02 AM
That's true on NetBSD-2.0, but no longer true on NetBSD-current,
because the default was changed to the number of CPUs yesterday.<nobr> <wbr></nobr>:-)

#

Re:NetBSD smp

Posted by: n-soda on February 11, 2005 08:41 AM
> but no longer true on NetBSD-current,
> because the default was changed to the number of CPUs yesterday.<nobr> <wbr></nobr>:-)

But this change is backed out for now,
because one regression test in the NetBSD tree failed with this...

#

Re:NetBSD smp

Posted by: Anonymous Coward on February 11, 2005 03:56 PM
what is the best way to set this on a 2.0 MP machine for all threaded applications to use?

#

Great Report!!!!

Posted by: Anonymous Coward on February 11, 2005 04:36 AM
Thanks for a great performance report.

It would have been interesting to see the Win32 results as well though.

#

Re:Great Report!!!!

Posted by: Anonymous Coward on February 11, 2005 04:12 PM
But who would be waiting for that? I guess most people reading this article think:

"If I would have to chose a UNIX based/cloned operating system, I'd chose..."

I guess nobody would even be thinking about running Win32.

#

FreeBSD is stable

Posted by: Anonymous Coward on February 11, 2005 05:21 AM
You maybe forgetting that FreeBSD is defaulted to stability and not performance. Most system designers and administrators dont usually just toss a default FreeBSD box out to gain performance they toss it out to add stability. FreeBSD needs to be tuned<nobr> <wbr></nobr>... read man tuning...

#

Re:FreeBSD is stable

Posted by: Anonymous Coward on February 11, 2005 07:38 AM
No that's completely false. FreeBSD's "thing" was always "the fastest unix on x86 etc etc" - although granted that was not always the truth, it was kind of their motto.

#

Re:FreeBSD is stable

Posted by: Anonymous Coward on February 11, 2005 01:38 PM
No that's not false.

Everyone I know that runs FreeBSD has tweaked the KERNEL and rebuilt it.

#

Re:FreeBSD is stable

Posted by: Anonymous Coward on February 11, 2005 02:18 PM
It is in fact the fastest UNIX on x86. GNU/Linux isn't UNIX, remember?<nobr> <wbr></nobr>:)

#

Re:FreeBSD is stable

Posted by: Anonymous Coward on February 12, 2005 04:46 AM
FreeBSD isn't UNIX either.

#

Re:FreeBSD is stable

Posted by: Anonymous Coward on February 13, 2005 09:50 AM
And even if it was, we just saw in the test that Solaris on x86 whips it... and Solaris is UNIX.

#

Re:FreeBSD is stable

Posted by: Anonymous Coward on February 12, 2005 01:48 AM
freebsd 5.x is not stable.

jails don't work without compromising security and sharing<nobr> <wbr></nobr>/dev filesystems.

vinum has been broken and replaced with gvinum, which lacks such basic functionality as recovery from loss of a drive on raid5 (read: in effect, no data redundancy at all)

smp still has a long way to go, giant is still there, new scheduler is broken, etc. etc.

of course, this is what freebsd 3.x looked like at this point in its lifecycle as well. hopefully it'll mature with 5.4 and 5.5 and become truly stable. the problem is nobody runs freebsd -current, so the current releases don't get stable quick enough. i'm not sure freebsd's trick of calling 5.3 stable is the best solution here tho...

#

2.6 scheduling query

Posted by: Anonymous Coward on February 11, 2005 03:16 PM
First off, good article.

What I would like to know is what scheduler you used in the 2.6 testing? There are 4 schedulers which can end up giving very different results. And I would expect that Gentoo either uses the default (as) or cfq -- both of which are geared towards better desktop interactivity. I would like to see the 2.6 testing done with the deadline scheduler, which has been optimised for IO and disk throughput. According to kernel documentation:

"Database servers, especially those using "TCQ" disks should investigate performance with the 'deadline' IO scheduler".

Personally, I've only really used the as and cfq schedulers, because my desktop machines don't need the IO prioritising of the deadline scheduler. But I can tell you that as and cfq are quite different -- I would expect deadline to give some interesting results.

I think that the discrepency between the 2.4 and 2.6 kernels would be solved by that -- the 2.4 kernel kinda "played fair", wheras the 2.6 kernel offers the ability to prioritise user interaction over system server performance. I would expect the 2.6 kernel to out-perform the 2.4 kernel, when set up for the task it is going to perform.

#

Re:2.6 scheduling query

Posted by: tokki on February 12, 2005 02:56 AM
I used both the default scheduler and deadline when I was playing with various tweeks. Oddly enough, there was no difference in the results for the 10M row test (the only I/O-bound test). The results posted are with as.

#

Re:2.6 scheduling query

Posted by: Anonymous Coward on February 13, 2005 09:53 AM
The main trouble with AS on database workloads is when you have high end disk systems that support command queueing (or TCQ). AS usually falls short here on database workloads - on a plain IDE disk, it shouldn't be much different.

#

Re:2.6 scheduling query --- 64bit ?

Posted by: Anonymous Coward on February 12, 2005 03:36 AM
did the author do any benching under x86_64 ?
with x86 on its way out, most people believe that AMD64 or ia64 will just only be better, and that is not the case at all.

#

No win32?

Posted by: Anonymous Coward on February 11, 2005 03:24 PM
Would love to see Win32 tacked in there...

#

Re:No win32?

Posted by: Anonymous Coward on February 11, 2005 04:42 PM
trust me, you wouldnt...

#

Re:No win32?

Posted by: Anonymous Coward on February 11, 2005 11:27 PM
Actually, I (for one) would.

#

Re:No win32?

Posted by: Anonymous Coward on February 12, 2005 06:30 AM
I would like to see how pathetic it was.

#

NetBSD io

Posted by: Anonymous Coward on February 11, 2005 06:58 PM
It may be possible to dramatically increase NetBSD's IO performance by tuning your system.

The filesystem type, soft-updates and noatime which you tried should not make any difference, as you also said that they had no effect.

However, there are a bunch of sysctl knobs that can be tuned. Under vm tree there are {anon,exec,file}{min,max} and bufcache. Understanding and tweaking them may have dramatic effects just in this kind of case where you are certain to run out of physical memory.

I don't have any links right here, but the mailing lists contain lots of information about this, and there are also a few web pages detailing those tunables.

#

Other oses, same chances

Posted by: Anonymous Coward on February 11, 2005 10:35 PM
I to would like to see the results with correct smpsettings for netbsd, same compilation flags and stuff for the bsds as linux, and maybe even atleast some simple tweaking. And also final Solaris 10.

Been following this article and the slashdot replys for some time now since the choice of OS, differences and so on intrests me and they are all in my oses-i-might-consider bucket (and also oses-i've-tried).

Would be so much fun seeing Solaris or the BSD own Linux atleast one time<nobr> <wbr></nobr>;)<nobr> <wbr></nobr>// aliquis@link-net.org

#

also dragonfly

Posted by: Anonymous Coward on February 11, 2005 10:53 PM
forgot, sorry guys =P

#

And also OpenDarwin

Posted by: Anonymous Coward on February 12, 2005 05:49 PM
That now runs on x86

#

Re:Other oses, same chances

Posted by: Anonymous Coward on February 12, 2005 05:24 AM
For Solaris 10, I am curious about whether the gcc compiler was used or Sun's latest compiler was used for building MySQL. I have often heard that the Sun compiler routinely produces well-optimized code, though it is slow to compile.

#

Linux 's the best - and that's the truth...

Posted by: dect211 on February 11, 2005 11:00 PM
so please, stop bullshitting about "secure OpenBSD" or "stable FreeBSD" cause every of these OS properly configured can be stable and secure (especially if it works only as a database server). Face the facts - Linux's the best!

#

Re:Linux 's the best - and that's the truth...

Posted by: Anonymous Coward on February 13, 2005 01:41 AM
Since *BSD users can't say that their OS is fast, the have to troll about meaningless stuff like "robustness" or "stability". Moreover MySQL is unstable under load on FreeBSD for InnoDB without Linuxthreads.

BSD have nice things. Like the ports system which is rather handy.

But they are no more secure than Linux. Running the same software under BSD won't magically make a bug disappear. If KDE has a vulnerability, it will affect usually all platforms.
And if you are talking about barriers that can sometimes avoid exploitation of vulnerabilities, Linux is way more advanced than *BSDs with NSALinux or even GRsecurity.

Same thing with stability. Running MySQL under BSD won't make the bugs disappear. They are the same as on Linux.

The kernels themselves have similar stability, although BSD require tuning in order to properly handle an important network load.

If you are running test Linux kernels, sure, you can get unwanted kernel panics. Just like what you can get if you run -current BSDs. If you stick with vendors-supplied kernels, they are very stable.

#

Good job using excel to build your graphs

Posted by: Anonymous Coward on February 12, 2005 12:47 AM
I guess we know which OS performs best for analysis, at least for Newsforge....

#

Re:Good job using excel to build your graphs

Posted by: tokki on February 12, 2005 05:17 AM
Actually, I used OpenOffice.

#

Re:Good job using excel to build your graphs

Posted by: Anonymous Coward on February 12, 2005 06:43 AM
Think before you make yourself look stupid.

#

No DragonflyBSD?

Posted by: Anonymous Coward on February 13, 2005 01:51 AM
It's a shame that FreeBSD was benchmarked but not DragonFlyBSD.

#

OpenBSD alternative disk I/O scheduler

Posted by: Anonymous Coward on February 13, 2005 01:55 AM
Maybe OpenBSD would get better results using this <A HREF="http://www.42-networks.com/obsd_patches/" title="42-networks.com">alternative scheduler</a 42-networks.com>.

#

Win32 and Mac

Posted by: jecuendet on February 14, 2005 01:28 AM
Why not add also a test of the same MySQL server on Windows XPpro and WinSrv2003 on the same hardware?
And also MacOS X and LinuxPPC on the same Mac?
Would be interesting and cool!
Thanks anyway for your test. Worth the read!
-jec

#

Re:Win32 and Mac

Posted by: Anonymous Coward on February 15, 2005 11:19 AM
Um - this is about server performance - not Desktop OS (of which Windows XP is one of and OS-X is largely tuned as)

#

Re:Win32 and Mac

Posted by: Anonymous Coward on February 17, 2005 12:34 AM
Actually OSX has a very fine tuned server OS. And it would be very valuable to stack it in there considering it is a BSD unix. It deserves to be compared in that list much more so than windows. Not that it wouldn't be interesting to see those benchmarks also.

#

Why are all the graphs identical?

Posted by: Anonymous [ip: unknown] on November 28, 2007 07:29 AM
Linux.com, you screwed up. Each and every single graph in this post, even though they have different garbage .gif names, are identical.

#

Where are the 2-cpu and other graphs with sysbench?

Posted by: Anonymous [ip: 216.145.54.158] on December 06, 2007 08:31 PM
All the graphs are for just the Super smak 1 cpu

#

Comparing MySQL performance

Posted by: Anonymous [ip: 121.34.42.176] on December 24, 2007 02:51 AM
What you tested is incorrect reslut,don't you see?

#

This story has been archived. Comments can no longer be posted.



 
Tableless layout Validate XHTML 1.0 Strict Validate CSS Powered by Xaraya