Re: pg_autovacuum

On Tuesday 23 March 2004 12:32 am, Josh Berkus wrote:
> Matt,
>
> > What I'm thinking is that the VACUUM command could be modified to write
> > down some data from the stats system at vacuum time.  Once the VACUUM
> > command writes this down for itself then pg_autovacuum just uses that
> > number to make its decision.  Again, we are trying to reduce as much as
> > possible superfluous vacuums.  If an admin vacuums his whole cluster
> > every Sunday night that may prevent lots of  vacuums occurring during
> > business hours that effect processing.
>
> Be nice, yes.    However, my experience is that mixing manual and
> autovacuums is bound to lead to endless support requests, because conflicts
> *will* arise. So in some ways you'd be working to please those who can't be
> pleased.

Could you please expound on this.  What types of conflicts in what situations?  
Maybe I'm missing something, but I really don't see how this could cause any 
problems, and would only help prevent unneeded vacuums since pg_autovacuum 
would always know the stats as of the last vacuum no matter who issued it, 
because the vacuum itself would write it down.

> Hmmm ... that's an interesting idea.    I'd more been thinking about
> vacuums of small tables, where a high-end server under low load could
> vacuum several tables in parallel, one per CPU.   However, working through
> tablespaces would make a lot of sense.

The reason tablespaces might make sense is that vacuum is I/O bound, not CPU 
bound.  So having 4 processors isn't going to make multiple vacuums go any 
faster.  Now you might have enough I/O bandwidth such that multiple 
simultaneous vacuums are actually faster that serialized.  You could probably 
test this yourself, I would be curious to see some data on this.

> You would get into a cycle where you are running vacuum continuously, all
> the time.  This is a very bad situation and the admin should be warned
> about it via the logs.

Finding a way to log this would be good. 

> Hmmm ... thinkiing about that, are we changing the defaults for threshold
> and scale?  You and I have discussed this, yes?

Right now pg_autovacuum doesn't change the thresholds, but it does change the 
sleep time.  Just like the vacuum and analyze command options there is a 
sleep base value and a sleep scaling factor.  The purpose was to make sure 
that pg_autovacuum was never continuously issuing vacuum commands.  I think 
that if pg_autovacuum is vacuuming too often then the admin needs to tune and 
turn it down.


Matthew