Re: fairly current mysql v postgresql comparison need for

Our MVCC allows it because there are multiple copies of the row --- pre-dump
and post-dump.

---------------------------------------------------------------------------

Mike Mascari wrote:
> 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
>
>
>
>
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073