On Mar 14, 2011, at 2:36 PM, Robert Haas wrote:
> I'm not quite sure how we'd decide whether to do a "really lazy"
> vacuum or the kind we do now. The case where this approach wins big
> is when there are few or no dead tuples. In that case, we do a lot of
> work looking at the indexes and we don't get much out of it; plus we
> scan the heap twice instead of just once. If there are a lot of dead
> tuples, then we have to bite the bullet and do the whole thing.
<snip>
> Thoughts? Does this sound at all feasible/useful? Any ideas on how to tune it?
One way to look at this is that any system will have a limit on how quickly it can vacuum everything. If it's having
troublededicating enough IO to vacuum, then autovac is going to have a long list of tables that it wants to vacuum.
Whenyou're in that situation, you want to get to the next table that needs vacuuming as quickly as possible, so if
you'verun through the first heap scan and found only a limited number of dead tuples, it doesn't make sense to spend a
bunchof time scanning indexes and making a second heap scan (though, IIRC the second scan doesn't hit the entire heap;
itonly hits the tuples that were remembered as being dead).
Of course, going along the lines of an autovac-based tuning mechanism, you have to question how a table would show up
forautovac if there's not actually a number of dead tuples. One scenario is freezing (though I'm not sure if your
super-lazyvacuum could freeze tuples or not). Another is inserts. That might become a big win; you might want to
aggressivelyscan a table that gets data loaded into it in order to set hint/all visible bits.
From a manual standpoint, ISTM that super-lazy vac would be extremely useful for dealing with hint bits after a bulk
insertto a table that also has some update activity. Using a regular vacuum in that case would result in a lot of extra
workto deal with the small number of dead tuples.
Perhaps it would be useful to write a script that analyzed the output of vacuum verbose looking for tables where a
super-lazyvacuum would have made sense (assuming vacuum verbose provides the needed info). If we had such a script we
couldask folks to run it and see how much super-lazy vacuuming would help in the real world.
--
Jim C. Nasby, Database Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net
