Re: FSM search modes

On Thu, 2009-10-01 at 12:05 -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > I wonder if we should have a different mode of operation that only
> > attempted the truncate (say VACUUM TRUNCATE), optionally being
> > non-conditional about obtaining the required lock. That said, I wonder
> > even more whether any such hacks are still needed after the visilibity
> > map that changed the landscape for vacuum so dramatically.
>
> Yeah. The one thing in this thread that seemed like a good idea to me
> was to bias the FSM code a little bit towards returning space near the
> start of the relation, rather than its current behavior of treating all
> the free space equally. The current arrangement pretty much guarantees
> that you'll never recover from a bloating episode without taking special
> manual action. (This is not new to 8.4, the previous FSM code behaved
> the same.)
>
> The analogy in the back of my mind is the btree code that decides
> whether to split the current page or move to the next page when it has a
> full page and a new key that could go to either page. We found out that
> randomizing that choice made a *huge* improvement in the average
> behavior, even with the probabilities set up as 99-to-1. I'm thinking
> that just a small chance of resetting the search to the start of the
> relation might do the trick for FSM.

No real need to be random is there? In the bloated space scenario,
VACUUM will be triggered but will be unable to remove the empty blocks.
So in that case VACUUM can hint the FSM to perform "start from beginning
of relation" behaviour. When a searcher does that and can't usefully
find space quickly, then we can reset the hint back to normal. So it's
an automated mode change in both directions.

I think we can use the N-to-1 idea to define what we mean by "quickly",
but that should be in proportion to exactly how many free blocks there
were in table, not a fixed N. It would be a shame to make this work
poorly for very large tables.

It would be more useful to think of this as "look for huge chunks of
space and fill them" rather than "start at beginning", since space won't
always be right at start.

--
Simon Riggs www.2ndQuadrant.com