Horton

Re: [HACKERS] Autovacuum Improvements

Bruce Momjian <bruce@momjian.us>

View thread
From: Bruce Momjian <bruce@momjian.us>
To: Alvaro Herrera <alvherre@commandprompt.com>
Cc: Gregory Stark <stark@enterprisedb.com>, Heikki Linnakangas <heikki@enterprisedb.com>, Russell Smith <mr-russ@pws.com.au>, Darcy Buskermolen <darcyb@commandprompt.com>, Simon Riggs <simon@2ndquadrant.com>, "Matthew T. O'Connor" <matthew@zeut.net>, Pavan Deolasee <pavan@enterprisedb.com>, Christopher Browne <cbbrowne@acm.org>, pgsql-general@postgresql.org, pgsql-hackers@postgresql.org
Date: 2007-01-22T19:42:39Z
Lists: pgsql-hackers 
Alvaro Herrera wrote:
> Bruce Momjian wrote:
> > 
> > Yep, agreed on the random I/O issue.  The larger question is if you have
> > a huge table, do you care to reclaim 3% of the table size, rather than
> > just vacuum it when it gets to 10% dirty?  I realize the vacuum is going
> > to take a lot of time, but vacuuming to relaim 3% three times seems like
> > it is going to be more expensive than just vacuuming the 10% once.  And
> > vacuuming to reclaim 1% ten times seems even more expensive.  The
> > partial vacuum idea is starting to look like a loser to me again.
> 
> But if the partial vacuum is able to clean the busiest pages and reclaim
> useful space, currently-running transactions will be able to use that
> space and thus not have to extend the table.  Not that extension is a
> problem on itself, but it'll keep your working set smaller.

Yes, but my point is that if you are trying to avoid vacuuming the
table, I am afraid the full index scan is going to be painful too.  I
can see corner cases where partial vacuum is a win (I only have 4 hours
of idle I/O), but for the general case I am still worried that partial
vacuum will not be that useful as long as we have to scan the indexes.

-- 
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +