Re: Per table autovacuum vacuum cost limit behaviour strange - Mailing list pgsql-hackers

On Mon, May 5, 2014 at 1:09 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:
> On Mon, Feb 17, 2014 at 7:38 AM, Haribabu Kommi
> <kommi.haribabu@gmail.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.".

It is not breaking the behavior. This setting can be overridden for
individual tables by
changing storage parameters. Still the cost values for the default
tables are under the guc limit.

> 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.

If user doesn't provide any table specific value then
autovacuum_vacuum_cost_limit guc
value is set to the table. So the same is used in the calculation.

> 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.

It works for the case where the table specific values less than the
default cost limit.
The same logic doesn't work with higher values. Usually the table
specific values
are more than default values to the tables where the faster vacuuming
is expected.

> 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 the proposed change, it works for both fine whether the table
specific value is higher
or lower to the default value. It works on the factor of the
difference between the default value
to the table specific value.

default autovacuum_vacuum_cost_limit = 10000
t1 - 1000, t2 - default, t3 - default, t4 - default  --> balance costs
t1 - 250, t2 - 2500, t3 - 2500, t4 - 2500.

t1 - 20000, t2 - default, t3 - default, t4 - default  --> balance
costs t1 - 5000, t2 - 2500, t3 - 2500, t4 - 2500.

Regards,
Hari Babu
Fujitsu Australia