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

Re: Database-wide VACUUM ANALYZE

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Steven Flatt <steven(dot)flatt(at)gmail(dot)com>
Cc: Bill Moran <wmoran(at)collaborativefusion(dot)com>, Francisco Reyes <lists(at)stringsutils(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Database-wide VACUUM ANALYZE
Date: 2007-06-22 13:55:04
Message-ID: 20070622135504.GA8949@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-performance
Steven Flatt escribió:
> Thanks everyone.  It appears that we had hacked the 502.pgsql script for our
> 8.1 build to disable the daily vacuum.  I was not aware of this when
> building and upgrading to 8.2.
> 
> So it looks like for the past two weeks, that 36 hour db-wide vacuum has
> been running every 24 hours.  Good for it for being reasonably non-intrusive
> and going unnoticed until now. :)

Looks like you have plenty of spare I/O ;-)


> Although apparently not related anymore, I still think it was a good move to
> change autovacuum_freeze_max_age from 200 million to 2 billion.

Absolutely not related.  Also note that

1. autovacuum is not able (in 8.2 or older) to have more than one task
   running

2. autovacuum in 8.2 doesn't ever launch database-wide vacuums.  As of
   8.2 it only vacuums tables that are in actual danger of Xid
   wraparound (according to pg_class.relfrozenxid); tables that had been
   vacuumed the day before would not need another vacuum for Xid
   purposes (though if you had modified the table to the point that it
   needed another vacuum, that would be another matter).  Unless you
   consumed 200 million (or 2 billion) transactions during the day, that
   is.

-- 
Alvaro Herrera                  http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"The only difference is that Saddam would kill you on private, where the
Americans will kill you in public" (Mohammad Saleh, 39, a building contractor)

In response to

pgsql-performance by date

Next:From: Rainer BauerDate: 2007-06-22 14:01:49
Subject: Re: Data transfer very slow when connected via DSL
Previous:From: Joshua D. DrakeDate: 2007-06-22 13:54:01
Subject: Re: PITR Backups

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