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...
