Re: Database Performance? - Mailing list pgsql-general

On Mon, Feb 18, 2002 at 01:52:34PM +1100, Justin Clift wrote:
> Hi all,
>
> A friend of mine who's into MySQL recently said told me that MySQL
> doesn't *really* support transactions, it just looks like it does.  He
> then went on to say it doesn't really do ROLLBACK, or isolate the
> transactions or something.

No flames, please.  I just thought reading the fine manuals might
give us some info.

<disclaimer>
I haven't used MySQL for anything in a long time.  Your
mileage may vary.  Health Canada/Surgeon General recommends.  &c.
Opinions are my own; my employer very likely disagrees, and in any
case does not approve or disapprove of, or agree or disagree with,
any opinion, explicit or implied, herein.  Passages marked
"----quote----" are extracted from copyright materials published
elsewhere, and are used under fair-use provisions.  Any errors,
omissions, or misrepresentations are the result of my feeble mind and
not attributable to anyone else.  Do not attempt at home.  This is
all my fault.
</disclaimer>

Here's all I could gather from the MySQL man pages:

----quote----
<http://www.mysql.com/doc/B/D/BDB_characteristics.html>

-LOCK TABLES works on BDB tables as with other tables. If you don't
use LOCK TABLE, MySQL will issue an internal multiple-write lock on
the table to ensure that the table will be properly locked if another
thread issues a table lock.

-Internal locking in BDB tables is done on page level.

----end quote----

So, that looks like not-high-load to me, although much better than
talk-SQL-to-the-filesystem, which always seemed like a kludge.  But,

----quote----

<http://www.mysql.com/doc/I/n/InnoDB_overview.html>

 InnoDB provides MySQL with a transaction-safe (ACID compliant) table
handler with commit, rollback, and crash recovery capabilities.
InnoDB does locking on row level and also provides an Oracle-style
consistent non-locking read in SELECTs. These features increase
multiuser concurrency and performance. There is no need for lock
escalation in InnoDB, because row level locks in InnoDB fit in very
small space. InnoDB tables support FOREIGN KEY constraints as the
first table type in MySQL.

 InnoDB has been designed for maximum performance when processing
large data volumes. Its CPU efficiency is probably not matched by any
other disk-based relational database engine.

Technically, InnoDB is a complete database backend placed under
MySQL. InnoDB has its own buffer pool for caching data and indexes in
main memory. InnoDB stores its tables and indexes in a tablespace,
which may consist of several files. This is different from, for
example, MyISAM tables where each table is stored as a separate file.
InnoDB tables can be of any size also on those operating systems
where file size is limited to 2 GB.

----end quote----

The latter just raises the question, of course, of why in the world
you'd want to paste MySQL on top of InnoDB, since the latter seems
pretty cool from the description.  Especially considering this
limitation <http://www.mysql.com/doc/I/n/InnoDB_restrictions.html>:

----quote----

WARNING: Do NOT convert MySQL system tables from MyISAM TO InnoDB
tables! This is not supported; If you do this MySQL will not restart
until you restore the old system tables from a backup or re-generate
them with the mysql_install_db script.

----end quote----

That suggests that MySQL as a system is not transaction-safe.  I
guess that's what Justin's friend may have been talking about.
Anyway, it'd make me nervous.  But the individual databases might be
transaction-safe.  Note that most of the traditional "Nyah-nyah,
Postgres has all these limitations" limitations are imposed by InnoDB
as well (see the url).  Fancy that.

I'd like to see some real benchmarks under load comparing PostgreSQL
and MySQL+InnoDB.  It seems that such would be an apples::apples (ok,
maybe one's an overbred hybrid apple, but still an apple) comparison
at last.  Anyone know how to do as much?

A

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110