I had no idea it was that bad!

[pphaneuf]

I was checking out a review of new Xeon hardware, which was using MySQL 4, MySQL 5 and PostgreSQL 8.2 beta. The test is mostly realistic, consisting of PHP pages doing queries, taken from their own website, with an exception, that the dataset is restrained to something like 2 gigabytes, so that it doesn't hit the disks too much (this can make it either unrealistic, or is a strategy to stay sane).

The article mostly talks about the hardware, but the results put MySQL in, uh, a "bad light". MySQL 4.1.20, going from a one single-core processor to two dual-core processors (that's between twice to four times the horsepower), goes 56% faster. MySQL 5.0.20a goes 40% faster. PostgreSQL 8.2-dev (okay, dev, whatever, have you looked at MySQL recently?) puts in a 224% increase in performance, meaning that it actually gets the "slightly more than three times as fast" you'd expect from having "between two and four times the horsepower".

They also show graphs of how they behave dealing with concurrent requests, where you see MySQL peak at about 520 requests per second, then goes down as the number of concurrent requests. PostgreSQL, on the other hand, slowly goes up to about 640 requests per second, then pretty much stays there, being clearly limited by the hardware to that level, a textbook example of nice scaling. At a concurrency of 100, PostgreSQL pulls in nearly twice as many requests per second than MySQL.

So much for MySQL being "the fast one" (PostgreSQL was traditionally the "safe and correct one", which was already nothing to sneeze at, as long as it managed to be "fast enough").

Comments

[phrawzty.livejournal.com]

Those are some interesting results, to say the least. It'll take some other independent tests to verify these results of course, but perhaps this could be a turning point for the open source DB world?

[pphaneuf.livejournal.com]

Meh, I doubt it, somehow. I've been complaining for a long time at MySQL for being a stupid piece of crap as far as database went (unsafe, but oh so fast! exactly what I want to store my precious data), I don't expect people to become sensible all of a sudden.

Especially as that involves the PHP people becoming sensible. No chance, right there.

[sfllaw.livejournal.com]

No, it will take a different culture to move away from the unholy MySQL-PHP pair. Logic doesn't factor into these things.

[cpirate.livejournal.com]

That spells pure doom for MySQL in the long run. Computers have just about stopped getting faster, now they're just getting more parallel (c.f. Sun's nutty Niagara machine). If MySQL can't hang in there, the benchmarks will start to look pretty dismal, even worse than this.

[pphaneuf.livejournal.com]

MySQL has been losing data for years, and PHP has been messing with people's heads for just about as long. See what sfllaw said to phrawzty, above.

[cpirate.livejournal.com]

Everyone thinks the unsafeness won't happen to them, or won't matter, or whatever, but buying a faster machine costs more money. But if MySQL is unsafe and *slow*, then they might actually be in trouble. Being simple won't save them then.

[pphaneuf.livejournal.com]

MySQL is like an STI. That happen just to others, and doing it safely breaks the mood.

I know what I'm going to do, though!

[pphaneuf.livejournal.com]

Also, a reminder that MySQL elected to use threads, and that as predicted, it would seem they're finally paying for shared VM as a concurrency model, synchronization between CPUs increasing exponentially with the number of CPUs. PostgreSQL uses parallelism through plain old Unix processes, and explicit shared memory mappings where needed.

This is also one of the reason the PostgreSQL people have struggled so much to get things working on Windows...

Note, also, that of the two, they're the one with a non-blocking client API (like with Xlib, you get the file descriptor and you have to call it back when it's readable). That just plains reeks of smartness and sanity, right there.

[pphaneuf.livejournal.com]

What do you know, I mention the "-dev" of PostgreSQL as possibly being fishy, and the very next day, they took it off.

[cpirate.livejournal.com]

You're an eerily predictive man.