Open Source Database Routs Competition in New Benchmark Tests

Kaare Rasmussen <kar@webline.dk> wrote:

I think a bit of explanation is required for this story:

http://www.newsalert.com/bin/story?StoryId=CozDUWbKbytiXnZy&amp;FQ=Linux&amp;Nav=na-search-&amp;StoryTitle=Linux

Up until now, the MySQL people have been boasting performance as the
product's great advantage. Now this contradicts thi sfor the first time. I
believe it has to do with the test. Perhaps MySQL is faster when you just
do one simple SELECT * FROM table, and that it has never really been
tested in a real-life (or as close as possible) environment?

I wouldn't say that this is exactly the first time we've heard
about problems with MySQL's famed "speed". Take the Tim Perdue
article that came out a while back:

http://www.phpbuilder.com/columns/tim20000705.php3?page=1

The most interesting thing about my test results was to
see how much of a load Postgres could withstand before
giving any errors. In fact, Postgres seemed to scale 3
times higher than MySQL before giving any errors at
all. MySQL begins collapsing at about 40-50 concurrent
connections, whereas Postgres handily scaled to 120
before balking. My guess is, that Postgres could have
gone far past 120 connections with enough memory and CPU.

On the surface, this can appear to be a huge win for
Postgres, but if you look at the results in more detail,
you'll see that Postgres took up to 2-3 times longer to
generate each page, so it needs to scale 2-3 times higher
just to break even with MySQL. So in terms of max numbers
of pages generated concurrently without giving errors,
it's pretty much a dead heat between the two
databases. In terms of generating one page at a time,
MySQL does it up to 2-3 times faster.

As written, this not exactly slanted toward postgresql, but
you could easily rephrase this as "MySQL is fast, but not
under heavy load. When heavily loaded, it degrades much
faster than Postgresql, and they're both roughly the same
speed, despite the fact that postgresql is doing more
(transaction processing, etc.)."

This story has made slashdot:

http://slashdot.org/article.pl?sid=00/08/14/2128237&amp;amp;mode=nested

Some of the comments are interesting. One MySQL defender
claims that the bottle neck in the benchmarks Great Bridge
used is the ODBC drivers. It's possible that all the test
really shows is that MySQL has a poor ODBC driver.

I wouldn't say that this is exactly the first time we've heard
about problems with MySQL's famed "speed". Take the Tim Perdue
article that came out a while back:

http://www.phpbuilder.com/columns/tim20000705.php3?page=1

Yes, but the conclusion at that time was that PostgreSQL in general was
slower, but scaled better. So on a heavily loaded site, they would
perform equally well, because PostgreSQL could handle 5 times the load
of MySQL, but MySQL was 5 times faster than PostgreSQL (ot something
like that).

This is the first benchmark saying that PostgreSQL is actually faster
than MySQL. And as we all know, benchmarks can be stretched any way you
like it, so that's why I'd like some comments before I go out and
advocate too strongly :-)

At 09:26 AM 8/15/00 +0200, Kaare Rasmussen wrote:

I think a bit of explanation is required for this story:

http://www.newsalert.com/bin/story?StoryId=CozDUWbKbytiXnZy&amp;FQ=Linux&amp;Nav=na

-search-&StoryTitle=Linux

Up until now, the MySQL people have been boasting performance as the
product's great advantage. Now this contradicts thi sfor the first time. I
believe it has to do with the test. Perhaps MySQL is faster when you just
do one simple SELECT * FROM table, and that it has never really been
tested in a real-life (or as close as possible) environment?

It's no secret that MySQL falls apart under load when there are inserts
and updates in the mix. They do table-level locking. If you read
various threads about "hints and tricks" in MySQL-land concerning
performance in high-concurrency (i.e. web site) situations, there are
all sorts of suggestions about periodically caching copies of tables for
reading so readers don't get blocked, etc.

The sickness lies in the fact that the folks writing these complex workarounds
are still convinced that MySQL is the fastest, most efficient DB tool
available,
that the lack of transactions is making their system faster, and that the
concurrency problems they see are no worse than are seen with "real" a RDBMS
like Oracle or Postgres.

The level of ignorance in the MySQL world is just stunning at times, mostly
due to a lot of openly dishonest (IMO) claims and advocacy by the authors
of MySQL, in their documentation, for instance. A significant percentage
of MySQL users seem to take these statements as gospel and are offended when
you suggest, for instance, that table-level locking isn't such a hot idea
for a DB used to drive a popular website.

At least now when they all shout "Slashdot's popular, and they use MySQL"
we can answer, "yeah, but the Slashdot folks are the ones who paid
for the integration of MySQL with the SleepyCat backend, and guess why?"
And the Slashdot folks have been openly talking about rewriting their
code to be more DB agnostic (I refuse to call MySQL an RDBMS) and about
perhaps switching to Oracle in the future. Maybe tests like this and more
user advocacy will convince them to consider Postgres!

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

