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
