Re: [HACKERS] autovacuum workers warning

2011-10-26 Thread Tom Lane
Euler Taveira de Oliveira eu...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] autovacuum workers warning

2011-10-26 Thread Alvaro Herrera

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] autovacuum workers warning

2011-10-26 Thread Euler Taveira de Oliveira

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, Suporte 24x7 e Treinamento

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] autovacuum workers warning

2011-10-26 Thread Alvaro Herrera

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] autovacuum workers warning

2011-10-26 Thread Dickson S. Guedes
2011/10/26 Euler Taveira de Oliveira eu...@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: gue...@guedesoft.net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers