[Zope] Zope, performance and multithreading (beginner questio ns)

[Andy Dustman]

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.

I mentioned this (buried in another thread), but I have a MySQLdb module
for Python that is completely thread-safe (i.e. it releases the global
interpreter lock on blocking calls). It's mostly compatible with
MySQLmodule-1.4 (what ZMySQLDA uses) so it should be easily adaptable to
Zope, except for the fact that it does try to use mxDateTime, which
conflicts name-wise with Zope's DateTime. If any changes are required to
make it work with Zope (it should probably have it's own ZMySQLdbDA), let
me know.

http://starship.python.net/crew/adustman/MySQLdb.html

-- 
andy dustman  | programmer/analyst |  comstar communications corporation
telephone: 770.485.6025 / 706.549.7689 | icq: 32922760 | pgp: 0xc72f3f1d