Re: Buglist

On Wed, Aug 20, 2003 at 05:41:18PM -0400, Tom Lane wrote:
> Andrew Sullivan <andrew(at)libertyrms(dot)info> writes:
> > I don't actually think having the process done in real time will
> > help, though -- it seems to me what would be more useful is an even
> > lazier vacuum: something that could be told "clean up as cycles are
> > available, but make sure you stay out of the way." Of course, that's
> > easy to say glibly, and mighty hard to do, I expect.
>
> I'd love to be able to do that, but I can't think of a good way.
>
> Just nice'ing the VACUUM process is likely to be counterproductive
> because of locking issues (priority inversion). Though if anyone cares
> to try it on a heavily-loaded system, I'd be interested to hear the
> results...

How about the really simple solution: explicit yields. Along the lines of:

for each page
vacuum page
sleep 5ms

As long as you sleep without holding any locks, this means it would (very
slowly) interate over the table. If the auto-vacuum can target tables it
would be even more effective. A slightly more sophisticated version would
be:

VACUUM MAX 10MB/s (or some such syntax)

Then you just keep a count of the number of processed pages and the amount
of time and if you're getting ahead of yourself, sleep a while.

Given lazy vacuum doesn't hold locks for long periods, it could be an idea
to continuously spend 1% of your disk bandwidth on a background vacuum. As
for vacuum full, I don't know if you could do the same thing.

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato