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."

Comments

postgres

[fludlight]

what about postgresql?

That's all well and good, but...

[Sheetrock]

MySQL is a different animal from PostgreSQL, which is itself a horse of a different color than DBI. To truly profile these operating systems you must take into account the differences in:

• Tuple calculus
• Transaction journaling
• Operator space/system call overhead
• Disk cache timings

And much more... in essence, you can't be certain these benchmarks hold true for the performance of all databases and it may even be a mute argument -- the same operating system may be tweaked differently if you're fileserving or mailserving or networkserving or if you're only dataserving. A useful tool, but one that must be run on each server.

Re:That's all well and good, but...

[JabberWokky]

Pretty much exactly what he said in the second article. Of course, he presented this specifically as a MySQL benchmark rather than a general OS comparison, so you've just killed a straw man.

--
Evan

Why do people use MySQL over Postgres?

[Vellmont]

Slightly off topic, but if it's really performance you want, why don't people just use Postgres? It's had a much better feature set for years, and is starting to get enterprise level features. It seems like MySQL is somehow the default choice for open source projects, but as far as I can tell it offers no advantages and many disadvantages over postgres.
Is it just MySQL is slightly easier to setup?

Re:Why do people use MySQL over Postgres?

[james_orr]

MySQL got the head start. Postgres has had a better feature set for a long time now, but when I first looked at it you couldn't do left joins and all triggers had to be written in C. At the same time MySQL could ... well .. do what MySQL does now.

So MySQL became the popular open-source database.

Re:Why do people use MySQL over Postgres?

[Unordained]

I was at Barnes & Noble tonight, waiting to go pick someone up at the airport. Looking through the tech-book section, apart from noticing a lack of theory books, I noticed that MySQL has done well for itself in the publishing world. SQL books most often refer to MySQL for examples, PHP books (and other "learn this language so you can incorrectly apply what you halfway learn to any problem you encounter" books) generally include a large section devoted to database stuff, seemingly always involving MySQL ... There were more MySQL books than Oracle books, more Oracle books than MS-SQL books, more of those than of PostgreSQL books, and no Firebird, MaxDB, Ingres, DB2, Cloudscape, HSQL, etc. books that I recall.

If most all programming were taught in Basic, don't you think people would wind up coding apps in Basic, regardless of its actual worthiness? (Today, it's Java, not Basic. But now I've called upon myself the wrath of the Java coders.)

I personally use Firebird at work and at home. Installation is a snap, particularly from binaries. Unpack, run installer, go. Works under *nix and windows, and has an embedded version appropriate for standalone apps. Its creator is the father of generational record-keeping (which some RDBMSs like MS-SQL have only recently gotten around to), and it's like an "easier to use under windows" cousin to PostgreSQL, feature-wise.

Other than good marketing (including a lot of word-of-mouth rumors) in favor of MySQL, I can't explain its success. When you have to help someone implement a query as a correlated sub-query because their RDBMS of choice (MySQL) doesn't do certain joins properly, or doesn't do them quickly (yes, even with indexing -- the optimizer is just stupid), ... you start to wonder why they don't use an RDBMS where they can express exactly what they mean, directly, and have it be fast. And MySQL's popularity has lead to a lot of people learning some very bad habits (particularly about doing a lot of client-side work, like joins or aggregates, or using sub-queries where joins should work) ... but people love it! (I think this is related to the stockholm syndrome, in a way; I also see it with data-entry clerks who are convinced that their crappy, buggy, expensive, old, vendor-supplied software is better than anything anyone will ever build, ever. If you're an in-house programmer, you're not a real programmer, either. Masochists.)

Re:Why do people use MySQL over Postgres?

[SerialHistorian]

I've written a bunch of enterprise-class stuff on MySQL.

The first and second answers are inertia. All of my tools work with MySQL and I'd have to spend a week or two re-writing them for PostgresSQL, and I can't shake loose that kind of time right now.

Also, I have a set of redundant, mirrored MySQL servers in my colo box that run all of the websites I've built, and I'd have to get more rack space or convert everything over to Postgres at the same time. Neither of which are cost effective when what I have ... works.

