Обсуждение: The database slows down after a few weeks

Hello

We have a problem with our database. It is accessed by
Java using Hibernate. When the program has been
running for a couple of weeks some transactions are
delayed and are executed up to an hour later than then
they are first called.

If the computer is restarted it will take about a week
before the problems appear again.

If the following sequence of commands is executed
(reinserting the data) then it will take a couple of
weeks before the problem appears again.

pg_dump > file.sql
dropdb
createdb
psql < file.sql

Is this behaviour something seen before and in that
case: how to get around it? I also need to know why it
is happening so that when I have a solution I can
guarantee that the problem will not appear again.

The system with the problem is unfortunately placed
abroad with very limited remote access. Therefore "Try
this, and try that"-solutions are not possible in this
case. I have a hard time reproducing the error on my
local system which makes the problem so much harder.

If no cause can be found: Are there any other useful
tips?

Versions running:
Linux Fedora Core 3
psql (PostgreSQL) 7.4.8
jdk1.5.0_02



      ____________________________________________________________________________________
Luggage? GPS? Comic books?
Check out fitting gifts for grads at Yahoo! Search
http://search.yahoo.com/search?fr=oni_on_mail&p=graduation+gifts&cs=bz

In response to Bekar Bartaia <bbartaia@yahoo.com>:

> Hello
>
> We have a problem with our database. It is accessed by
> Java using Hibernate. When the program has been
> running for a couple of weeks some transactions are
> delayed and are executed up to an hour later than then
> they are first called.

What is your maintenance schedule?  Specifically: vacuum
and analyze?  What is the output of vacuum verbose when
you are experiencing the problem?

> If the computer is restarted it will take about a week
> before the problems appear again.
>
> If the following sequence of commands is executed
> (reinserting the data) then it will take a couple of
> weeks before the problem appears again.
>
> pg_dump > file.sql
> dropdb
> createdb
> psql < file.sql
>
> Is this behaviour something seen before and in that
> case: how to get around it? I also need to know why it
> is happening so that when I have a solution I can
> guarantee that the problem will not appear again.
>
> The system with the problem is unfortunately placed
> abroad with very limited remote access. Therefore "Try
> this, and try that"-solutions are not possible in this
> case. I have a hard time reproducing the error on my
> local system which makes the problem so much harder.
>
> If no cause can be found: Are there any other useful
> tips?
>
> Versions running:
> Linux Fedora Core 3
> psql (PostgreSQL) 7.4.8
> jdk1.5.0_02
>
>
>
>       ____________________________________________________________________________________
> Luggage? GPS? Comic books?
> Check out fitting gifts for grads at Yahoo! Search
> http://search.yahoo.com/search?fr=oni_on_mail&p=graduation+gifts&cs=bz
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org/


--
Bill Moran
http://www.potentialtech.com

Bekar Bartaia <bbartaia@yahoo.com> writes:
> If no cause can be found: Are there any other useful
> tips?

> Versions running:
> Linux Fedora Core 3
> psql (PostgreSQL) 7.4.8
> jdk1.5.0_02

The first tip I'd give you is to get onto something newer than PG 7.4.
Fedora 3 is pretty long in the tooth as well.

            regards, tom lane

On 7/27/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Bekar Bartaia <bbartaia@yahoo.com> writes:
>
> > Versions running:
> > Linux Fedora Core 3
> > psql (PostgreSQL) 7.4.8
> > jdk1.5.0_02
>
> The first tip I'd give you is to get onto something newer than PG 7.4.
> Fedora 3 is pretty long in the tooth as well.

Although unlikely to be related to your problem or its solution, but
jdk1.5.0_02 is certainly not recommended. The "5" series is currently
at 1.5.0_12.  Also, it is likely that an update to JRE6 (currently
1.6.0_02 Build 06) would yield few problems and significant
performance gains for a 10 minute effort.
-Damian