Re: scheduling autovacuum at lean hours only.

On a large partitioned database, ordinary vacuum is a very very difficult option.

Most of the time on such a system, most tables are dormant with respect to writes and never need to be vacuumed.  A
'dailyvacuum' is likely to take a full day to run on larger systems. Since ordinary vacuum can't be run on subsets of
tableswithout explicitly naming them one at a time (can't just vacuum a schema, tablespace, or use a regex to match
tablenames), good luck using it effectively if you have a few thousand tables in partitions.  You'll have to have
applicationcode or a script with knowledge of all the partition names and which are in need of an analyze/vacuum. 

Autovacuum is good enough in recent versions to be tuned to have very low impact though.  If you have to, rather than
stopand start it, just turn the delay or cost settings up and down during different parts of the day.  More than likely
however,it will be able to keep up with a single set of settings. 
In particular, rather than making the delay longer, make the appropriate cost larger -- page miss or page dirty affect
howmuch I/O it will do, and page hit will mostly affect how much CPU it uses. 

Perhaps a feature request is to have a version of the manual vacuum command that doesn't bother running on tables that
autovacuumwouldn't touch due to insufficient data churn.  This way, at lean hours one can manually vacuum to help an
autovacuumthat was tuned for very low impact 'catch up'. 
Also, if there was some way to make vacuum not stink so badly on tables that were just loaded with pg_load, where it
causeshuge write activity for tables that clearly have no bloat (I believe this is a hint bits thing?). 
________________________________________
From: pgsql-performance-owner@postgresql.org [pgsql-performance-owner@postgresql.org] On Behalf Of Ben
[bench@silentmedia.com]
Sent: Wednesday, February 11, 2009 8:46 AM
To: Rajesh Kumar Mallah
Cc: PostgreSQL Performance
Subject: Re: [PERFORM] scheduling autovacuum at lean hours only.

On Feb 11, 2009, at 6:57 AM, Rajesh Kumar Mallah wrote:

> why is it not a good idea to give end users control over when they
> want to run it ?

There's nothing stopping you from just turning off autovacuum and
running vacuum manually. The point of autovacuum is to vacuum "as
needed."


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance