Re: First steps with 8.3 and autovacuum launcher

On Wed, 2007-10-10 at 11:04 +0200, Michael Paesold wrote:
> Simon Riggs wrote:
> > OK, I've got this working now. It successfully handles this test case,
> > which trips up on an auto ANALYZE every time I run it.
> ...
> > I notice when we cancel an AV worker it always says "cancelling
> > autovacuum of table", even when its just an ANALYZE. Wasn't important
> > before but now looks a little strange.
> ...
> > Any other input anyone?
>
> What about VACUUM (not just ANALYZE)? The starter of the thread
> "Possible bugreport 8.3 beta1 on Win32: Looking like a deadlock with
> AutoVacuum" complained about vacuum, not analyze.
>
> It is just as Tom said earlier: it will be before end of beta that
> people will complain about more than just restoring dumps. ;-)

I'm not looking at this from the perspective of how to make restores
work better, I'm looking at the common use cases. Re-adding FKs after a
major data load is part of the Performance Tips section.

> So does this approach work for both analyze as well as vacuum?

It doesn't work with VACUUM.

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.

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.

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.

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.

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