Обсуждение: autovacuum workers warning

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.


[1] http://archives.postgresql.org/pgsql-hackers/2011-06/msg00678.php


--
    Euler Taveira de Oliveira - Timbira       http://www.timbira.com.br/
    PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Euler Taveira de Oliveira <euler@timbira.com> writes:
> +        if (!can_launch)
> +            ereport(LOG,
> +                    (errmsg("maximum number of autovacuum workers reached"),
> +                     errhint("Consider increasing autovacuum_max_workers (currently %d).",
> +                             autovacuum_max_workers)));

Isn't it normal for the launcher to max out the number of workers?
A log message that's generated routinely in normal operation doesn't
sound particularly helpful to me ...
        regards, tom lane

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@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On 26-10-2011 16:14, Alvaro Herrera wrote:
> 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.
>
Why not? You're saying that parallelizing the work won't help? As about 
autovacuum_vacuum_cost_delay, don't you think that 20ms isn't small enough to 
suggest decreasing instead of increasing the number of workers?

> Wasn't there some discussion recently on measuring the length of the
> work queue, or something like that?
>
Yes, there is. As I said, it is an expensive and approximate measure. I'm not 
saying that is not the right direction, I'm arguing that a hint is better than 
nothing. Right now the only way to know if it is out of workers is to query 
pg_stat_activity frequently.


--    Euler Taveira de Oliveira - Timbira       http://www.timbira.com.br/   PostgreSQL: Consultoria, Desenvolvimento,
Suporte24x7 e Treinamento
 

Excerpts from Euler Taveira de Oliveira's message of mié oct 26 16:57:18 -0300 2011:
> 
> On 26-10-2011 16:14, Alvaro Herrera wrote:
> > 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.
> >
> Why not? You're saying that parallelizing the work won't help? As about 
> autovacuum_vacuum_cost_delay, don't you think that 20ms isn't small enough to 
> suggest decreasing instead of increasing the number of workers?

I am saying that if you have two workers running, they increase their
cost delay to 40ms internally.  If you increase the max to four, they
would run at an effective delay of 80ms.

> > Wasn't there some discussion recently on measuring the length of the
> > work queue, or something like that?
> >
> Yes, there is. As I said, it is an expensive and approximate measure. I'm not 
> saying that is not the right direction, I'm arguing that a hint is better than 
> nothing. Right now the only way to know if it is out of workers is to query 
> pg_stat_activity frequently.

Well, I'm not saying there aren't other ways.

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

2011/10/26 Euler Taveira de Oliveira <euler@timbira.com>:
> I'm not saying that is not the right direction, I'm arguing that a hint is
> better than nothing. Right now the only way to know if it is out of workers
> is to query pg_stat_activity frequently.

The currently number of autovaccum workers could be in the errmsg only
instead errhint, then errhint could be omited from patch if there
isn't a good hint to report.

-- 
Dickson S. Guedes
mail/xmpp: guedes@guedesoft.net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br