Re: fairly current mysql v postgresql comparison need for

Bruce Momjian wrote:
> Our MVCC allows it because there are multiple copies of the row --- pre-dump
> and post-dump.

Exactly. However, according to the mySQL docs I see the
mysqldump option:

"--single-transaction

This option issues a BEGIN SQL command before dumping data from
server. It is mostly useful with InnoDB tables and
READ_COMMITTED transaction isolation level, as in this mode it
will dump the consistent state of the database at the time then
BEGIN was issued without blocking any applications. When using
this option you should keep in mind that only transactional
tables will be dumped in a consistent state, e.g., any MyISAM or
HEAP tables dumped while using this option may still change
state. The --single-transaction option was added in version 4.0.2."

They also have mysqlhotcopy which:

"mysqlhotcopy is a Perl script that uses LOCK TABLES, FLUSH
TABLES and cp or scp to quickly make a backup of a database.
It's the fastest way to make a backup of the database or single
tables, but it can only be run on the same machine where the
database directories are."

Again, without MVCC, I fail to see how InnoDB or BDB can perform
a hot-backup without blocking UPDATES/DELETES until the dump is
complete. Of course mysqlhotcopy just locks the whole database
until the backup is complete.

scott(dot)marlowe(at)ihs(dot)com also wrote:

> As long as there aren't any transactions pending while
> you run it. It does have issues then. The
> hotbackuptool that MySQL comes with is explicitly
> documented by the mysql folks as NOT working for
> innodb tables.

Apparently --single-transaction was added in 4.0.2. It still
doesn't resolve the underlying concurrency issues associated
with a non-MVCC database, however.

medi(dot)montaseri(at)intransa(dot)com also wrote:

> Not to diverge from the main issue, but was this a
> referene to a conversation between Bill Gate and
> Steve Jobs in "Silicon Valley Pirates" movie?

Not intentionally ;-)

Mike Mascari
mascarm(at)mascari(dot)com