Skip site navigation (1) Skip section navigation (2)

Re: Postgres performance slowly gets worse over a month

From: Marc Spitzer <marc(at)oscar(dot)eng(dot)cv(dot)net>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: Postgres performance slowly gets worse over a month
Date: 2002-07-26 17:34:16
Message-ID: 20020726133416.B16887@oscar.eng.cv.net (view raw or flat)
Thread:
Lists: pgsql-admin
On Fri, Jul 26, 2002 at 09:11:25AM -0700, Joshua Daniel Franklin wrote:
> Thanks, this is exactly what I was thinking. 
> 
> --- "Michael G. Martin" <michael(at)vpmonline(dot)com> wrote:
> > You then remove a bunch of old tuples.  Space is still X+Y.  You now 
> > have 2 basic options:
> > 
> > 1.  Run a vacuum full -- this locks the entier table, and de-fragments 
> > all unused space, so space is now back to X. Table will grow incremently 
> > by Y over the next 6 months again.
> > 2.  Run a lazy vacuum-- no lock, no de-fragment, space is still X+Y. 
> >  Assuming max_fsm_pages was large enough to hold all the changed pages, 
> > over the next 6 months, the space remains fixed at about X+Y.  You are 
> > now re-using the unused table space.
> > 
> > Either solution will work.  If you really want to cut disk space, choose 
> > 1.  If you want to keep the space at about it optimal size and avoid any 
> > downtime, choose 2.
> > 
> > --Michael
> 

Do not forget to reindex the db after the delete, index's do not
manage them selves(if I remember correctly).  The index will continue
to grow until it eats your file system, as it did with me.  Also if
you do not reindex regulary it can take a looong time to do, much like
vacuum.  Also bigger indexes mean slower queries.

marc


> 
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Health - Feel better, live better
> http://health.yahoo.com
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

In response to

pgsql-admin by date

Next:From: ashwini sridharDate: 2002-07-26 17:42:38
Subject: unsubscribe me
Previous:From: LUTZ GEOFFREYDate: 2002-07-26 16:59:30
Subject: unsubscribe

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group