Re: Postgres performance slowly gets worse over a month

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.

My db was growing greatly like, 10's of thousands of tuples being added
to the unused value each day.  After bumping up the max_fsm_pages, my db
has grown much slower-as expected.

Do a vacuum full to get the database back to it normal size, then play
with the max_fsm_pages value to find a good value for you based on your
vacuum verbose output.

Hope that helps.  It worked for me.

--Michael



Marcos Garcia wrote:

>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@eldocomp.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@postgresql.org so that your
>>message can get through to the mailing list cleanly
>>
>>