Hung backends
Hi,
I'm new to PostgreSQL and have been tasked with determining the cause of
hung processes on FreeBSD after the frontend app dies. Eventually we run out
of connections when hung processes = MAXBACKENDS.
ps -cl -U postgres returns:
UID PID PPID CPU PRI NI VSZ RSS WCHAN STAT TT TIME COMMAND
500 1395 1 0 2 0 4040 2380 select Ss ?? 0:01.17 postgres
500 2255 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2256 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2257 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2258 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2259 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
...
The java frontend I'm using to test this scenario makes multiple connections
and crashes(unhandled exception) before disconnecting. This appears to leave
one "postgres" proccess for each connection. Can anyone point me in the
right direction?
SELECT version(); -> PostgreSQL 7.0.2 on i386-unknown-freebsdelf4.0
Thanks for any advice.
Peter Schmidt
"Schmidt, Peter" <peter.schmidt@prismedia.com> writes:
The java frontend I'm using to test this scenario makes multiple connections
and crashes(unhandled exception) before disconnecting. This appears to leave
one "postgres" proccess for each connection. Can anyone point me in the
right direction?
SELECT version(); -> PostgreSQL 7.0.2 on i386-unknown-freebsdelf4.0
Hm. I'd definitely recommend updating to 7.0.3 if you are using
unreliable clients, because 7.0.2 forgets to run its transaction-abort
routine if the client disconnects mid-transaction, with various
annoying consequences. However, that wouldn't lead to the
above-described symptom.
It looks like the backends don't know that their clients have gone away,
which would suggest that the kernel is failing to deliver an EOF
indication on the socket connection. Are you using TCP connections,
or Unix sockets? Is there some other process that could be holding the
client end of the connection open?
regards, tom lane
It turned out the Java VM was still running on the clients, keeping TCP
connections open (exactly as you suggested). When the VM process is killed
all backends are cleaned up.
Thanks,
Peter
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, November 23, 2000 9:35 AM
To: Schmidt, Peter
Cc: 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] Hung backends
"Schmidt, Peter" <peter.schmidt@prismedia.com> writes:
The java frontend I'm using to test this scenario makes multiple
connections
and crashes(unhandled exception) before disconnecting. This appears to
leave
one "postgres" proccess for each connection. Can anyone point me in the
right direction?
SELECT version(); -> PostgreSQL 7.0.2 on i386-unknown-freebsdelf4.0
Hm. I'd definitely recommend updating to 7.0.3 if you are using
unreliable clients, because 7.0.2 forgets to run its transaction-abort
routine if the client disconnects mid-transaction, with various
annoying consequences. However, that wouldn't lead to the
above-described symptom.
It looks like the backends don't know that their clients have gone away,
which would suggest that the kernel is failing to deliver an EOF
indication on the socket connection. Are you using TCP connections,
or Unix sockets? Is there some other process that could be holding the
client end of the connection open?
regards, tom lane
Import Notes
Resolved by subject fallback
"Schmidt, Peter" <peter.schmidt@prismedia.com> writes:
It turned out the Java VM was still running on the clients, keeping TCP
connections open (exactly as you suggested).
Hmm, so the VM forgets to close connections belonging to a crashed
Java thread? Seems like a severe deficiency in the VM to me...
regards, tom lane
Tom Lane wrote:
"Schmidt, Peter" <peter.schmidt@prismedia.com> writes:
It turned out the Java VM was still running on the clients, keeping TCP
connections open (exactly as you suggested).Hmm, so the VM forgets to close connections belonging to a crashed
Java thread? Seems like a severe deficiency in the VM to me...regards, tom lane
There is no such thing as a crashed java thread. It either terminated
or is still running. You are supposed to explictly close your
connections to the db in java. If you don't you will have to wait until
the Connection object is garbage collected for it to be closed. If your
java app is sitting around not doing much then it might never get around
to calling the garbage collector.
Bottom line:
1) expliclty close connections yourself
2) Call System.gc() from time to time if you need to.
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com