Re: Vacuum rate limit in KBps

On Sat, Jan 21, 2012 at 5:54 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
> What would be interesting is if we could monitor how long all *foreground* IO requests took. If they start exceeding some number, that means the system is at or near full capacity, and we'd like background stuff to slow down.

There's something to that...

On occasion, we've had a bit of "hue and cry" when sysadmins noticed
that nearly 100% of bandwidth was being chewed up by VACUUM.

A closer look showed that there wasn't a *true* problem, as there
wasn't anything else material looking for I/O. Letting VACUUM have
all the bandwidth it wants when the system isn't otherwise busy is a
pretty fine idea.

At such times, limiting VACUUM is counterproductive; it would be
better to let it go through and clean up as many tables in as short a
time as possible.

However, as soon as the number of processes waiting for I/O starts to
grow, you want VACUUM to back off.

That's an entirely more "dynamic" kind of dynamic than the suggested
parameterization indicates. There's enough there that I'm not sure
how yet to measure that it may be necessary to start with the more
static indicator of "maximum acceptable bandwidth usage."

As long as the parameters can be changed without requiring a
postmaster restart, it should be possible to do something more
adaptive if and when measurements emerge.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"