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