Message-ID: <3A4D404A.F4B4DE6E@acm.org>
Date: Fri, 29 Dec 2000 17:54:18 -0800
From: Tim Kientzle <kientzle@acm.org>
Reply-To: kientzle@acm.org
MIME-Version: 1.0
To: PostgreSQL general mailing list <pgsql-general@postgresql.org>
Subject: Re: MySQL and PostgreSQL speed compare
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

> Search were almost the same ... sorting and
> reading sorted entries from dba was the same.
> But insert/modify/delete [ were up to 15 times
> slower in PostgreSQL than MySQL ].

One of MySQL's big speed problems involves
multi-user situations where some users are writing
while others are reading; MySQL's table-level
locking is a real drawback here, which is
something your testing apparently did not
exercise.  (The new row-level locking code
should alleviate this, but it's _very_ new
and not something I'd want to risk in a production
system today.)

I've had several problems in the past with
MySQL when one client would request a
time-consuming DELETE (e.g., dropping several
million rows containing last month's web log
data).  The DELETE could lock the table (and block
most of the clients) for long periods of
time (anywhere from 10 minutes to 1/2 hour
for one system I implemented).

In practice, of course, web applications are
usually >99% reads, so this issue is often
not a big problem.  Nevertheless, if you're
going to go with MySQL (which is a fine product),
you should give some consideration to avoiding
any time-consuming table modifications.  (In the
system I mentioned above, we moved the log data
out of the database and into rotated log files
on disk, which addressed the problem quite handily.)

I also have some minor concerns about data
integrity with MySQL's Berkeley DB tables, since I
understand that the BDB tables store the row
data within the B-Tree.  This is fundamentally
a less reliable design than other MySQL table
types, which separate the data file from the
index file.  Index files are inherently more
brittle, but easy to reconstruct if they get
damaged.  An almost purely theoretical quibble.

More seriously, I'm also not certain whether
MySQL's table-recovery utilities support BDB
tables at all right now.  This gives me pause.

One big advantage of MySQL over PostgreSQL
right now is ease of upgrading.  Upgrading
MySQL just requires shutting down the old
one and starting the new; PostgreSQL requires
a more involved process to migrate all of
your data.  (Of course, upgrading your DB
is never a simple matter, but at least
MySQL tries to make it less painful.)

			- Tim