[Gllug] DB connection / netstat wierdness

[Richard Huxton]

On Tuesday 03 February 2004 17:20, Daniel P. Berrange wrote:
> Ok, so the following situation is really confusing the hell out of me.
> I have two application servers and one database server. Typically each app
> server has 150 connections open the database, making 300 in total. Now
> what's strange is that if I don't shutdown the application servers
> cleanly (eg killall -9 java, or C-A-D the box), then database for
> ever things its got TCP connections open from that app server, even
> though the client end of the connection is long since dead. These
> never time out, forcing me to bounce the DB eventually.

At a wild guess, your java environment is using connection pooling and the 
pool is keeping the connections open.

> app01# netstat -t -n -a |grep 5432 | grep 192.168.100.4 | wc -l
>    150

Yep - Postgresql is saying the connections are open, because the app server 
has them open.

> If I look at the second app server which I rebooted with prejudice:
>
> app02# netstat -t -n -a |grep 5432 | grep 192.168.100.4 | wc -l
>    0

Have you waited long enough for the TCP timeout to kick in here (usually 1 
hour plus IIRC). PG should notice the disconnect after TCP tells it the 
connection is gone.

> The only way I can get them to go away
> is to bounce the DB server process, which kind of sucks because the
> whole point of having two app servers is to allow for one to fail.
> I've had this behaviour with both PostgreSQL and Oracle.

Which points to your application. What is it, and how does it manage its 
connections?
If it's closed source and misbehaving, you might end up putting a proxy 
between the DB and app server - then the proxy can kill connections properly 
if one of the app servers dies.

-- 
  Richard Huxton
  Archonet Ltd
-- 
Gllug mailing list  -  Gllug at gllug.org.uk
http://lists.gllug.org.uk/mailman/listinfo/gllug