Re: Postgres performance slowly gets worse over a month

This is before my morning coffee, so someone please correct me if I am
wrong, but that should be the amount of space in your table that is
available for reuse before new pages need to be added. Whether it is
actually used is determined by a combination of factors including the
amount of insert/update activity on your system and the size of your
free space map. If you want to recover all of that space, you'll need to
do a vacuum full. As it looks now you could probably start vacuuming
this table more frequently to keep this number from growing so quickly.

Robert Treat

On Wed, 2002-07-24 at 07:14, Gaetano Mendola wrote:
>
> "Michael G. Martin" <michael(at)vpmonline(dot)com> wrote:
> > Check this value in the postgresql.con file:
> >
> > max_fsm_pages = 100000
> >
> > I had the same problem with the db growing, index no longer being used,
> > despite vacuums each night. Somewhere, there is a thread on this.
> >
> > Anyway, If you look at the vacuum stats each time your run vacuum, looks
> > to see how many pages are being updated between vacuums--i looked at the
> > removed x tuples in y pages value. Then, set this value to be greater
> > than the number of pages changed between vacuums. If more pages are
> > being updated between vacuums than what max_fsm_pages allows, the extra
> > pages won't be marked to be re-used--from what I understand. This then
> > results in the db growing and the optimizer starts to chose full table
> > scans since the db spans so many pages on the disk--at least this is
> > what happened in my db.
>
>
> Can you explain me this line that I obatin in the log
> after a vacuum analyze ?
>
> --Relation ua_user_data_exp--
> 2002-07-21 05:00:02 [28492] DEBUG: Pages 1402: Changed 2, reaped 1192,
> Empty 0, New 0; Tup 4277: Vac 16207, Keep/VTL 0/0, Crash 0, UnUsed 1, MinLen
> 393, MaxLen 680; Re-using: Free/Avail. Space 9148944/9141356;
> EndEmpty/Avail. Pages 0/1191. CPU 0.00s/0.03u sec.
>
> I'm wondering about "Re-using: Free/Avail. Space 9148944/9141356"
>
>
> Ciao
> Gaetano
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org