Re: Vacuum rate limit in KBps

On 02/08/2012 11:22 AM, Bruce Momjian wrote:
> Why can't vacuum handle things automatically like checkpoint smoothing?
> Why can't it detect when it is falling behind and speed up? Why can't
> it see as busy background writer and slow down? Unless we answer
> these questions, we are not solving the problem for 99% of our users.

Scheduling VACUUM is a much harder problem than checkpoint smoothing.
Checkpoints can define "ahead" and "behind" simply; defining what those
terms mean for vacuum scheduling across a set of tables is tricky. A
possible downside to not vacuuming hard enough is that you'll have
tables reach their thresholds faster than you're cleaning them up.
That's a future bad event possible if you don't do the right thing now.
Predicting when that will happen is a whole different class of problem
than tracking whether checkpoints are running on schedule.

I have a design sketched out for something that adjusts the vacuum rate
based on background writer activity. I'm not optimistic that will ever
be automatic to the level you'd like here either. That looks like it
will be prone to feedback loops where autovacuum can starve forever. If
it ever lags enough that commonly accessed tables have become
inefficiently stored, they will then generate more buffer I/O to access,
and thus continue to block future vacuum work. That's the exact
situation where vacuum is most needed, even though it seems too
expensive to do. That's one trap people already fall into when doing
this by hand. Everyone who's ever said "autovacuum uses too much I/O
when it pops up unexpectedly during the day, I'm going to turn that off"
has seen how that works out. Hilarity ensues when the inevitable and
gigantic wraparound vacuums happen in the middle of the day instead.

Just trying to set the expectations bar realistically here. I don't
consider the easier problem of checkpoint smoothing a solved one,
either. Saying autovacuum needs to reach even that level of automation
to be a useful improvement over now is a slippery goal. Regardless, the
simple idea I submitted to this CF is clearly dead for now. I'll take
the feedback of "this level of change can live in a user-side tuning
tool" and do that instead. Since I was already thinking in the
direction of background activity monitoring, I have a good idea how I'd
need to approach this next, to be more likely to gain community buy-in
as an automated improvement. That's a longer term project though, which
I'll hopefully be able to revisit for 9.3.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com