Thread: 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
Attachment
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