Re: Foreground vacuum and buffer access strategy

On Tue, Aug 13, 2013 at 4:11 PM, Greg Stark <stark@mit.edu> wrote:
> I'm not convinced using a ring buffer is necessarily that bad even if
> you want to vacuum as fast as possible. The reason we use a small ring
> buffer is to avoid poisoning the entire cache with vacuum pages, not
> to throttle the speed of vacuum by introducing synchronous wal
> flushes.

Right, but the DBA, being God, is entitled to override that.  A
regular user should not be able to change system policy here, but if a
superuser wants to do it, who are we to say no?

> I think we should increase the size of the ring buffer if we hit a
> synchronous wal buffer flush and there is less than some amount of wal
> pending. That amount is the relevant thing people might want to limit
> to avoid slowing down other transaction commits. The walwriter might
> even provide a relevant knob already for how much wal should be the
> maximum pending.

I doubt that would work out; the amount of WAL pending is going to
change extremely rapidly.  You can't increase the size of the ring
buffer for a vacuum that might run for another 24 hours on the basis
of an instantaneous measurement whose value might be completely
different a few milliseconds later or earlier.

Also, if there is a lot of WAL pending, then the system is likely I/O
saturated, which might be exactly the wrong time to allow more cache
poisoning.

Auto-tuning is nice when we can do it, but you can't
auto-guess-what-the-human-wants.

...Robert