Re: Free space mapping (was Re: Multi-Versions and Vacuum)

Tom Lane wrote:
> Andrew Sullivan <andrew@libertyrms.info> writes:
> > But if I understand now what you're saying, it's this: the vacuum
> > _does_ get everything, but if the free space map settings are too
> > small, the free space map doesn't know about that free space;
> > therefore, the space never gets re-used for future data, because as
> > far as the postmaster is concerened, the space isn't available.  So
> > the tables keep growing because they have to append their data at the
> > end of the file (because the free space map hasn't reported any free
> > space).  Run vacuum a few times like this, and there is a substantial
> > amount of unused space in the tables, where the free space map
> > doesn't have an entry for it.  Is that right?
>
> Well, the design theory was that once you free enough space on a
> particular page, it *will* get into the FSM and then will be filled up.
> The fact that small amounts of free space don't get re-used quickly
> doesn't bother me a whole lot; the table size should still stabilize
> at some proportion of free to used space that depends on your vacuum
> frequency.
>
> However, if the FSM is vastly smaller than the number of
> actively-changing pages in your database, then that argument breaks down
> --- in particular, if the FSM can't even keep track of all the
> completely-empty pages in your database then you are going to suffer
> progressive leakage.  I'm not sure where the problem gets bad enough to
> worry about, and have not had time to try to do a probabilistic
> analysis.
>
> If we could determine where trouble sets in, perhaps we could detect the
> situation and print warnings during VACUUM?  Another possibility is to
> auto-size FSM during postmaster startup, perhaps as a fraction of the
> total DB size measured by "du".  (But that probably doesn't work if
> you've been playing symlink games.)  In any case we need more knowledge
> about appropriate FSM sizes than we have at the moment.
>
> The whole FSM concept is new in 7.2 and I'm sure it still needs
> refinement.  Ideas welcome.

Not sure if I have said this before, but I think the auto-sizing needs
to get some feedback from when empty pages overflow the FSM _and_ when
later there is demand for free pages and none exist in the FSM.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073