Re: Per table autovacuum vacuum cost limit behaviour strange

On Mon, Feb 17, 2014 at 7:38 AM, Haribabu Kommi
<kommi(dot)haribabu(at)gmail(dot)com> wrote:
> I modified the "autovac_balance_cost" function to balance the costs using
> the number of running workers, instead
> of default vacuum cost parameters.
>
> Lets assume there are 4 workers running currently with default cost values
> of limit 200 and delay 20ms.
> The cost will be distributed as 50 and 10ms each.
>
> Suppose if one worker is having a different cost limit value as 1000, which
> is 5 times more than default value.
> The cost will be distributed as 50 and 10ms each for other 3 workers and 250
> and 10ms for the worker having
> cost limit value other than default. By this way also it still ensures the
> cost limit value is 5 times more than other workers.

Won't this change break the basic idea of autovacuum_vacuum_cost_limit
which is as follows:
"Note that the value is distributed proportionally among the running autovacuum
workers, if there is more than one, so that the sum of the limits of each worker
never exceeds the limit on this variable.".

Basically with proposed change, the sum of limits of each worker will be more
than autovacuum_vacuum_cost_limit and I think main reason for same is that
the new calculation doesn't consider autovacuum_vacuum_cost_limit or other
similar parameters.

I think current calculation gives appropriate consideration for table level
vacuum settings when autovacuum_vacuum_cost_limit is configured
with more care (i.e it is more than table level settings). As an example
consider the below case:

autovacuum_vacuum_cost_limit = 10000
t1 (autovacuum_vacuum_cost_limit = 1000)
t2 (default)
t3 (default)
t4 (default)

Consider other settings as Default.

Now cost_limit after autovac_balance_cost is as follows:
Worker-1 for t1 = 322
Worker-2 for t2 = 3225
Worker-3 for t3 = 3225
Worker-4 for t3 = 3225

So in this way proper consideration has been given to table level
vacuum settings and guc configured for autovacuum_vacuum_cost_limit
with current code.

Now it might be the case that we want to improve current calculation for
cases where it doesn't work well, but I think it has to be better than current
behaviour and it is better to consider both guc's and table level settings with
some better formula.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com