Michael Ludwig schrieb am 11.05.2010 um 00:24:59 (+0200):

> On page 382 of Helen's book, in the section "Preventing Slowdown on
> Batches", I'm reading the following:
>
> Two behaviours can cause huge batches to get progressively
> slower during executino.
>
> * [maintaining indexes - okay]
>
> * The accumulation of *back versions*, the deltas of the
> old rows that are marked for obsolescence once the
> update or delete operations are committed. Back version
> do not become available for garbage collection until the
> transaction completes. In transactions involving back
> version numbering in the tens to hundreds of thousands
> (or more), garbage colllection may never win its battle
> to clean out obsolete version. Under these conditions,
> only a backup and restore will sanitize the database
> completely.
>
> Not sure I'm following. I thought that once the huge batch is done and
> all transactions that are older than the batch completion have also
> completed, the back versions will have become eligible for garbage
> collection. Correct?
>
> If so, how will GC never win the battle?
>
> Why is a backup/restore needed?

I think this simply means that UPDATE and DELETE will leave back
versions behind, and when there is an awful lot of them, that is
likely to slow things down. And when they're all created by one huge
transaction then there is less opportunity for garbage collection
then when there are smaller write operations interleaved with read
operations, which may cause GC to kick in and dispose of the back
versions, keeping the database lean and clean.

--
Michael Ludwig