Slashdot Mirror
Comparing MySQL Performance
An anonymous reader writes "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.
These two articles will show you how to benchmark operating system performance using MySQL on these operating systems so you can find out for yourself if you're missing out. While this may not necessarily be indicative of overall system performance or overall database application performance, it will tell you specifically how well MySQL performs on your platform."
All kidding aside, it would have been good to see these test also running on XP and OSX.
Valid point. RedHat Linux is tuned for Oracle - including a larger shared memory region. A more valid comparison would be if each OS was first tuned for the database.
Something like shared memory, which is used for sorting, caching, and hashes, would slow a database down quite a bit if there was not enough.
I work with multi-terabyte databases daily, and by my observation, the flavor of UNIX is irrelevant if the IPC resources are adequate. When you're scanning a gig from disk, all DMA, an extra second in the kernel doesn't count in the O().
Nevertheless, this is a very good benchmark conducted in a fair manner. I was pretty much surprised at how the guy lacking support (from Solaris no less) went on to find out by himself how to increase performance. This also underlies the point made by many in the "netbsd vs. free" benchmarK about the focus of FreeBSD being SMP in the past few years ... which has payed off nicely it seems.
What about Mac OS X? I know (actually, not really) that Solaris doesn't run on Apple hardware, but it would be interesting to compare the same stuff on an Xserve and also be able to test the OS X performance.
When you look at the state of the world, how can you not become a radical, liberal anarchist?
No serious DBA uses MySQL. Oracle, Sybase, PeopleSoft(soon to be oracle), IBM...
MySQL is what they use to teach you structured query in school. Databases have come a long way in FEATURES in the last 10 years...
------- "From bored to fanboy in 3.8 asian girls" ----------
Because MySQL is so much easier to say than PostgreSQL... Sounds like a speech impediment.
Free Firefox news reader.
Certainly, XP (also 2K, 2K3) would have been an interesting comparison. OSX, however, is out of the question - all of the others ran on exactly the same hardware, a dual 1GHz PIII system. The point of the test was to see operating differences, without the hardware being a factor.
Of course, it would be possible to run the tests on the same Mac under OSX, Linux and any other OS available for that platform. That might have been interesting, but you wouldn't be able to compare the results directly to those from the PIII system.
-- Steve
Must say that it is very nice to see Solaris up there in the top in the tests that it was featured in. Seems Sun was not joking around when they claimed that Solaris 10 would be greatly improved on x86. As is often said around here; More choice is good.
Recompiling the kernel to remove unnecessary features/drivers and enable support for modern processor features seems very intuitive to me. Removing as many unnecessary features as possible and runing it as closely as possible to the host system is a basic step when configuring a system, the difference is linux lets you take the tuning several steps furthur.
And your second coment depends on the organisation, a lot of strongly-microsoft shops work the way you describe, and only use unix for jobs where windows can't handle the task at hand (even microsoft run a lot of their backend stuff on solaris and as/400 systems for instance). While many other companies use unix for most backend tasks while leaving windows strictly for end user interaction.
http://spamdecoy.net - free throwaway anonymous email - avoid spam!
What about the Firebird relational database?
Good point. "Firebird is a relational database offering many ANSI SQL-92 features" [emphasis added] PostgreSQL "supports SQL92 and SQL99" [emphasis added]. "New code modules added to Firebird are licensed under the Initial Developer's Public License. (IDPL). The original modules released by Inprise are licensed under the InterBase Public License v.1.0. Both licences are modified versions of the Mozilla Public License v.1.1." On the other hand, "PostgreSQL is released under the BSD license." Other than that they are mostly comparable, so you have risen a very good point. If you don't need standard SQL support and the license is acceptable, Firebird is a very good option.
MySQL may be fast, because its features are so limited. Sure, it stores and retrieves records, but its partial implementation of SQL (without subqueries etc) and blatant bugs that violate SQL (try inserting '123456' into a varchar(4) column, it will silently truncate to '1234' instead of giving a 'Inserted value too large for column' error) make it useless for anything reliable.
If 'fast' is your only important concern you should be using flat files or dbm/ndbm files, but there is more to a database than speed.
I have always had a soft sop for Solaris since 2.51, it secure, and stable, in my experience, but alas its always been SLOW on x86 hardware.
:) I wonder if its a result of better x86 optimization or the new Filesystem
These benchmarks show that at least with mySql its pretty fast, but more importantly look at the solaris benchmarks, they are nearly identically consistent across all test, where others vary much.
Ive always kinda thought of Solaris as a 4 wheel drive truck in low range, but it looks like they added a turbo
He was modded offtopic, because this article was about OS benchmarks, not database benchmarks. The title of TFA is "Using MySQL to benchmark OS performance"...so its seeing which OS mySQL runs best on, not which database is best.
One thing I noticed about version of mysql (prior to version 4.1, I believe) was that mysql didn't support the notion of nested queries, which at the time, was what I really needed to perform. An upgrade to mysql 4.1 solved this, but something that what I would consider to be an integral part of sql was just "left out" of previous versions just reinforced my decision to use PostgreSQL instead. (The mysql databaase was a product of a coworker, not using our organizational standards.)
And they said zombies weren't real!
While your post is thorough and accurate,
you glossed over the fact that MySQL is now dual licensed. This DOES have repercussions. The GPL version can only be used by GPL software OR as a special exception. The special exception is made for PHP (and maybe others). If you are a Bank and choose MySQL you have to BUY a license.
I wonder how much there is to the MySQL great for websites (many read, few write) and the PHP license exception.
MySQL 4+ is not the MySQL that we all came to know and love in the 3.x days. Previously, I used MySQL 3.x but when I needed to upgrade, I moved to PostgreSQL because of the new license alone.
Let me re iterate my take. PHP license allows you to make commercial websites with it. MySQL allows its GPL license to be used with PHP regardless of purpose of the PHP scripts by special exception. Had there been no special exception, we'd have seen the downfall of MySQL and the upshoot of PostgreSQL or SQLite.
As a user/admin of all 3, I find that you can either use PostgreSQL or get away with using SQLite. Incedentally, try using SQLite with SQLRelay if you need network access for SQLite.
Slashdot's rate-of-post filter: Preventing you from posting too many great ideas at once.
Learn more about the benchmarks and WHY FreeBSD is slightly slower NOW. This time next year you will likely fully understand FreeBSD's decisions when they are again the benchmark leader on a 'typical' hardware configuration. The future is all about multi-processors and multi-core processors. Guess what? FreeBSD 5.x is ready.
Maybe one should do it. It is sure useful to evaluate a database platform on many hardware confugurations in the same price/capabilities range. Maybe buying a XServe running OSX from Apple makes sense for doing MySQL more than, say, an IBM xSeries or an Itanic offering from HP running HP-UX.
Of course, such a test would involve some serious resources not everyone has available.
http://www.dieblinkenlights.com