On Feb 15, 2014 9:19 AM, "Alvaro Herrera" <alvhe...@2ndquadrant.com> wrote: > > Haribabu Kommi escribió: > > > I changed the balance cost calculations a little bit to give priority to > > the user provided per table autovacuum parameters. > > If any user specified per table vacuum parameters exists and those are > > different with guc vacuum parameters then the > > balance cost calculations will not include that worker in calculation. Only > > the cost is distributed between other workers > > with specified guc vacuum cost parameter. > > > > The problem in this calculation is if the user provides same guc values to > > the per table values also then it doesn't consider them in calculation. > > 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. Regards, Hari Babu Fujitsu Australia
