Re: Performance features the 4th

On Sun, 9 Nov 2003, Jan Wieck wrote:

> scott.marlowe wrote:
>
> > On Fri, 7 Nov 2003, Matthew T. O'Connor wrote:
> >
> >> ----- Original Message -----
> >> From: "Jan Wieck" <JanWieck(at)Yahoo(dot)com>
> >> > Tom Lane wrote:
> >> > > Gaetano and a couple of other people did experiments that seemed to show
> >> > > it was useful. I think we'd want to change the shape of the knob per
> >> > > later suggestions (sleep 10 ms every N blocks, instead of N ms every
> >> > > block) but it did seem that there was useful bang for little buck there.
> >> >
> >> > I thought it was "sleep N ms every M blocks".
> >> >
> >> > Have we seen any numbers? Anything at all? Something that gives us a
> >> > clue by what factor one has to multiply the total time a "VACUUM
> >> > ANALYZE" takes, to get what effect in return?
> >>
> >> I have some time on sunday to do some testing. Is there a patch that I can
> >> apply that implements either of the two options? (sleep 10ms every M blocks
> >> or sleep N ms every M blocks).
> >>
> >> I know Tom posted the original patch that sleept N ms every 1 block (where N
> >> is > 10 due to OS limitations). Jan can you post a patch that has just the
> >> sleep code in it? Or should it be easy enough for me to cull out of the
> >> larger patch you posted?
> >
> > The reason for the change is that the minumum sleep period on many systems
> > is 10mS, which meant that vacuum was running 20X slower than normal.
> > While it might be necessary in certain very I/O starved situations to make
> > it this slow, it would probably be better to be able to get a vacuum that
> > ran at about 1/2 to 1/5 speed for most folks. So, since the delta can't
> > less than 10mS on most systems, it's better to just leave it at a fixed
> > amount and change the number of pages vacuumed per sleep.
>
> I disagree with that. If you limit yourself to the number of pages being
> the only knob you have and set the napping time fixed, you can only
> lower the number of sequentially read pages to slow it down. Making read
> ahead absurd in an IO starved situation ...
>
> I'll post a patch doing
>
> every N pages nap for M milliseconds
>
> using two GUC variables and based on a select(2) call later.

I didn't mean "fixed in the code" I meant in your setup. I.e. find a
delay (10mS, 50, 100 etc...) then vary the number of pages processed at a
time until you start to notice the load, then back it off.

Not being forced by the code to have one and only one delay value, setting
it yourself.