Re: Per table autovacuum vacuum cost limit behaviour strange

On 06/05/14 16:28, Amit Kapila wrote:
> On Mon, May 5, 2014 at 11:57 AM, Mark Kirkwood
> <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz> wrote:
>> On 05/05/14 15:22, Amit Kapila wrote:
>> Right, but have a look at the 1st message in this thread - the current
>> behavior (and to a large extent the above condition) means that setting
>> cost limits per table does not work in any remotely intuitive way.
>>
>> ITSM that the whole purpose of a per table setting in this context is to
>> override the behavior of auto vacuum throttling - and currently this does
>> not happen unless you get real brutal (i.e setting the cost delay to zero in
>> addition to setting cost limit...making the whole cost limit a bit
>> pointless).
> I think meaning of per table setting is just that it overrides the default
> value of autovacuum_vacuum_cost_limit for that table and the rest of
> calculation or concept remains same. This is what currently code does
> and the same is mentioned in docs as far as I can understand.
>
> As per current behaviour the per-table cost_limit is also adjusted based
> on the setting of GUC autovacuum_vacuum_cost_limit and right now it
> follows a simple principle that the total cost limit for all workers should be
> equal to autovacuum_vacuum_cost_limit. Even code has below comment:
>
> /*
> * Adjust cost limit of each active worker to balance the total of cost
> * limit to autovacuum_vacuum_cost_limit.
> */
>
> Now If you want to change for the case where user specifies value per
> table which is more than autovacuum_vacuum_cost_limit or otherwise,
> then I think the new definition should be bit more clear and it is better
> not to impact current calculation for default values.
>
> I could think of 2 ways to change this:
>
> a. if user has specified cost_limit value for table, then it just uses it
> rather than rebalancing based on value of system-wide guc variable
> autovacuum_vacuum_cost_limit
> b. another could be to restrict setting per-table value to be lesser than
> system-wide value?
>
> The former is used for auto vacuum parameters like scale_factor and
> later is used for parameters like freeze_max_age.
>
> Thoughts?
>
> Alvaro, do you think above options makes sense to solve this problem?

Yes indeed - the code currently working differently from what one would
expect. However the usual reason for handing knobs to the user for
individual object is so that special configurations can be applied to
them. The current method of operation of the per table knobs does not do
this (not without clubbing 'em on the head)

The (ahem) sensible way that one would expect (perhaps even need)
autovacuum throttling to work is:

- set sensible defaults for all the usual (well behaved) tables
- set a few really aggressive overrides for a handful of the naughty ones

Runaway free space bloat is one of the things that can really mangle a
postgres system (I've been called in to rescue a few in my time)...
there needs to be a way to control those few badly behaved tables ...
without removing the usefulness of throttling the others.

Regards

Mark