Re: Performance features the 4th

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'm certainly gonna test the patch out too. We aren't really I/O bound,
but it would be nice to have a database that only slowed down ~1% or so
during vacuuming.