At 11:12 AM 8/15/00 +0200, Kaare Rasmussen wrote:

This is the first benchmark saying that PostgreSQL is actually faster
than MySQL. And as we all know, benchmarks can be stretched any way you
like it, so that's why I'd like some comments before I go out and
advocate too strongly :-)

Good scaling characteristics are a lot more important than raw speed
for the web environment, at least, where short, quick queries to
personalize content, etc are the rule. If only a couple of folks
are using the site simultaneously, who cares if it takes an
extra 50 milliseconds to return the page? If I've got a hundred
users on my site, though, and the database engine "starts falling
apart around 40-50 users", then I'm in deep doo-doo.

In practice, MySQL users have to implement the atomic updating of
a set of tables "by hand" using special locking tables, etc. All
the cruft surrounding this is not very likely to be more efficient
than the built-in transaction code of a real RDBMS. When people
talk about the raw speed of MySQL they forget that working around
its table locking granularity and lack of transaction semantics
is a pain that costs CPU as well as programmer cycles.

I came back to Postgres after rejecting it for website development
work when I heard that MVCC was replacing the older table-level
locking model. I've never been excited about MySQL for the same
reason (among many others).

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

Some of the comments are interesting. One MySQL defender
claims that the bottle neck in the benchmarks Great Bridge
used is the ODBC drivers. It's possible that all the test
really shows is that MySQL has a poor ODBC driver.

Nope. If it were due to the ODBC driver, then MySQL and PostgreSQL would
not have had comparable performance in the 1-2 user case. The ODBC
driver is a per-client interface so would have no role as the number of
users goes up.

The Postgres core group were as suprised as anyone with the test
results. There was no effort to "cook the books" on the testing: afaik
GB did the testing as part of *their* evaluation of whether PostgreSQL
would be a viable product for their company. I believe that the tests
were all run on the same system, and, especially given the results, they
did go through and verify that the settings for each DB were reasonable.

The AS3AP test is a *read only test*, which should have been MySQL's
bread and butter according to their marketing literature. The shape of
that curve shows that MySQL started wheezing at about 4 users, and
tailed off rapidly after that point. The other guys barely made it out
of the starting gate :)

The thing that was the most fun about this (the PostgreSQL steering
committee got a sneak preview of the results a couple of months ago) was
that we have never made an effort to benchmark Postgres against other
databases, so we had no quantitative measurement on how we were doing.
And we are doing pretty good!

- Thomas

On Tue, 15 Aug 2000, Thomas Lockhart wrote:

The AS3AP test is a *read only test*, which should have been MySQL's
bread and butter according to their marketing literature. The shape of
that curve shows that MySQL started wheezing at about 4 users, and
tailed off rapidly after that point. The other guys barely made it out
of the starting gate :)

Ah, cool, that answers one of my previous questions ... and scary that we
beat "the best database for read only apps" *grin*

On Tue, 15 Aug 2000, Don Baccus wrote:

It's no secret that MySQL falls apart under load when there are
inserts and updates in the mix. They do table-level locking. If you
read various threads about "hints and tricks" in MySQL-land concerning
performance in high-concurrency (i.e. web site) situations, there are
all sorts of suggestions about periodically caching copies of tables
for reading so readers don't get blocked, etc.

Here's one you might like. I am aware of a site (not one I
run, and I shouldn't give its name) which has a share feed
(or several). This means that, every 15 minutes, they have
to get a bunch of rows into a few tables in a real hurry.

MySQL's table level locking causes them such trouble that
they run two instances. No big surprises there, but here's
the fun bit: they both point at the same datafiles.

Their web code accesses a mysqld which was started with
their --readonly and --no-locking flags, so that it never
writes to the datafiles. And the share feed goes through
a separate, writable database.

Every now and then a query fails with an error like "Eek!
The table changed under us." so they modified (or wrapped -
I'm not sure) the DBI driver to retry a couple of times under
such circumstances.

The result: it works. An actually quite well (ie. a lot
better than before). I believe (hope!) that they are using
the breathing space to investigate alternative solutions.

Matthew.

Given all this performance discussion, has anyone seen any numbersregarding the
speed of PostgreSQl vs Oracle?

Thanks.
- Brandon

--
sixdegrees.com
w 212.375.2688
c 917.723.1981

Given all this performance discussion, has anyone seen any numbers regarding
the speed of PostgreSQl vs Oracle?

Um, er...

No, but perhaps you could consider "one of the leading closed source
database products" which was compared in the Great Bridge test to be
similar in speed and features to The Database You Have Named. *hint
hint*

- Thomas

Is "Uracle" called "Proprietary 1" or "Proprietary 2"? I can't remember :-)

