Postgres Pro
Downloads
Home   >   mailing lists

Thread: Hung backends

Hung backends

From
"Schmidt, Peter"
Date:

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

Re: Hung backends

From
Tom Lane
Date:
"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

Re: Hung backends

From
Tom Lane
Date:
"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

RE: Hung backends

From
"Schmidt, Peter"
Date:

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

Re: Hung backends

From
Joseph Shraibman
Date:
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