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

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

> 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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to