Re: First steps with 8.3 and autovacuum launcher

Heikki,

Thanks for your comments, we do need some review on the expected
behaviour.

On Wed, 2007-10-10 at 11:17 +0100, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > My thoughts are that it doesn't need to. Typically we create objects and
> > then fill them. It isn't that frequent that we would load data, then
> > delete or update more than 20% of it, then attempt other DDL.
> 
> One scenario that comes to mind is a table that's used in OLTP fashion
> during day, but it's taken offline for data loading during night. To
> speed up the data loading, indexes are dropped before the load and
> recreated afterwards.

Yes, delaying the index re-creation could cause an effective outage,
even if it is more efficient to let the VACUUM happen then re-add the
indexes.

> Even if there's no dead rows in a table, autovacuum will still kick in
> to freeze it at some point.

Yeh, but that can wait a while.

> > If a COPY fails it will create dead rows, which should be cleared up by
> > an autoVACUUM. If a COPY fails, the user knows to run a VACUUM or a
> > re-TRUNCATE before re-attempting a modified COPY. So there is potential
> > for more than one VACUUM to be attempted in that case.
> 
> I wish the user didn't have to know to do that.

Yeh, but thats an 8.4 feature now.

> > So there could be an argument for TRUNCATE causing a cancellation of a
> > VACUUM, but I don't see the use case for other DDL. Maybe it would be
> > easier to make all conflicting lock requestors cancel VACUUM.
> 
> Any VACUUM, or just autovacuum?

After some thought, you and Michael have persuaded me that there is
cause to do this for VACUUM as well, but just autovacuum, I think. That
also makes the patch simpler, since we don't need to delve inside the av
worker to see what it is doing.

Alvaro: That means we can just skip your patch altogether, or at least
we can discuss them separately now.

> The only danger I can see is that the autovacuum is always killed and
> never gets to finish, leading to degrading performance at first and
> shutdown to prevent xid wraparound at the extreme. Doesn't seem likely
> under normal circumstances, though. 

Yeh agreed. Table locks aren't that common, so I think we are safe for
100s of millions of transactions. The user has log messages to warn of
that, so I think we're good.

> A scenario that comes to mind is
> having very lazy autovacuum settings, so that vacuum of the table takes
> longer than 24h, and a daily cron job to run REINDEX.

A table that big would have a REINDEX run for a very long time too, so I
hope the user would notice before too long.

> The "priority inheritance" scheme I proposed earlier would work well
> with that: instead of killing the autovacuum, set cost delay to zero to
> let it finish out of the way ASAP. It has it's own set of problems,
> though. An innocent-looking DROP INDEX would cause the autovacuum to go
> full steam ahead, hurting performance for others.

Not very nice performance behaviour. 

> > I think it would be helpful if user-initiated VACUUMs waited behind
> > another VACUUM that was already in progress on the table and then
> > returned immediately as successful when the first VACUUM finishes. That
> > would seem better than queuing up behind the first VACUUM and then
> > repeating the process. 
> 
> I don't think that's a good idea. The second VACUUM wouldn't be a no-op,
> it would clean up any dead rows accumulated during the first VACUUM.

That was my first reaction to that thought too!

In practice, whoever submitted the first VACUUM can re-run it. So that
might be a custom program emitting a stream of VACUUMs or autovacuum
doing the same thing. If we need to VACUUM almost continuously then
autovacuum will realise this and re-submit. Sitting in the locking queue
won't make anything more efficient; like waiting for Rolling Stones
tickets at 4am doesn't make them play any better at the gig.

I'm not proposing to do this latter idea for now though.

--  Simon Riggs 2ndQuadrant  http://www.2ndQuadrant.com