Re: autovacuum workers warning

Excerpts from Euler Taveira de Oliveira's message of mar oct 25 16:56:12 -0300 2011:
> Hi,
>
> Some time ago [1], I proposed print a message every time there isn't
> autovacuum slots available and it asks for another one. It is not a complete
> solution for autovacuum tuning but it would at least give us a hint that
> number of workers is insufficient to keep up with the current load. The
> accurate number of slots needed would be the optimal solution but that
> information is not free (it would have to check every table in the databases
> available to get the approximate number of slots needed. Approximate because
> some table could be finishing the operation). A new warning is better than
> nothing. If we decided to improve this area in a future we should remove the
> warning but right now it would be an excelent hint to tune autovacuum.

Well, just increasing the number of workers would do nothing to solve
the problem, because the more workers there are, the slower they work.
The actual solution to the problem would be decreasing
autovacuum_vacuum_delay_cost, and/or related knobs.

I'm sure we need more data on autovacuum activities to properly tune
autovac, but I'm not sure that "maxxing out max_workers" is one of them.
Wasn't there some discussion recently on measuring the length of the
work queue, or something like that?

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support