Re: more about pg_toast growth

On Wed, Apr 10, 2002 at 07:59:53AM +0100, Richard A Lough wrote:
> Martijn van Oosterhout wrote:
> >
> > On Tue, Apr 09, 2002 at 12:26:38PM -0700, Jeffrey W. Baker wrote:
> > > Surely I'd be willing to learn how to tune Pg, since I've staked my
> > > operation on it. But, "max_fsm_pages" produces fewer pages on Google
> > > than "deep fried orangutan pancreas", and I do not often have the time
> > > to leaf through 460,000 lines of source. Some documentation would be
> > > swell.
> >
> > In case anything thinks he's kidding, he's not. max_fsm_pages produces a
> > total of 5 hits. 1 under runtime configuration for 7.3devel docs (which is
> > interesting since it's not runtime configurable). 1 in the postgreql.conf
> > sample file.
> >
>
> I'd be interested in trying to reproduce the problem. I'm trying to put
> some figures together on how postgres uses disk space.
>
> Unless I've missed something, there isn't anything in the earlier thread
> on whether the table gets dropped anytime, or whether the records get
> changed by INSERT's or whatever.

It seems he's inserting and deleting around 300MB of data per day. The
pg_toast table seems to be full of tuples on average 10k in size. 1000
inserts an hour, deletes 24,000 per day.

The problem with the fsm seems to stem from too many deletions between
vacuums, causing it to lose track. This shouldn't be too hard to test,
assuming you have the disk space :).
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Ignorance continues to thrive when intelligent people choose to do
> nothing. Speaking out against censorship and ignorance is the imperative
> of all intelligent people.