Re: First steps with 8.3 and autovacuum launcher

Simon Riggs schreef:
> 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.
>
>
Just a few thoughts:

1) In the postgresql.conf you can define if you use autovacuum.
You make a parameter that states a time of day.
If autovacuum is canceled once and not performed manually before that time,
then it executes at that time (or just after the next system restart
after that time).
So you ensure that it isn't delayed indefinitely and you execute it on a
time the database is normally not under a heavy load.
As a standard value you could take 2am in the morning or so.

2) I you can cancel an autovacuum that way, could you prevent it by a
statement to start executing in the first
place, and then restart execution by another statement. There are a few
situations where vacuuming is entirely pointless

Example:
a) Everyone logs out, upgradeprocedure of db is started
b) drop indexes
c) add tables/change tables/add columns/change columns
d) convert data
e) drop tables/drop columns
f) add indexes
g) vacuum full analyze
h) Everyone starts new app

BTW: I like pg83, allready looking for implementation when it hits the
shelves...