Re: fairly current mysql v postgresql comparison need for

Bruce Momjian wrote:
> Arjen van der Meijden wrote:
>
>>Is that, for postgresql, the pg_dump tool? In that case, mysql has the
>>same. Mysqldump works fine with innodb-tables as well as for myisam
>>tables.
>
> Can the database be active during the backup, read/writes? pg_dump can
> do that, and make a consistent dump.

I don't see how it could be done without locking out writers, or
writers locking out the dump utility, since InnoDB uses row
locks and BDB uses page locks instead of MVCC. Remember when
Sleepycat wanted to convince hackers that the storage manager
should be trashed for Berkley DB? MVCC was going to be pitched
overboard. Clearly the native tables wouldn't support it:

"Table locking is, however, not very good under the following
senario:

A client issues a SELECT that takes a long time to run.
Another client then issues an UPDATE on a used table. This
client will wait until the SELECT is finished.
Another client issues another SELECT statement on the same
table. As UPDATE has higher priority than SELECT, this SELECT
will wait for the UPDATE to finish. It will also wait for the
first SELECT to finish!"

Their spin on this is awful:

"MySQL uses table locking (instead of row locking or column
locking) on all table types, except InnoDB and BDB tables, to
achieve a very high lock speed. For large tables, table locking
is much better than row locking for most applications, but there
are, of course, some pitfalls."

Does the truth matter at all? Ugh.

Mike Mascari
mascarm(at)mascari(dot)com