Re: Adjust autovacuum naptime automatically

"Matthew T. O'Connor" <matthew(at)zeut(dot)net> wrote:

Sorry, I should have explained more.

> What is this based on? That is, based on what information is it
> deciding to reduce the naptime?

If there are some vacuum or analyze jobs, the naptime is shortened
(i.e, autovacuum is accelerated). And if there are no jobs, the naptime
is lengthened (autovacuum is decelerated).

> Given that we can now specify the vacuum cost delay settings for
> autovacuum and disable tables and everything else, I'm not sure we this
> anymore, at least not as it was originally designed. It sounds like
> Itagaki is doing things a little different with his patch, but I'm not
> sure I understand it.

I noticed my method is based on different views from contrib/pg_autovacuum.
I'm afraid of the lack of vacuum by autovacuum. So if the database seems to
require frequent vacuums, I'll accelerate autovacuum, and vice versa.
If we have a small heavily-updated table and a large rarely-updated table,
we should vacuum the small one soon after vacuum on the large one is done,
even if the large vacuum takes long time. -- but hmm, it may be better to
have multiple autovacuums in such a case primarily.

> My vision of the maintenance window has always been very simple, that
> is, during the maintenance window the thresholds get reduced by some
> factor (probably a GUC variable) so during the day it might take 10000
> updates on a table to cause a vacuum but during the naptime it might be
> 10% of that, 1000. Is this in-line with what others were thinking?

I agree. We can use autovacuum thresholds and cost-delay parameters to
control the frequency and priority of vacuum. I don't think it is good
to control vacuums by changing naptime.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center