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

From Amit Kapila
Subject Re: Per table autovacuum vacuum cost limit behaviour strange
Date
Msg-id CAA4eK1KDAUP=g59ku_0ekDoRjaGfEamyadQn5rJVt3j5oErLXg@mail.gmail.com
Whole thread Raw
In response to Re: Per table autovacuum vacuum cost limit behaviour strange  (Mark Kirkwood <mark.kirkwood@catalyst.net.nz>)
Responses Re: Per table autovacuum vacuum cost limit behaviour strange
List pgsql-hackers
On Mon, May 5, 2014 at 11:57 AM, Mark Kirkwood
<mark.kirkwood@catalyst.net.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-wideguc 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?

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



pgsql-hackers by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: Docs for 9.4's worker_spi?
Next
From: "MauMau"
Date:
Subject: Re: elog a stack trace