Re: cost based vacuum (parallel) - Mailing list pgsql-hackers

On Mon, Nov 11, 2019 at 4:23 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Mon, Nov 11, 2019 at 12:59 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
> >
> > On Mon, Nov 11, 2019 at 9:43 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:
> > >
> > > On Fri, Nov 8, 2019 at 11:49 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > > >
> > > >
> > > > Yeah, I think it is difficult to get the exact balance, but we can try
> > > > to be as close as possible.  We can try to play with the threshold and
> > > > another possibility is to try to sleep in proportion to the amount of
> > > > I/O done by the worker.
> > > I have done another experiment where I have done another 2 changes on
> > > top op patch3
> > > a) Only reduce the local balance from the total shared balance
> > > whenever it's applying delay
> > > b) Compute the delay based on the local balance.
> > >
> > > patch4:
> > > worker 0 delay=84.130000 total I/O=17931 hit=17891 miss=0 dirty=2
> > > worker 1 delay=89.230000 total I/O=17931 hit=17891 miss=0 dirty=2
> > > worker 2 delay=88.680000 total I/O=17931 hit=17891 miss=0 dirty=2
> > > worker 3 delay=80.790000 total I/O=16378 hit=4318 miss=0 dirty=603
> > >
> > > I think with this approach the delay is divided among the worker quite
> > > well compared to other approaches
> > >
> > > >
> ..
> > I have tested the same with some other workload(test file attached).
> > I can see the same behaviour with this workload as well that with the
> > patch 4 the distribution of the delay is better compared to other
> > patches i.e. worker with more I/O have more delay and with equal IO
> > have alsomost equal delay.  Only thing is that the total delay with
> > the patch 4 is slightly less compared to other pacthes.
> >
>
> I see one problem with the formula you have used in the patch, maybe
> that is causing the value of total delay to go down.
>
> - if (new_balance >= VacuumCostLimit)
> + VacuumCostBalanceLocal += VacuumCostBalance;
> + if ((new_balance >= VacuumCostLimit) &&
> + (VacuumCostBalanceLocal > VacuumCostLimit/(0.5 * nworker)))
>
> As per discussion, the second part of the condition should be
> "VacuumCostBalanceLocal > (0.5) * VacuumCostLimit/nworker".
My Bad
I think
> you can once change this and try again.  Also, please try with the
> different values of threshold (0.3, 0.5, 0.7, etc.).
>
Okay, I will retest with both patch3 and path4 for both the scenarios.
I will also try with different multipliers.

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



-- 
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com