Re: Multi-Versions and Vacuum

Added to TODO:

    * Allow free space map to be auto-sized or warn when it is too small

---------------------------------------------------------------------------

Andrew Sullivan wrote:
> On Fri, Jul 19, 2002 at 12:35:23PM -0400, Tom Lane wrote:
>
> > After VACUUM does its thing (and yes, it removes everything it legally
> > can) it reports the amount of free space it found on each page to the
> > FSM, and the FSM remembers as much of that info as it has room for ---
> > preferring pages with more free space, if it has to be selective.
>
> I think this is where the confusion lies.  The ever-growing tables
> that people are experiencing is somehow related to the new
> non-blocking vacuum and the value of the free space map settings.
> Previous posts on the matter have left some (well, me, at least) with
> the idea that the non-blocking vacuum doesn't get everything, just in
> case the free space map values are too small.
>
> 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?
>
> If so, it should be possible to calculate what one needs for the
> max_fsm_pages setting by calculating how much data is replaced or
> deleted in any given period, vacuum-to-vacuum, I guess.  What one
> would need to do is calculate how much space a row is taking up on
> disk, and then set the max_fsm_pages to comprise (almost?) enough
> space to keep a map big enough to include all the rows likely to be
> recovered by vacuum.  Or am I completely mad?  (I know, I know, "Yes"
> is a possible answer there.)
>
> A
>
> --
> ----
> Andrew Sullivan                               87 Mowat Avenue
> Liberty RMS                           Toronto, Ontario Canada
> <andrew@libertyrms.info>                              M6K 3E3
>                                          +1 416 646 3304 x110
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

--
  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