[Gllug] DB connection / netstat wierdness

[Daniel P. Berrange]

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.


db01# netstat -t -n -a |grep 5432 | grep 192.168.100.2 | wc -l
   150
db01# netstat -t -n -a |grep 5432 | grep 192.168.100.3 | wc -l
   150

So the DB thinks there are 150 connections from each server.

If i look at the first app servers which is still running:

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

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

And yet the DB server still thinks its got the connections from
192.168.100.3.  They're not even in the CLOSE_WAIT state - netstat
lists them as ESTABLISHED. And the database server also lists them
has 'IDLE in transaction'. 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.

Any ideas on what on earth is going on here  & how to fix it without
bouncing the DB each time it happens ?

Dan.
-- 
|=-               http://www.berrange.com/~dan/gpgkey.txt             -=|
|=-   berrange at redhat.com  -  Daniel Berrange  -  dan at berrange.com    -=|
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 240 bytes
Desc: not available
URL: <http://mailman.lug.org.uk/pipermail/gllug/attachments/20040203/b0bd7ceb/attachment.pgp>
-------------- next part --------------
-- 
Gllug mailing list  -  Gllug at gllug.org.uk
http://lists.gllug.org.uk/mailman/listinfo/gllug