Thread: 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