Thread: BUG #1747: PostgreSQL 'hangs' after prolonged usage

The following bug has been logged online:

Bug reference:      1747
Logged by:          Wojciech Sobczuk
Email address:      wojtek@nemo.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

On Sat, Jul 02, 2005 at 02:12:42PM +0100, Wojciech Sobczuk wrote:
> 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.
Erm, what about vacuum? What kind of queries? explain analyze of such
"slow" query might help a lot to find a reason.

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

"Wojciech Sobczuk" <wojtek@nemo.pl> writes:
> 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

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

"Wojciech Sobczuk" <wojtek@nemo.pl> writes:
> 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.

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