The third answer is that MySQL is blazingly fast at doing simple things. Where Oracle (The other RDBMS that I'm familiar with) can return simple select queries or complex insert or joined select queries in .5 to 1.0 seconds each, MySQL can return simple queries in .01 seconds and stupidly complex queries in 5-10 seconds. Since 100% of what I'm doing is simple selects or can be hacked very quickly to seem like simple selects, there's no reason to use anything more powerful for what I'm doing.

I don't need to have "good habits" ... I don't need to have nth degree optimized queries. I don't use 99.99% of the features that MySQL has, not to mention all the features that Postgres has that I wouldn't use. (And don't get me started on Oracle.) It's also faster for me, in both database query return and programmer time, to execute 5 simple, general, fast queries that are part of a code library (and when the database structure changes, edit that one code library) than it is for me to write one really complex query for each code module (and have to edit every module when the database structure changes).

What it comes down to is that it works well as a lightweight database for websites and web apps, and there's a ton of community support and literature. It's not Oracle. It never will be. It's not useful for everything. But when you need a lightweight database to handle a ton of simple select queries without melting down, .... MySQL fits the bill. Why swat a fly with a sledgehammer?

Re:Why do people use MySQL over Postgres?

[bani]

because mysql is easier for ISPs to manage. adding new users with very fine grained db control is very simple for ISPs. not so with postgresql -- it can be a real bear for ISPs to manage with 1000's of separate users.

and ISPs are the driving force behind a lot of what is widely deployed.

this is one of the same reasons PHP won out over everything else -- because it integrates easily and because it's easy to manage. not because it's "the best" designed language or the most powerful.

mysql is "good enough", quite frankly the majority of people out there using sql in deployment are using eg phpbb or postnuke or whatnot and would gain little to nothing from using postgresql.

Re:Why do people use MySQL over Postgres?

[higginsm2000]

I've written a bunch of enterprise-class stuff on MySQL.

How do you define enterprise class? I certainly wouldn't define a simple website (however large) as an enterprise class application and that seems to be what you are talking about.

Re:Why do people use MySQL over Postgres?

[chez69]

conidering that slashdot often gives 503 errors, I wouldn't consider it even close to a real high availabily application

slashdot enterprise quality? my shit is more enterprise quality

Re:Why do people use MySQL over Postgres?

[arkanes]

On the other hand, Wikipedia suffers from intermittent timeouts and editing errors. Perhaps the lesson here is not that MySQL is optimal for enterprise scalability, but that it can be leveraged into it, although there may be better options?

Different filesystems?

[dolphinling]

I might just be naive, but doesn't database performance depend a lot more on filesystem than OS?

same reason people use windows

[RelliK]

because everyone else does.

Mac OS X.

[freemacmini]

Too bad he could not test on Mac OS X.

Of course in order to do that he would have to install the OSes on a PPC machine and I don't think freebsd on PPC is ready for prime time yet.

How relevant are those benchmarks

[Donny Smith]

Does anyone go: "OK, I need the OS for my mySQL project. I'll benchmark BSD, Linux, Windows, and choose the fastest OS."

Difference among OS should be pretty much unimportant unless one's an ISP or big enterprise. I would choose the OS based on completely different criteria:
1) Existing skillset (advantage to existing skills)
2) Existing deployed OS (advantage to OS already deployed)
3) My company's OS strategy (advantage to the OS and the CPU platform we chose to standardize on)
4) Existing software (if I already have X vendor's backup agent for mySQL on Linux or database tuning tools, I wouldn't use BSD just to (potentially) gain an extra 5% in some ludicrous benchmark result).

Today's hardware (and operating systems) are so cheap that it's almost irrelevant what OS and hardware goes into many a project.
Look at the new HP's 25p and 35p blades (Opteron-based) - a 2 processor 1GB RAM version is just some $1,700 more expensive than a 1 processor 512MB RAM version.
It's easy to lose that $1,700 in downtime, spend it on a Windows engineer's new RHCE or such...

Re:Useless Benchmarks

[setagllib]

Entirely right, and some user-space optimization could have gotten the final few percent in too. He installed stock BSDs and recompiled their kernels straight, didn't tweak any options that weren't necessary to run the suite, and compared to a Linux optimized from the ground up (Gentoo + his knowledge of Linux itself). Real clever benchmark.

That NetBSD performed worse than FreeBSD for disk IO is really strange. I have never seen this happen in any of the machines I've tried both on (hint: a lot), so either he has a very exotic disk controller which isn't supported properly (weird) or there's a disturbance in the force. Members of the mailing lists are talking it over with him now, and a follow-up should arrive eventually.

I would have liked to see results of FreeBSD 5-STABLE too, because he compared a refined Linux and a solid NetBSD to a FreeBSD release that was deemed not-ready-for-benching-let-alone-production on day 0, which gave it little chance. It's interesting to see if the claims 5.4 will be much better hold water.

Re:Procedural problem with NetBSD multiprocessor

[Anonymous Coward]

it's because NetBSD insist on N:M threading, and there's no sane way to do that (which is why the people behind NPTL gave up trying on Linux and just made threads so lightweight you could afford a few thousand running at once)

So NetBSD is really asking the user for a hint: How many _real_ threads should we use to emulate the threads you've asked for ?

Needless to say providing performance hints could always help a threading implementation, but as we saw here, it also means the administrator must painfully figure out which settings are optimal.

Amazing

[cranos]

Somebody posts a comparison of an application running on different OS's as a system benchmark, and what do people do? Attack MySQL.

God guys get over it, MySQL is here and it has actually proven itself to be usefull. Yes its missing features and has issues, but it fills the niche it is aimed at.

what a lightweight

[justins]

(1) 9 GB SCSI-160 (7200 RPM)

Okay, this guy is not a database person. Now, the choice of database product to benchmark with might have been a dead giveaway, but so is the choice of configuration. If you're not going to test with a real database configuration (which the author of the article has probably never seen) why bother? Funny that he'd test on a machine with two CPUs and one drive, though. Great judgement in hardware selection.

Releasing the results against an old build of Solaris x86 a few days after the general release of Solaris 10 is pretty funny, too.

Compiling his own versions of the software without even looking at the patches the maintainers of the FreeBSD and OpenBSD ports of MySQL use was really good judgemenet. I mean, heck, they might know something about how to make that software perform properly on their platforms, and we wouldn't want that skewing the results! "After all, I'm testing the operating systems, not their pre-packaged MySQL distributions or source builds."

What... a... loser...

Give me ANSI SQL or give me death

[WillAffleckUW]

well, i've been having some interesting ODBC issues passing the same SQL queries to Access and to Oracle, so let's not pretend that the ANSI SQL compatible world is that compatible ...

Gentoo From Scratch???

[RoadWarriorX]

After reading the benchmarks, I noticed that the Linux distribution chosen was Gentoo. I like Gentoo (and use it myself). The author of the newsforge article does not really state if the all of the kernel, libraries and applications were built completely from scratch or did he use a stage 2 or 3 install.

If the box was built from source, I would expect that Linux benchmarks would be higher simply because the kernel, libraries, and applications were most likely tuned to the hardware. Otherwise, I would like to see RedHat, or SUSE, or other "out of the box" distros in addition to the others.

Just my $0.02