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@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com