Re: Perfomance decreasing

Does it help if you drop and recreate the indexes, in addition to the vacuuming
you're doing now?  I think this was suggested not long ago on this list.




Erwin Lansing <erwin%lansing.dk@interlock.lexmark.com> on 08/14/2001 04:38:59 AM

To:   pgsql-general%postgresql.org@interlock.lexmark.com
cc:    (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject:  Re: [GENERAL] Perfomance decreasing


On Tue, Aug 14, 2001 at 02:06:40PM +0600, Alexander Loginov wrote:
> Hello.
>
>        I have a question about perfomance.
>        I'm running PostgreSQL 7.1.2 at FreeBSD 4.3.
>
>        For  the first 1-2 days of running perfomance is excellent. But
>        after  that,  speed  began  to  decrease.  And  after a week of
>        operation, perfomance  falls  8-10  times, than at first day of
>        using.
>
>        I'm  doing  vacuum  periodically  (once a hour), but perfomance
>        still falls down.
>
>        After that I dump database as text file, make dropdb & createdb
>        and  after  that,  restore  database from dump -> Perfomance is
>        excellent again (for 1-2 days).
>
>        Why this situation occures? May be I must use "VACUUM ANALYSE"
>        instead of VACUUM?
>

I have actually the same problem, also FreeBSD 4.3, pgsql 7.1.2. I do
use VACUUM ANALYSE quite often. The problem in the end gets that bad
that perl-jobs cannot perform any SELECTs, or at least they stop
returning results before dbi times out. So far I have tracked the
problem down to the size of the database in the filesystem, where
problems start occurring when it exceeds 1,4 Gb. A
dump/drop/create/restore reduces files size to approx. 350 Mb.

Any pointers would be helpful as a weekly dump/restore is not quite
optimal :)

/erwin

--
Erwin Lansing       --        http://droso.org
"You've got mail"

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster