Re: more about pg_toast growth

Jeffrey W. Baker wrote:
> On Fri, 2002-03-15 at 10:35, Jeffrey W. Baker wrote:
> > On Fri, 2002-03-15 at 06:47, Jan Wieck wrote:
> >
> > > > I have increased the free space map and will be able to restart the
> > > > postmaster today at around midnight GMT.
> > >
> > >     Any news?
> >
> > I couldn't work it in to the last maintenance window.  I'll give it
> > another run tonight.
>
> I increased the FSM and restarted postgres several weeks ago, and the
> toast tables continue to just grow and grow.  I set it to:
>
> max_fsm_relations = 1000    # min 10, fsm is free space map
> max_fsm_pages = 100000      # min 1000, fsm is free space map
>
> Now, my table with only 107490 rows takes 20GB on disk.  I dumped and
> reloaded the database and got 18.5GB free space back.

    Dump  and  reload  is  a  bit  drastic.  A  "VACUUM FULL" and
    "REINDEX" should've done that as well.

> I really think this is a bug in the implementation.

    Come on, Jeffrey, do a little math - it's not *that* hard  to
    understand.  A  free  space  map  of 100,000 entries can hold
    information about 100,000  pages  where  new  tuples  can  be
    stored.  that's  100,000  maybe partially filled 8K pages, so
    we're talking about 800MB here.

    As soon as your table once has more  than  100,000  partially
    filled  or entirely free pages due to deleted rows, you start
    loosing pages. Now, how much data where you  pumping  through
    that table by the hour?

    I  really  think this time the bug is in front of the screen,
    not behind it :-) Give it a chance and increase max_fsm_pages
    to 10 million.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #