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.
Comparing MySQL performance, Part II
About The Author
Eugenia Loli
Ex-programmer, ex-editor in chief at OSNews.com, now a visual artist/filmmaker.
Follow me on Twitter @EugeniaLoli
A shame they couldn’t test out DragonFly. I wonder how it would have fared.
Reading through the comments, the author didn’t enable PTHREAD_CONCURRENCY for the multiple cpu tests. He’s going to re-run the test for NetBSD with that enabled.
Just a question that is glaringly obvious — the tests were run with an 6+ month old pre-release of Solaris 10. Doesn’t seem quite valid if you ask me. He also doesn’t say what tuning was done.
I would like to see more information run on equal quality software before drawing conclusions.
It’s a good thing he didn’t. DragonFly is not ready, stuff like David Xu’s 1:1 threading library were only added recently and bigger changes are still to come. Even if it had done well on the test, it still would’ve been a unfair because you would not be seeing it’s true potential.
what about windows? Shame it wasnt tested along with the rest.
Personally I think he should have left off Solaris also. Instead of going with the beta that was available at the time.
According to the first article, the author used MySQL 4.0.22 which was released on October 27, 2004.
Probably because that would be illegal.
“Probably because that would be illegal.”
Don’t see how your testing MySQL not windows.
Even so, Solaris did very well and I’m quite surprised by the results. If you’d read stuff on OSNews in the last two years with numerous posters calling Solaris SLOWlaris or some other derivative, I’d expected Solaris to perform poorly.
How would that not be a test of Windows? In this experiment, the application is held constant and the platform is the variable. So, testing this with Windows would certainly be a comparison of Windows with other operating systems.
I wish he’d tested both debian, redhat and slackware (sinces they are main basises of many distros), using their stock 2.6 kernels.
I wouldn’t be surprised if they produced better results than a gentoo box, it ain’t just the kernel that matters.
Tony Bourke gave us at least two reasons to perform this test : the recent flurry of Unix-like OS releases; the overhaul of most of them (FreeBSD 4.x vs 5.x ; NetBSD 1.x vs 2.0 ; Solaris 9 vs 10 ; Linux 2.4 vs 2.6).
MS Windows is not a Unix-like OS. Therefore, it was not necessary to benchmark it. I wonder what would have been the results if Bourke had used his Sun Ultra 5 instead of a x86 box.
Solaris most likely would have taken #1 if MySQL was recompiled with a more decent compiler. According to Sun the Sun Studio 10 compilers give 30% better performance on average compared to gcc. Also keep in mind that out of all OS’s on that list MySQL is probably least optimized for Solaris. Still, impressive results for Solaris, especially scalability.
Changes were made to Solaris 10 to drop the “SLOWlaris” name it was given in the past. Solaris 10 is a major upgrade over previous versions and brings a lot to the table. One major speed upgrade was the re-written TCP/IP stack (among many other things).
So given the goals they put ahead for Solaris 10, it seems they have done a decent job. I was more suprised at the fact Linux scaled on multiple CPU’s as well as Solaris, since Solaris has always been a good SMP OS. Of course this was with only 2 CPU’s, this might change when you have 4 or more CPUs (which several Sun systems I work with do).
I am really surprised by NetBSD. On some single-CPU benchmarks it even beats Linux, yet whenever multiple CPU’s or lots of I/O come into play, NetBSD goes away into a corner and cries. It seems really odd that adding more processors can make an OS run slower. For a P4, it makes sense that enabling hyperthreading sometimes results in worse performance (since both “hyperthreads” use the same cache), but adding a second physical CPU should never result in worse performance unless there is a serious bug in the OS…
Even so, Solaris did very well and I’m quite surprised by the results. If you’d read stuff on OSNews in the last two years with numerous posters calling Solaris SLOWlaris or some other derivative, I’d expected Solaris to perform poorly.
That’s the key thing. In a horse race, Solaris came in second behind Linux, but it was a pretty close race.
Which means for a vast majority of applications, Solaris is “fast enough” when compared with Linux, and that if you like the Solaris feature set, you aren’t really “compromising” performance to get it.
It would be interesting to compare them again using SCSI drives.
“It seems really odd that adding more processors can make an OS run slower. For a P4, it makes sense that enabling hyperthreading sometimes results in worse performance (since both “hyperthreads” use the same cache), but adding a second physical CPU should never result in worse performance unless there is a serious bug in the OS…”
You should go find some info on SMP. A couple things were at play here. First, NetBSD still uses the big-lock method of SMP. It does not scale well and yes it can even reduce performance. However, you are right in a way. The Benchmarker apperently did not turn on support for more then 1 CPU in the netbsd pthread lib. According to the NetBSD lists he is going to re-run the test for NetBSD with support turned on.
Seriously though, you should look up SMP. It is a very interesting subject.
Solaris most likely would have taken #1 if MySQL was recompiled with a more decent compiler. According to Sun the Sun Studio 10 compilers give 30% better performance on average compared to gcc.
So you’re quite sure that the bottleneck was that MySQL was CPU bound in userspace? That IO and the kernel did not play a large part?
Also keep in mind that out of all OS’s on that list MySQL is probably least optimized for Solaris.
That’s a funny thing to say. Maybe we should also keep in mind that you don’t know what you’re talking about?
Still, impressive results for Solaris, especially scalability
Not really. Linux 2.6 is faster and equally as scalable on these tests. In comparision to all the noise that Solaris zealots have been making about Solaris 10, this nothing short of a disaster. Though I’ll grant, it is pretty good for Solaris’s standards – it used to be much slower.
Agreed, especially sense Sun supposedly had benchmarks showing how much it outperformed Linux. I doubt if they are valid.
Also you can use ICC with GNU/Linux and compile MySQL with it, this will also improve performance significantly.
On a related note, it turns out FreeBSD 5.3 with its smpNG is terrible and does not scale well compared to Linux 2.6, or even Linux 2.4. Although FreeBSD 5.3 still has VFS in its big lock and is not using ULE; hopefully when these become stable FreeBSD should improve its standing.
“On a related note, it turns out FreeBSD 5.3 with its smpNG is terrible and does not scale well compared to Linux 2.6, or even Linux 2.4”
Let’s be fair here. I am not a fan of smpNG at the moment, but its not at all done. Saying smpNG is terrible is at best missleading.
I was always under the impression that multiprocessor use is the one case where Solaris shines since Sun spent years optimizing the kernel for it. I know this is is only a small test, but nonetheless I found two things surprising. First that, at least in this specific test, Linux seems to outperform Solaris. I wonder how scalability will be for a larger number of processors, e.g. 4 or 8. Second, how relatively badly FreeBSD 5.x performed compared to Solaris and Linux. I’ve heard a lot of comments from FreeBSD fans during development on how, once 5.x came out, it would put Linux’s multiprocessor scalability to shame. Having seen the level of engineering in the 4.x series made this prediction at least believable, but for the moment (maybe this will change after more tuning) the results seem to be somewhat of a disappointment.
> So you’re quite sure that the bottleneck was that MySQL was CPU bound in userspace? That IO and the kernel did not play a large part?
As with any database application the memory subsystem is likely to be stressed in this benchmark, so any optimizations to memory management would be welcome and are likely yield performance improvement. So yes, superior compiler could improve performance and could make Solaris #1 — the margin between Solaris and Linux in these test is pretty narrow.
> Not really. Linux 2.6 is faster and equally as scalable on these tests.
Linux is marginally faster in these test which most likely could be fixed with better Solaris tuning or simple recompile — I question the quality of tuning done with Solaris, since even adding forcedirectio option (an obvious thing for any DBA) was a revelation to the author. Calling a move from 1 to 2 CPU a scalability test is also laughable, seeing 4 and 8 CPU performance deltas whould be much more interesting.
Most database benchmarks put the database, indexes, and associated files on separate volumes. I think in all cases, not just Solaris the overall performance would be improved by having the database on one volume, binaries on another, and indexes on another. Additionally these volumes would be either mirrored, mirrored and striped, or a RAID 5 array (for redo logs) and spread accross more than one disk controller.
It would also be interesting to know if X was running on the machines during the testing.
Let’s be fair here. I am not a fan of smpNG at the moment, but its not at all done. Saying smpNG is terrible is at best missleading.
Then what do you call an SMP implementation that has been in the works for five years, has a significant overhead on UP machines, and yet is not at all competitive on MP machines compared to it’s biggest rivals?
Then what do you call an SMP implementation that has been in the works for five years, has a significant overhead on UP machines, and yet is not at all competitive on MP machines compared to it’s biggest rivals?
In progress
It takes a very long time to build a good SMP software architecture. We’ve been at this for thirty years, and we’re still at the current stage in Linux, Aix, Solaris, etc. evolutions. Five years is a pretty short time to become remotely competitive with that, really 
To be fair, smpNG has less manpower attached to development than Linux SMP does at the moment, as well. It’s a fairly good effort, though. Not the best, but not unusable.
Also you can use ICC with GNU/Linux and compile MySQL…
ICC is also in FreeBSD’s ports tree, but I have mixed results trying to build some of the larger ports.
>As with any database application the memory subsystem is
>likely to be stressed in this benchmark, so any optimizations
>to memory management would be welcome and are likely yield
>performance improvement. So yes, superior compiler could
>improve performance and could make Solaris #1 — the margin
>between Solaris and Linux in these test is pretty narrow.
No, wrong. Firstly, the memory subsystem will be stressed if the dataset fits in RAM, and is being operated upon by simple queries. In this case though, recompiling the DBMS (if you can call MySQL that) will *not* improve performance.
If the dataset fits in RAM, and is being operated upon by complex queries, it could be CPU bound. In this case, recompiling the DBMS could help (but this is not the type of workload that is being tested).
If the dataset does not fit into RAM, IO bandwidth is likely to be the bottleneck. Recompiling will not help here either.
The first and the third cases were covered in testing, Linux beat Solaris in both. The second case was not.
>Linux is marginally faster in these test which most likely >could be fixed with better Solaris tuning or simple >recompile
Ifs and buts, mate. Perhaps Linux would have fixed it if propolice and stack protection were not used.
> — I question the quality of tuning done with Solaris,
>since even adding forcedirectio option (an obvious thing
>for any DBA) was a revelation to the author.
If you read the article, he got in contact with an employee from Sun to work through tuning. None was needed on Linux. So it is untuned Linux beating a tuned Solaris.
>Calling a move from 1 to 2 CPU a scalability test is also >laughable, seeing 4 and 8 CPU performance deltas whould be >much more interesting.
It is a scalability test. But you’re right, it wasn’t very interesting between Linux and Solaris. Do you have any better results? From what I’ve seen, Linux is just as scalable. For example here http://www.spec.org/jbb2000/results/jbb2000.html, when moving from 8 to 16 cores, Linux gets 90.6% scalability, when moving from 12 to 24 cores, Solaris gets 87.3%.
> In progress
It takes a very long time to build a good SMP software architecture. We’ve been at this for thirty years, and we’re still at the current stage in Linux, Aix, Solaris, etc. evolutions. Five years is a pretty short time to become remotely competitive with that, really 
To be fair, smpNG has less manpower attached to development than Linux SMP does at the moment, as well. It’s a fairly good effort, though. Not the best, but not unusable.
It is not even nearly as scalable as Linux 2.4, which was released 4 years ago.
I wonder if the fact that Linux shows so well in this MySQL benchmarks has anything to do with the fact that MySQL over the years has been extensively optimized for linux in the realm of trying to be the #1 database FOR linux. This is evident just by the fact that MySQL runs much better under FreeBSD w/LinuxThreads than without and that the Solaris 10 results are very close to Linux since Solaris has spent much time on its linux layer which MySQL is most likely taking advantage of even though it is still Solaris platform.
“It is not even nearly as scalable as Linux 2.4, which was released 4 years ago.”
and where pray-tell did you get that? One sql benchmark now decides it all?..my my..the world of computing has gone forward now hasn’t it….
> If the dataset fits in RAM, and is being operated upon by complex queries, it could be CPU bound. In this case, recompiling the DBMS could help (but this is not the type of workload that is being tested).
So what you’re basically saying is that if the entire dataset doesn’t fit in memory there is no room optimizations in operations on memory? Sorry, but your statement is just idiotic.Yes, IO is likely to be a bigger bottleneck when datasets are needed to be loaded from disk periodically, but that is a whole new ball of beeswax — it will be highly dependent on the composition of the benchmark, which is a whole new topic for a discussion. The fact remains, performing queries on the datasets in memory is both CPU and memory intensive and optimizations provided by recompiling can give you and advantage.
> If you read the article, he got in contact with an employee from Sun to work through tuning
Obviously the Sun employee didn’t have a clue how to tune Solaris for MySQL or any database for that matter, if he didn’t mention the forcedirectio option right off the bat, that already raises a big question over the “quality” of suggested tuning.
MySQL has been historically developed as a Linux database, so all optimizations are there for Linux and not for Solaris, which doesn’t make it an exactly level playing field. You have to give Solaris credit for very competitive performance even when performining on the foreign turf. To make this a better comparison MySQL should be recompiled with Solaris native compilers and should be tested on a GA release of OS not beta. This test looks like Solaris was put at a disadvantage to make Linux look more competitive.
> So what you’re basically saying is that if the entire dataset doesn’t fit in memory there is no room optimizations in operations on memory? Sorry, but your statement is just idiotic.
No, what I said was that for the tests performed, recompiling MySQL wouldn’t have had much effect because it isn’t doing much work on the data in the first place (just simple select, update queries).
> Yes, IO is likely to be a bigger bottleneck when datasets are needed to be loaded from disk periodically, but that is a whole new ball of beeswax — it will be highly dependent on the composition of the benchmark, which is a whole new topic for a discussion. The fact remains, performing queries on the datasets in memory is both CPU and memory intensive and optimizations provided by recompiling can give you and advantage.
They can yes. But the memory intensive stuff (eg. copying data around) is usually being performed by the host OS, in which case recompiling the database is unlikely to make much improvement.
I thought it was funny how you dismiss the lead of Linux over Solaris by stating that a recompile will fix it.
> Obviously the Sun employee didn’t have a clue how to tune Solaris for MySQL or any database for that matter, if he didn’t mention the forcedirectio option right off the bat, that already raises a big question over the “quality” of suggested tuning.
Oh right, sure.
> MySQL has been historically developed as a Linux database, so all optimizations are there for Linux and not for Solaris, which doesn’t make it an exactly level playing field.
Oh? And which optimisations would those be?
> You have to give Solaris credit for very competitive performance even when performining on the foreign turf. To make this a better comparison MySQL should be recompiled with Solaris native compilers and should be tested on a GA release of OS not beta. This test looks like Solaris was put at a disadvantage to make Linux look more competitive.
The only thing I give credit is your overactive imagination.
You think that the author contacting Sun about help with performance tuning really gives weight to your claim that Solaris was deliberately put at a disadvantage? While Linux wasn’t even tuned at all? No, you just need to put your tinfoil hat back on.
> and where pray-tell did you get that? One sql benchmark now decides it all?..my my..the world of computing has gone forward now hasn’t it….
From the FreeBSD developers.
http://groups.google.com.au/groups?q=g:thl157617010d&dq=&hl=en&lr=&…
Would you like to show me something that indicates otherwise?
Look at the date.
Why he used an older Solaris version? Because, unfortunately, that was the most recent version available at the time he did the benchmark. If you read part 1 of the article then you can read why he didn’t benchmark Windows.
Solaris most likely would have taken #1 if MySQL was recompiled with a more decent compiler. According to Sun the Sun Studio 10 compilers give 30% better performance on average compared to gcc. Also keep in mind that out of all OS’s on that list MySQL is probably least optimized for Solaris. Still, impressive results for Solaris, especially scalability.
..weird that i don’t see you talking about ICC.
Very nice article, of similar quality as the Scalability test Fefe did a while ago. That is to say: the testing circumstances are documentated, the testing parameters are documented, informative graphs and explanations, and he seems open to suggestions.
Nearly 2 years ago. So I guess you must have test results from sometime after that date showing FreeBSD 5 IS more scalable than Linux 2.4?
ok, so the only 2 good OS’s here are Solaris and Linux, with solaris doing better slightly on more cpus?
I think it’s unfair given the configuration he used for the BSD’s. Usually you have to install additional software on the BSDs to get good threading support for mySQL. And besides, mySQL is fine-tuned for Linux anyway.
So, I guess Solaris 10 can’t be called “slowaris” lol
I don’t see the need to call people offensive terms in what should be a peaceful dicussion.
It does not take a rocket scientist to relize that each package for each OS has certain needs that optimize performance.
“Nearly 2 years ago. So I guess you must have test results from sometime after that date showing FreeBSD 5 IS more scalable than Linux 2.4? ”
Nope..I got nothin, but neither do you. I have yet to see any benchmarks that really address this. FeFe covered scaling in a process sense, but I have never seen anything that does 1-2-4-8 CPUs FreeBSD 5 vs Linux.
I was just pointing out that:
“On a related note, it turns out FreeBSD 5.3 with its smpNG is terrible and does not scale well compared to Linux 2.6, or even Linux 2.4”
is complete bullshit…
>ok, so the only 2 good OS’s here are Solaris and Linux, with solaris doing better slightly on more cpus?
Nope, Linux 2.6 beat Solaris in all tests, 1 CPU or 2. Scalability for each when moving from 1 to 2 CPUs was the same, so Linux is more impressive (because it is easier to scale a low baseline).
> Nope..I got nothin, but neither do you.
Yeah I do. I have this test, I have that developer’s comments, and I have seen nothing to the contrary.
It is you who have nothing.
“Yeah I do. I have this test, I have that developer’s comments, and I have seen nothing to the contrary. ”
….yaaa….a test of mysql scaling on 5.3, an unfinished release in many people’s minds, and a small comment from a FreeBSD dev almost 2 years ago to designate an entire design failed. If that is all you need, then by my guest…but if I could have your name so I make sure to never hire you I would be thankful.
….yaaa….a test of mysql scaling on 5.3, an unfinished release in many people’s minds,
According to the FreeBSD release engineering team, 5.3 was a STABLE release. I didn’t realise you knew better than they.
and a small comment from a FreeBSD dev almost 2 years ago to designate an entire design failed.
I didn’t make the comment about the design failing. I said it is currently less scalable than Linux 2.4, which it is.
If that is all you need, then by my guest…but if I could have your name so I make sure to never hire you I would be thankful.
That won’t be necessary – I don’t work for imbiciles.
Why is it always “if only this or that.. some such os <u>would be</u> better”, or “given time I can do better than.. at whatever..”? I’m just happy my computer boots.
So, Linux dumps on everything, including Solaris, for all-round MySQL performance. That’s basically the gist of the article, although he’s tried to be politically correct about it to stop treading on people’s sensibilities.
Yes, we all know you can use Sun’s commercial compiler, as you can with ICC and Linux, but you do have to try and compare like-with-like as much as possible. But, where it counts and especially at a kernel level of things including I/O handling etc. and handling the small, cheap SMP systems that most people will be using with x86 (4-way and above would have been interesting though) Linux dumps on everyone. It’s there in black and white and in colour, and a lot of the stuff there doesn’t even have much to do with MySQL itself – it’s about your surrounding system. Some people won’t want to admit that though.
I’m also rather astounded that Solaris needed tuning (albeit slightly) just to get half-decent performance out of it – no one else did. However, as we all know that’s a big part of the point of Solaris. Make some excuses for Sun engineers and support people to come in and get paid a lot of money and call it tuning, when it’s actually just pointless.
Yes, I know Solaris is doing far better than it has done on in the past on tests like this, and it is certainly close to Linux, but the crap we’ve got from Sun and the Solaris fanboys about it running Linux into the ground on performance is quite clearly just that – crap.
I think the real test would be COMPILING mysql. It takes tens of minutes.
FreeBSD 5.3 was not a -RELEASE that was made from -STABLE. Calling FreeBSD 5.3 a STABLE release is not accurate as it was not taken from a frozen snapshot of -STABLE as in the normal FreeBSD release engineering process. However, 5.3 is marked as the current production release, so in that sense it claims to be stable, but not released from -STABLE.
You’ll also note that -STABLE was created after 5.3-RELEASE engineering began. Normally, a cvs branch is tagged from -STABLE on a given day, and the release process begins with betas, RCs, and other such cleanup work. 5.3-RELEASE, on the other hand, was basically made from a -CURRENT snapshot that had a lot of testing.
For further information, I refer you to the FreeBSD handbook at http://www.freebsd.org/handbook/ where you’ll find more information on the FreeBSD release process.
Also, it is possible to enable ULE in 5.x -STABLE right now with some work and it seems to be working well on a uniprocessor amd64 box. In fact, my system is noticeably more responsive under ULE than the 4BSD scheduler which causes my desktop to become laggy under load (1gig of RAM on an amd64 machine just shouldn’t be laggy, and it shows under ULE and Linux 2.6).
“According to the FreeBSD release engineering team, 5.3 was a STABLE release. I didn’t realise you knew better than they. ”
I do
….as do all the rest of the FreeBSD users and dev’s who regard 5.3 as unfinished. There are many….
“I didn’t make the comment about the design failing. I said it is currently less scalable than Linux 2.4, which it is. ”
What you said was:
“FreeBSD 5.3 with its smpNG is terrible and does not scale well compared to Linux 2.6, or even Linux 2.4”
So, it is possible that you meant _5.3_ did not scale well, but what you implied was that smpNG did not scale well.
“it is currently less scalable than Linux 2.4, which it is.”
and here is my real problem with you. You have no clue that this is the case. None. You have one sql test…thats it. That other comment you found was almost 2 years ago and it is very arguable that it was meaningless.
btw…I use NetBSD and run a FreeBSD 4.x server. I do not approve of 5.x at the moment, but I have real reasons. You decided what you want to believe and are trying to find something…anything..to support your point. Sadly..you have found nothing so far.
is like saying one building will always be able to house more people than another because they are comparing it to how many people they can house compared to the 1/3 built building can house.
The FreeBSD team has done a remarkable job in fixing FreeBSD. While adding smp to FreeBSD, they have kept FreeBSD usable and production worthy, they have added a lot of useful features, and they have done it all with a very small dedicated team while all these Linux naysayers have done nothing but insult their work.
The hard and painful work for FreeBSD is over now, the infrastructure has been built. FreeBSD will only start performing better and better now. Now it is a matter of converting all the code to start taking advantage of what FreeBSD can do, a lot of code cleaning, removal of Giant, and a lot of real world code optimizations.
And to all the Linux zealots out there, let me teach you a thing or two about being human. There is no need to insult some other people’s work just because you don’t like it. They aren’t hurting you or attacking you. If you don’t like what they are doing, that is perfectly fine. Just ignore it and live in your own universe. If you think what they are doing has good potential, take a look at their work, and either help them, or learn from their mistakes and methodology, and apply it to the Linux kernel. You can always learn something from even the stupidest person in the world. Humble yourself.
What you said was:
“FreeBSD 5.3 with its smpNG is terrible and does not scale well compared to Linux 2.6, or even Linux 2.4”
So, it is possible that you meant _5.3_ did not scale well, but what you implied was that smpNG did not scale well.
Ahh no, I didn’t say that. Adam did.
“it is currently less scalable than Linux 2.4, which it is.”
and here is my real problem with you. You have no clue that this is the case. None. You have one sql test…thats it. That other comment you found was almost 2 years ago and it is very arguable that it was meaningless.
Almost meaningless? You really don’t think very highly of FreeBSD developers, do you?
“Almost meaningless? You really don’t think very highly of FreeBSD developers, do you? ”
What that dev said being:
“All I know is that linux 2.4 is more parallel
than 5.x is today, yet it outperforms 4.x for things like system call
latency and networking packets/sec through a single interface.
Therefore it must be possible.”
Anyone saying that almost 2 years ago is meaningless.
I respect the FreeBSD devs greatly..it is you who don’t.
Also keep in mind that out of all OS’s on that list MySQL is probably least optimized for Solaris.
That’s a funny thing to say. Maybe we should also keep in mind that you don’t know what you’re talking about?
And you do? his statement is as acurate as saying that Oracle is better optimized for Solaris than it is for Linux. Oh and learn some manners mate.
If you read the article, he got in contact with an employee from Sun to work through tuning. None was needed on Linux. So it is untuned Linux beating a tuned Solaris.
Thats an inane statement turning on forcedirectio is hardly what i would consider performance tuning for Solaris.
It appears to me that a more valid test would to be to use Fedora Core or Suse stock Linux kernels rather then Gentoo.