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

From Haribabu Kommi
Subject Re: Per table autovacuum vacuum cost limit behaviour strange
Date
Msg-id CAJrrPGdK=nhTHUCsURgXQyG6RH=rJ8b4CdYhzGhwH=UuzaO9Ng@mail.gmail.com
Whole thread Raw
In response to Re: Per table autovacuum vacuum cost limit behaviour strange  (Haribabu Kommi <kommi.haribabu@gmail.com>)
Responses Re: Per table autovacuum vacuum cost limit behaviour strange
Re: Per table autovacuum vacuum cost limit behaviour strange
List pgsql-hackers
On Sat, Feb 15, 2014 at 10:47 AM, Haribabu Kommi <kommi.haribabu@gmail.com> wrote:

On Feb 15, 2014 9:19 AM, "Alvaro Herrera" <alvherre@2ndquadrant.com> wrote:
>
> I think this is a strange approach to the problem, because if you
> configure the backends just so, they are completely ignored instead of
> being adjusted.  And this would have action-at-a-distance consequences
> because if you change the defaults in postgresql.conf you end up with
> completely different behavior on the tables for which you have carefully
> tuned the delay so that they are ignored in rebalance calculations.
>
> I think that rather than ignoring some backends completely, we should be
> looking at how to "weight" the balancing calculations among all the
> backends in some smart way that doesn't mean they end up with the
> default values of limit, which AFAIU is what happens now -- which is
> stupid.  Not real sure how to do that, perhaps base it on the
> globally-configured ratio of delay/limit vs. the table-specific ratio.
>
> What I mean is that perhaps the current approach is all wrong and we
> need to find a better algorithm to suit this case and more generally.
> Of course, I don't mean to say that it should behave completely
> differently than now in normal cases, only that it shouldn't give
> completely stupid results in corner cases such as this one.
>
> As an example, suppose that global limit=200 and global delay=20 (the
> defaults).  Then we have a global ratio of 5.  If all three tables being
> vacuumed currently are using the default values, then they all have
> ratio=5 and therefore all should have the same limit and delay settings
> applied after rebalance.  Now, if two tables have ratio=5 and one table
> has been configured to have a very fast vacuum, that is limit=10000,
> then ratio for that table is 10000/20=500.  Therefore that table should
> be configured, after rebalance, to have a limit and delay that are 100
> times faster than the settings for the other two tables.  (And there is
> a further constraint that the total delay per "limit unit" should be
> so-and-so to accomodate getting the correct total delay per limit unit.)
>
> I haven't thought about how to code that, but I don't think it should be
> too difficult.  Want to give it a try?  I think it makes sense to modify
> both the running delay and the running limit to achieve whatever ratio
> we come up with, except that delay should probably not go below 10ms
> because, apparently, some platforms have that much of sleep granularity
> and it wouldn't really work to have a smaller delay.
>
> Am I making sense?

Yes makes sense and it's a good approach also not leaving the delay parameter as is. Thanks I will give a try.

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.

By this way the worker with user specified autovacuum cost parameters is not ignored completely.
Patch is attached. Please let me know your suggestions.

Regards,
Hari Babu
Fujitsu Australia
Attachment

pgsql-hackers by date:

Previous
From: Jeff Janes
Date:
Subject: Re: HBA files w/include support?
Next
From: Robert Haas
Date:
Subject: Re: Auto-tuning work_mem and maintenance_work_mem