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