Re: We need to log aborted autovacuums

Josh Berkus wrote:<br /><blockquote cite="mid:4D24C60E.4030408@agliodbs.com" type="cite"><blockquote type="cite"><pre
wrap="">Orshould it perhaps be a per-table counter in pg_stat_user_tables,
 
given your statement above?   </pre></blockquote><pre wrap="">
Or even a timestamp: last_autovacuum_attempt, which would record the
last time autovacuum was tried.  If that's fairly recent and you have a
large number of dead rows, you know what kind of problem you have and
can turn on debug. </pre></blockquote><br /> These are both reasonable ideas.  But there was just some kickback on
Tomas's"keeping timestamp of the lasts stats reset" patch recently, from the perspective of trying to limit per-table
statsbloat.  I think it's relatively easy to make a case that this situation is difficult enough to diagnose that a
littlebit of extra low-level logging is worthwhile.  That Josh and I have both been bit by it enough to be thinking
aboutpatches to make it easier to diagnost suggests it's obviously too hard to nail down.  But is this so common and
difficultto recognize that it's worth making all the table stats bigger?  That's a harder call.  <br /><br /> It's
alreadypossible to detect the main symptom--dead row percentage is much higher than the autovacuum threshold, but
there'sbeen no recent autovacuum.  That makes me less enthusiastic that there's such a genuine need to justify the
overheadof storing more table stats just to detect the same thing a little more easily.  I've been playing with the
MuninPG plug-in more recently, and I was just thinking of adding a dead row trend graph/threshold to it to address this
generalarea instead.<br /><br /> We could argue both sides of the trade-off of tracking this directly in stats for some
time,and I'd never expect there to be a clear victory for either perspective.  I've run into this vacuum problem a few
times,but certainly less than I've run into "why is the stats table so huge?"<br /><br /><pre class="moz-signature"
cols="72">--
 
Greg Smith   2ndQuadrant US    <a class="moz-txt-link-abbreviated"
href="mailto:greg@2ndQuadrant.com">greg@2ndQuadrant.com</a>  Baltimore, MD
 
PostgreSQL Training, Services and Support        <a class="moz-txt-link-abbreviated"
href="http://www.2ndQuadrant.us">www.2ndQuadrant.us</a>
"PostgreSQL 9.0 High Performance": <a class="moz-txt-link-freetext"
href="http://www.2ndQuadrant.com/books">http://www.2ndQuadrant.com/books</a>
</pre>