Re: 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"