Skip site navigation (1) Skip section navigation (2)

Re: autovacuum

From: Chris Browne <cbbrowne(at)acm(dot)org>
To: pgsql-admin(at)postgresql(dot)org(dot)pgsql-hackers(at)postgresql(dot)org
Subject: Re: autovacuum
Date: 2006-02-01 17:14:41
Message-ID: 60psm7knlq.fsf@dba2.int.libertyrms.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-hackers
matthew(at)zeut(dot)net ("Matthew T. O'Connor") writes:
> Legit concern.  However one of the things that autovacuum is supposed to
> do is not vacuum tables that don't need it.  This can result in an overal
> reduction in vacuum overhead.  In addition, if you see that autovacuum is
> firing off vacuum commands during the day and they are impacting your
> response time, then you can play with the vacuum cost delay settings that
> are design to throttle down the IO impact vacuum commands can have.  In
> addition if you use 8.1, you can set per table thresholds, per table
> vacuum cost delay settings, and autovacuum will respect the work done by
> non-autovacuum vacuum commands.  Meaning that if you manually vacuum
> tables at night during a maintenance window, autovacuum will take that
> into account.  Contrib autovacuum couldn't do this.
>
> Hope that helps.  Real world feed-back is always welcome.

I have a question/suggestion...

Something we found useful with Slony-I was the notion of checking the
eldest XID on the system to see if there was any point at all in
bothering to vacuum.  I don't see anything analagous in autovacuum.c;
this might well be a useful addition.

In the Slony-I cleanup thread loop, we collect, in each iteration, the
current earliest XID.

In each iteration of this loop, we check to see if that XID has
changed.

- First time thru, it changes from 0 to 'some value' and so tries to do
  a vacuum.

- But supposing you have some long running transaction (say, a pg_dump
  that runs for 2h), it becomes pretty futile to bother trying to
  vacuum things for the duration of that transaction, because that
  long running transaction will, via MVCC, hold onto any old tuples.

It strikes me as a slick idea for autovacuum to take on that
behaviour.  If the daily backup runs for 2h, then it is quite futile
to bother vacuuming a table multiple times during that 2h period when
none of the tuples obsoleted during the 2h period will be able to be
cleaned out until the end.

Presumably this means that, during that 2h period, pg_autovacuum would
probably only issue ANALYZE statements...
-- 
let name="cbbrowne" and tld="ntlug.org" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/languages.html
Rules of  the Evil Overlord #51.  "If one of my  dungeon guards begins
expressing  concern over  the  conditions in  the beautiful  princess'
cell,  I  will immediately  transfer  him  to  a less  people-oriented
position." <http://www.eviloverlord.com/>

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2006-02-01 17:23:54
Subject: Re: [HACKERS] Bug: random() can return 1.0
Previous:From: Bruce MomjianDate: 2006-02-01 17:10:30
Subject: Re: Some platform-specific MemSet research

pgsql-admin by date

Next:From: Alvaro HerreraDate: 2006-02-01 17:29:05
Subject: Re: [HACKERS] autovacuum
Previous:From: Luba AlpinDate: 2006-02-01 09:38:12
Subject: Installation problem question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group