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

<p><br /> On Feb 15, 2014 9:19 AM, "Alvaro Herrera" <<a
href="mailto:alvherre@2ndquadrant.com">alvherre@2ndquadrant.com</a>>wrote:<br /> ><br /> > Haribabu Kommi
escribió:<br/> ><br /> > > I changed the balance cost calculations a little bit to give priority to<br /> >
>the user provided per table autovacuum parameters.<br /> > > If any user specified per table vacuum
parametersexists and those are<br /> > > different with guc vacuum parameters then the<br /> > > balance
costcalculations will not include that worker in calculation. Only<br /> > > the cost is distributed between
otherworkers<br /> > > with specified guc vacuum cost parameter.<br /> > ><br /> > > The problem in
thiscalculation is if the user provides same guc values to<br /> > > the per table values also then it doesn't
considerthem in calculation.<br /> ><br /> > I think this is a strange approach to the problem, because if you<br
/>> configure the backends just so, they are completely ignored instead of<br /> > being adjusted.  And this
wouldhave action-at-a-distance consequences<br /> > because if you change the defaults in postgresql.conf you end up
with<br/> > completely different behavior on the tables for which you have carefully<br /> > tuned the delay so
thatthey are ignored in rebalance calculations.<br /> ><br /> > I think that rather than ignoring some backends
completely,we should be<br /> > looking at how to "weight" the balancing calculations among all the<br /> >
backendsin some smart way that doesn't mean they end up with the<br /> > default values of limit, which AFAIU is
whathappens now -- which is<br /> > stupid.  Not real sure how to do that, perhaps base it on the<br /> >
globally-configuredratio of delay/limit vs. the table-specific ratio.<br /> ><br /> > What I mean is that perhaps
thecurrent approach is all wrong and we<br /> > need to find a better algorithm to suit this case and more
generally.<br/> > Of course, I don't mean to say that it should behave completely<br /> > differently than now in
normalcases, only that it shouldn't give<br /> > completely stupid results in corner cases such as this one.<br />
><br/> > As an example, suppose that global limit=200 and global delay=20 (the<br /> > defaults).  Then we
havea global ratio of 5.  If all three tables being<br /> > vacuumed currently are using the default values, then
theyall have<br /> > ratio=5 and therefore all should have the same limit and delay settings<br /> > applied
afterrebalance.  Now, if two tables have ratio=5 and one table<br /> > has been configured to have a very fast
vacuum,that is limit=10000,<br /> > then ratio for that table is 10000/20=500.  Therefore that table should<br />
>be configured, after rebalance, to have a limit and delay that are 100<br /> > times faster than the settings
forthe other two tables.  (And there is<br /> > a further constraint that the total delay per "limit unit" should
be<br/> > so-and-so to accomodate getting the correct total delay per limit unit.)<br /> ><br /> > I haven't
thoughtabout how to code that, but I don't think it should be<br /> > too difficult.  Want to give it a try?  I
thinkit makes sense to modify<br /> > both the running delay and the running limit to achieve whatever ratio<br />
>we come up with, except that delay should probably not go below 10ms<br /> > because, apparently, some platforms
havethat much of sleep granularity<br /> > and it wouldn't really work to have a smaller delay.<br /> ><br />
>Am I making sense?<p>Yes makes sense and it's a good approach also not leaving the delay parameter as is. Thanks I
willgive a try.<p>Regards,<br /> Hari Babu<br /> Fujitsu Australia<br />