Re: First steps with 8.3 and autovacuum launcher

On Fri, 2007-10-12 at 07:17 +0100, Simon Riggs wrote:
> On Fri, 2007-10-12 at 01:24 -0400, Alvaro Herrera wrote:
> > Michael Paesold escribió:
> > > Simon Riggs wrote:
> >
> > > Hmm, I am not sure we are there, yet. Autovacuum does take extra care to
> > > vacuum tables nearing xid wrap-around, right? It even does so when
> > > autovacuum is disabled in the configuration.
> > >
> > > So in case a vacuum is needed for that very reason, the vacuum should *not*
> > > be canceled, of course. So we don't really need the information, whether
> > > the AV worker is doing VACUUM or ANALYZE, but whether it is critical
> > > against xid wrap-around. Could that be done as easily as in Alvaro's patch
> > > for distinguishing vacuum/analyze? Alvaro?
> >
> > Yes, I think it is easy to mark the "is for xid wraparound" bit in the
> > WorkerInfo struct and have the cancel work only if it's off.
> >
> > However, what I think should happen is that the signal handler for
> > SIGINT in a worker for xid wraparound should not cancel the current
> > vacuum. Instead turn it into a no-op, if possible. That way we also
> > disallow a user from cancelling vacuums for xid wraparound. I think he
> > can do that with pg_cancel_backend, and it could be dangerous.
>
> I think that is dangerous too because the user may have specifically
> turned AV off. That anti-wraparound vacuum might spring up right in a
> busy period and start working its way through many tables, all of which
> cause massive writes to occur. That's about as close to us causing an
> outage as I ever want to see. We need a way through that to allow the
> user to realise his predicament and find a good time to VACUUM. I never
> want to say to anybody "nothing you can do, just sit and watch, your
> production system will be working again in no time. Restart? no that
> won't work either."

I think the best way to handle this is to have two limits.

First limit attempts to autovacuum, but can be cancelled.

When we hit second limit, sometime later, then autovacuum cannot be
cancelled.

That would give us a breathing space if we need it.

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