The following bug has been logged online:

Bug reference: 1747
Logged by: Wojciech Sobczuk
Email address: wojtek (AT) nemo (DOT) pl
PostgreSQL version: 8.0.3
Operating system: FreeBSD 5.4-RELEASE
Description: PostgreSQL 'hangs' after prolonged usage
Details:

Hello,

I have a very busy dual-RAID1 machine, doing tens up to hundreds of queries
per second. After around 6 days of running at that kind of a load
postgresql starts processing queries very very slowly (load jumps to 40 from
2) which brings my system to it's knees. The only thing which helps then is
hard-restarting postgresql - and then it works fine for another 6 days.
Rebooting the machine has no influence on the performance so it seems to be
a postgresql issue.

My database takes up 9GB on the hdd and has millions of rows in around 40
relations (4-5 are really large, the rest is small).

I use a bit of triggers, but besides that it's just standard SQL queries
(called from Java using the 8.0 JDBC driver).

Please let me know if you're able to reproduce this bug - you just need to
put postgresql at a very high load for a long time (that's my first guess, I
don't have an enviroment in which I could do such tests, and even if I did I
woudln't know what to look for).

Any ideas how to fix it?

Thanks

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Sat, Jul 02, 2005 at 02:12:42PM +0100, Wojciech Sobczuk wrote:
"slow" query might help a lot to find a reason.

p.
--
Pawe³ Bernat; uselessness' lover;
select'<asm'||chr(64)||'asm'||'.'||'flynet'||chr(4 6)||'pl>'as email;
Slowly and surely the unix crept up on the Nintendo user ...

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

"Wojciech Sobczuk" <wojtek (AT) nemo (DOT) pl> writes:
No, otherwise we'd have heard a lot more complaints than yours.

You'll need to provide sufficient information to let someone else
reproduce the problem. This report is not even a start at that.

I would suggest doing some investigation to determine why things get
slower --- eg, is the system starting to swap heavily? Watching top,
vmstat, pg_stat_activity, etc would probably be informative.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

"Wojciech Sobczuk" <wojtek (AT) nemo (DOT) pl> writes:
BTW, exactly what do you mean by "hard-restarting postgresql"?
A machine reboot would certainly include a postmaster restart, so
you seem to be using that term to mean something else than what most
of us would consider it to mean.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend