Re: more about pg_toast growth

On Tue, 2002-04-09 at 06:06, Jan Wieck wrote:
> 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.

VACUUM FULL takes an extremely long time, while dump and reload takes
about 5 minutes.  They both require exclusive access.

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

Please post a URL to this documentation.  Please post a URL which
clarifies whether the FSM is shared for all tables or if another is
allocated for each table.

I have "done a little math", and the peak amount of data in the table is
approximately 450MB.  A free space map tracking 800MB of space should be
plenty.

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

You haven't earned the right to insult me.

>     not behind it :-) Give it a chance and increase max_fsm_pages
>     to 10 million.

Your previous advice:

On Wed, 2002-03-13 at 07:22, Jan Wieck wrote:
>     Anyway,  I  would  suggest  you  increase  the  max_fsm_pages
>     parameter.  Commented out parameters in  the  postgresql.conf
>     file  means  "default".   You  said  you're doing about 1,000
>     inserts an hour and a daily bulk delete  of  approx.  24,000.
>     Assuming  most of the toast tuples are contigous, that'd mean
>     you are freeing something like 35,000 toast  pages.  I  would
>     suggest  a freespace map size of 50,000 pages, to start with.

I doubled that, and it still doesn't work.  You are suggesting I
increase your previous estimate by a factor of 200.  Your email of
2002-03-13 at 15:16 -0500 suggests a FSM of 50,000 pages allocates "some
more shared memory.  It's  surely  in  the range of a few megabytes..."
Will a FSM map 200 times larger require 200 times more memory, or is the
growth nonlinear?  How can I calculate this requirement?  Without some
documentation this database is inoperable.

I stand behind my previous statement: if PostgreSQL's unchecked table
growth can only be prevented by changing an undocumented configuration
key using an undocumented formula producing undocumented system impact,
the implementation is flawed.

I thank you for your advice, but your insults are not welcome.

-jwb