Re: Postgres performance slowly gets worse over a month

I have the same problem.
My database is getting slower and slower.

I realized that the disk space used by the database is also increasing
and for me this is the reason for the bad performance.

what i have tryed:

- vacuumdb 4 times per day
- drop and create the indexes

The only solution I know is to make a dump and a restore.
In my case i can't stop my service, but i'm forced to do it.

To check that the problem wasn't in my code i made the following test:

------------------------------
AUTOCOMMIT = 1

FOR 1 to 120000{

INSERT RECORD
UPDATE RECORD
}

DELETE ALL INSERTED RECORDS
------------------------------

I have run this test program several times and after each time i have
run the vacummdb.

What i have realized is that every time i run this program the database
is growing and growing and running the vacummdb only recovers some
space.

How can this problem be solved???

Thanks,

M.P.Garcia

On Tue, 2002-07-23 at 19:29, Robert M. Meyer wrote:
> Well, we're running a hardware, three disk RAID5, on an
> sym53c896-0-<2,*>: FAST-20 WIDE SCSI 40.0 MB/s interface. This is a
> Compaq 3500 system with a CR3500 raid controller. An md5sum of a 1.2Gig
> file takes less than two minutes.
>
> We tried rebuilding the indices but that didn't seem to help. We had an
> outside consultant do the rebuild and he's not available now so I don't
> know what command he ran to do it.
>
> I've never used 'sar'. If you can believe it, I've been poking around
> with Unix for the last 20 years and I've never even seen the 'man' page
> for 'sar'. I probably should look into it. What flags would give me
> the most information to help figger out what's going on here?
>
> Of course the troubleshooting increment is going to be a month or more
> so this will probably take some time to resolve :-)
>
> Cheers!
>
> Bob
>
>
> On Tue, 2002-07-23 at 14:08, Naomi Walker wrote:
> >
> > >Nightly, we're doing a 'vacuumdb -a -z' after stopping and restarting
> > >the database. The performance will get so bad after a month that we
> > >start to see load spikes in excess of 30. Normally, we don't see load
> > >over 2.5 during the heaviest activity and generally less than 1.0 most
> > >of the time.
> > Typically, performance is linked to your I/O, but my first guess in this
> > case has to do with your indices. As a test, next time performance gets
> > really rotten, drop your indicies and rebuild them. It cannot hurt, and
> > might just help.
> >
> > The trick here is to see what is happening while it is tanking. What does
> > your disk configuration look like? Is it a raid or stripe where reads are
> > spread out among more than one controller? Do sar reports point to
> > anything in particular?
> >
> > ----------------------------------------------------------------------------
> > ----------------------------------
> > Naomi Walker
> > Eldorado Computing, Inc
> > Chief Information Officer
> > nwalker(at)eldocomp(dot)com
> > 602-604-3100 x242
> >
> >
> --
> Robert M. Meyer
> Sr. Network Administrator
> DigiVision Satellite Services
> 14 Lafayette Sq, Ste 410
> Buffalo, NY 14203-1904
> (716)332-1451
>
>
> ---------------------------(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
--
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(at)ptinovacao(dot)pt