And which other RDBMS is proprietary? Could it be M$ql Server....?

Hint, hint
Poul L. Christiansen

merlin wrote:

On Tue, 15 Aug 2000, merlin wrote:

Given all this performance discussion, has anyone seen any
numbersregarding the speed of PostgreSQl vs Oracle?

Oracle and MS SQL Server must have been the two
"leading commercial RDBMSes" mentioned in the
article.

The licencing of both of those expressly forbids
publishing benchmark results (including, we can
probably assume from the wording of the article,
even referring directly to such).

That said, it would be nice to see some actual
numbers and configurations for the postgresql end
of things; What hardware was used? What versions
of what system software? What OS tuning was done?
What parameters were supplied to postgres?

Matthew.

There are a thousand RDBMS products that might fall under that heading, the
reason the names weren't published is that most commercial RDBMS product
prohibit the publishing of benchmarks when you buy it. The guys that did
this benchmark weren't trying to hide who it was just for the sake of hiding
it, they really can't *legally* say.

I know some people that have benchmarked Oracle and PostgreSQL... Oracle
won, that's all I'll say..

People still need to use whatever RDBMS makes their life easier, one could
make an argument for virtually all existing products (commercial or not) on
that product's individual strengths and weaknesses.

-Mitch
----- Original Message -----
From: "Poul L. Christiansen" <plc@faroenet.fo>
To: "merlin" <merlin@sixdegrees.com>
Cc: <pgsql-hackers@postgreSQL.org>
Sent: Tuesday, August 15, 2000 10:17 AM
Subject: Re: [HACKERS] Open Source Database Routs Competition in New
Benchmark Tests

Is "Uracle" called "Proprietary 1" or "Proprietary 2"? I can't remember

:-)

And which other RDBMS is proprietary? Could it be M$ql Server....?

Hint, hint
Poul L. Christiansen

merlin wrote:

Given all this performance discussion, has anyone seen any

numbersregarding the

At 06:17 PM 8/15/00 +0100, Poul L. Christiansen wrote:

Is "Uracle" called "Proprietary 1" or "Proprietary 2"? I can't remember :-)

And which other RDBMS is proprietary? Could it be M$ql Server....?

Informix, possibly, I know they have the restrictive clause regarding
benchmarking in their contract.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

Hi Matthew,

We'll pull together some more background info on the specifics you
mentioned, and put them on the website shortly.

Regards,
Ned

Matthew Kirkwood wrote:

this may be interesting ned.. and others..

http://www.devshed.com/BrainDump/MySQL_Benchmarks/

it will probably just fuel a fire. but at the same
time something can probably be learned.

jeff

On Tue, 15 Aug 2000, Ned Lilly wrote:

Hi Matthew,

We'll pull together some more background info on the specifics you
mentioned, and put them on the website shortly.

Regards,
Ned

Matthew Kirkwood wrote:

That said, it would be nice to see some actual
numbers and configurations for the postgresql end
of things; What hardware was used? What versions
of what system software? What OS tuning was done?
What parameters were supplied to postgres?

Matthew.

Jeff MacDonald,

-----------------------------------------------------
PostgreSQL Inc | Hub.Org Networking Services
jeff@pgsql.com | jeff@hub.org
www.pgsql.com | www.hub.org
1-902-542-0713 | 1-902-542-3657
-----------------------------------------------------
Fascimile : 1 902 542 5386
IRC Nick : bignose

At 11:47 PM 8/15/00 -0300, Jeff MacDonald wrote:

this may be interesting ned.. and others..

http://www.devshed.com/BrainDump/MySQL_Benchmarks/

He's full of shit the first moment he talks about them always trying
to design fair tests.

Sorry ... I would love to see just one example where DEFAULT
table locking is better (as he claims) - in PG I can of course
lock a table if I want.

I was recently asked to check out an Oracle site that was dying
due to system loads escalating > 70.0 (the decimal point, sadly,
is properly placed). Turns out they were doing by-hand pessimistic
table locking because they didn't understand that Oracle wasn't
MySQL, so to speak, and under load (generating a digest) threads
stacked up (not helped by an Oracle client library bug that causes
weird spinlock deadlocks, not discovered by me but earlier by ardDigita).

Pessimistic locking is available in PG and real RDBMS systems like
Oracle. That's not proof that pessimistic locking is the right thing
to do as not only your default locking but your only locking.

Monty's not fair, and I think most people here know it. They lie,
obfuscate, refuse to update comparision charts to new versions, etc
etc etc.

I wouldn't trust him to pack my parachute, that's for sure.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

(re-posted to GENERAL)

All,

Please see http://www.greatbridge.com/news/p_081620001.html for more
background info about how the tests were conducted.

Best,

Ned Lilly
VP Hacker Relations
Great Bridge

Matthew Kirkwood wrote: