Re: tuning autovacuum

On Fri, Oct 14, 2011 at 12:59 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> Ideally we would have something like checkpoint_warning that warns users
>> in the log when there are too few autovacuum workers and cleanup is
>> being delayed.
>
> I don't think that any table-stats based approach is going to work.  I
> think you need to measure the queue of tables which need autovacuuming.
>  So you do something like:
>
> If > 10% of tables and > 10 tables need autovac/autoanalyze for more
> than one polling interval in a row, then emit a warning.
>
> Note that there are solutions other than adding workers; the user could
> also lower the polling interval, decrease vacuum_delay, or do other
> things to make autovac faster.
>
> This would require tracking stats about the size of the autovac queue.

Right. It's my feeling that that's exactly what we need to do. It's
similar to what we already do for checkpoint spreading, except applied
to a different system maintenance activity. What would be really neat
is if we could not just detect the problem, but actually adjust the
cost delay on the fly to try to fix it - again, like we do with
checkpoints.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company