Re: First steps with 8.3 and autovacuum launcher - Mailing list pgsql-hackers

Simon Riggs schreef: <blockquote cite="mid:1192171225.4233.480.camel@ebony.site" type="cite"><pre wrap="">On Fri,
2007-10-12at 07:17 +0100, Simon Riggs wrote: </pre><blockquote type="cite"><pre wrap="">On Fri, 2007-10-12 at 01:24
-0400,Alvaro Herrera wrote:   </pre><blockquote type="cite"><pre wrap="">Michael Paesold escribió:
</pre><blockquotetype="cite"><pre wrap="">Simon Riggs wrote:       </pre></blockquote><blockquote type="cite"><pre
wrap="">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?       </pre></blockquote><pre wrap="">Yes, I think it is easy to mark the
"isfor 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.     </pre></blockquote><pre wrap="">I think that is
dangeroustoo 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."   </pre></blockquote><pre wrap="">
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.
 </pre></blockquote> Just a few thoughts: <br /><br /> 1) In the postgresql.conf you can define if you use autovacuum. 
<br/> You make a parameter that states a time of day.  <br /> If autovacuum is canceled once and not performed manually
beforethat time, <br /> then it executes at that time (or just after the next system restart after that time).  <br />
Soyou ensure that it isn't delayed indefinitely and you execute it on a time the database is normally not under a heavy
load.<br/> As a standard value you could take 2am in the morning or so.  <br /><br /> 2) I you can cancel an autovacuum
thatway, could you prevent it by a statement to start executing in the first<br /> place, and then restart execution by
anotherstatement.  There are a few situations where vacuuming is entirely pointless<br /><br /> Example: <br /> a)
Everyonelogs out, upgradeprocedure of db is started<br /> b) drop indexes<br /> c) add tables/change tables/add
columns/changecolumns<br /> d) convert data<br /> e) drop tables/drop columns<br /> f) add indexes<br /> g) vacuum full
analyze<br/> h) Everyone starts new app<br /><br /> BTW: I like pg83, allready looking for implementation when it hits
theshelves...<br />