Re: [GENERAL] New FAQ item

On Sat, Jul 10, 1999 at 10:58:20PM -0400, Bruce Momjian wrote:
>
> Yes, I agree we compare much more easily to the big guys, but that
> information may not be getting out as much as it should, so we have to
> include the obvious MySQL comparison.
>

The general topic of comparing against MySQL and other dbs came up on the
Zope mailing list a couple of days ago, in the context of Zope becoming
multithreaded, and what the db Adaptors needed to support that. I think
it'd be fruitful to share this post from there with this list. This is
from a developer who's familiar with the internals of MySQL, not just
an end user.  One interesting nugget in the middle is a comparision of
query speed vs. Informix. He mentions that MySQL is faster, but only
if the selects are simple.  As the queries become complex, the advantage
gets smaller. Perhaps postgres would be similar, with our (much maligned,
but IMHO, now pretty damn good) optimizer.

Ross


Date: Fri, 9 Jul 1999 12:51:31 -0400 (EDT)
From: Andy Dustman <XXXXXXXX@XXXXXXXXXXX>
Subject: RE: [Zope] Zope, performance and multithreading (beginner questio
 ns)

On Fri, 9 Jul 1999, Rob Page wrote:

> > Anybody know how well MySQL performs with multiple threads? Last I
> > heard, it serializes database calls, which isn't exactly promising.
>
> MySQL doesn't support transactions.  Chris Petrilli here has the details
> but I'm led to believe this is a fatal blow to MySQL's ability to _ever_
> (at least until it does support txns) reliably support threaded usage.

The MySQL solution to doing transactions is to instead do table locking.
At least, this is what the docs say (see section 5.4). By not supporting
transactions, this makes the database 2-3x faster (claimed). An exerpt:

""" The current problem is actually ROLLBACK. Without ROLLBACK, you can do
any kind of COMMIT action with LOCK TABLES. To support ROLLBACK, MySQL
would have to be changed to store all old records that were updated and
revert everything back to the starting point if ROLLBACK was issued. For
simple cases, this isn't that hard to do (the current isamlog could be
used for this purpose), but it would be much more difficult to implement
ROLLBACK for ALTER/DROP/CREATE TABLE.

To avoid using ROLLBACK, you can use the following strategy:

    1.Use LOCK TABLES ... to lock all the tables you want to access.
    2.Test conditions.
    3.Update if everything is okay.
    4.Use UNLOCK TABLES to release your locks.

This is usually a much faster method than using transactions with possible
ROLLBACKs, although not always. The only situation this solution doesn't
handle is when someone kills the threads in the middle of an update. In
this case, all locks will be released but some of the updates may not have
been executed."""

But obviously, if you need transactions, or need to write a
cross-database-platform app, don't use MySQL. I have heard from someone
who has benchmarked MySQL against Informix (on Linux) that MySQL is about
2x faster, if the queries are simple. The more complicated the queries
are, the smaller this difference becomes.

Anyway, back to the original question: In general, MySQL does not
serialize database calls. With the non-standard LOW_PRIORITY keyword on
INSERT/REPLACE/UPDATE statements (REPLACE is like INSERT, except
pre-existing rows matching the primary key are replaced and there is no
error), the write is delayed until no other client is reading from the
table. With the non-standard DELAYED keyword on INSERT/REPLACE statements,
the query returns immediately but the data is not written out until later;
it goes into a delayed queue (with it's own thread). The delayed queue
handler tries to write delayed rows out en masse. This is intended for
things like logging where a lot of records are written on a regular basis,
but you don't want to delay the client.

Internally, the MySQL server uses table locks between server threads. The
client libraries are thread-safe, but there are some subtle caveats about
the connect call.

--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005