Re: Postgres performance slowly gets worse over a month

Since this conversation had started, the presented solutions for the
disk space (disk space of tables and indexes) problem were:

 - reindex -> no space recovered

 - frequent vacuums -> some space recovered, meanwhile the database
keeps growing

 - vacuumm full -> some space recovered, meanwhile the database keeps
growing, quite similar to simple vacuum. we have also to keep in mind,
that the option "full", makes locks to the tables. Therefore, the
services around the database locks too, and with the growing of the
database the time spent for "vacumm full" increases, as well as the
downtime of the services around the database.


So, my doubts are:

 - There's nothing we can do to avoid the growth of the database, only
slow down that growth.

 - We, application developers that use postgresql databases have to
count with this problem.

Sorry if i'm being a little rude, but i'm in a real trouble.

Thanks in advance,


M.P.Garcia

On Wed, 2002-07-24 at 15:04, Robert Treat wrote:
> 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@vpmonline.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
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
M.P.Garcia
PT Inovação, SA
Serviços e Redes Móveis
Rua José Ferreira Pinto Basto - 3810 Aveiro
Tel: 234 403 253  -  Fax: 234 424 160
E-mail: marcos-p-garcia@ptinovacao.pt