Re: tuning autovacuum

On Wed, Jun 8, 2011 at 5:54 PM, Euler Taveira de Oliveira
<euler(at)timbira(dot)com> wrote:
> LOG: maximum number of autovacuum workers reached
> HINT: Consider increasing autovacuum_max_workers (currently 5).
>
> Comments?

Is the hint correct? I mean, what if there were 100 small tables that
needed vacuuming all at the same time. We'd hit this limit no matter
how high you set autovacuum_max_workers, but it wouldn't be right to
set it to 101 just because every once in a blue moon you might trip
over the limit.

I think it'd be really useful to expose some more data in this area
though. One random idea is - remember the time at which a table was
first observed to need vacuuming. Clear the timestamp when it gets
vacuumed. Then you can do:

SELECT blahblah FROM wumpity WHERE
time_at_which_we_first_noticed_it_needed_vacuuming < now() - '1
hour'::interval;

...or something of the sort. That way you can alert if autovacuum
starts to fall too far behind, but you get to pick the definition of
"too far behind".

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