Re: Per table autovacuum vacuum cost limit behaviour strange

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.