Re: dangling connections

On Mon, May 14, 2012 at 8:58 PM, Daryl Stultz
<daryl(dot)stultz(at)opentempo(dot)com> wrote:
> Hello,
>
> I have a tomcat server with 7 applications connecting to PG 8.4. All apps
> use connection pooling. Over the weekend the apps ran out of connections.
> The max connections is at the default of 100 which is more than enough for
> normal operations. In attempting to resolve the situation I shutdown tomcat
> only to find that PG still appeared to have connections out. So if a

Looks like the problem is on apps side from the first glance.

Try to do

select client_addr, client_port from pg_stat_activity;

after shutting down the Tomcat and find out what holds the <client_port> on
the <client_addr>

[user@<client_addr> ~]$ sudo netstat -pnao | grep <client_port>

The second column from the right will be the process pid.

Also I would suggest to update PG to the latest version before making
further investigation, if it is possible of course.

> connection is between A and B, the A side (tomcat) was shut down, but the B
> side still thinks it's connected. I had to restart PG to get the connections
> to clean up.
>
> I've had a similar situation happen where the database server was severely
> overloaded. Many long-running queries were started. The application
> connection pools closed the connections after they had been out too long (60
> seconds). Eventually things caught up where CPU usage of the database server
> returned to normal but there remained several backends that did not appear
> to be connected anymore (a "select from pg_stat_activity" query in
> combination with "top" showed this). The situation this weekend does not
> appear to have been triggered by a heavy load.
>
> My question is this: how is it possible for PG to get into a state where it
> thinks it is connected to an application but the application doesn't agree?
> (Since I was unable to connect due to max clients reached, I was not able to
> kill the backends using psql). I'm looking for any avenue that I might
> explore.
>
> Thanks.
>
> --
> Daryl Stultz
> _____________________________________
> 6 Degrees Software and Consulting, Inc.
> http://www.6degrees.com
> http://www.opentempo.com
> mailto:daryl(dot)stultz(at)opentempo(dot)com

--
Sergey Konoplev

a database and software architect
http://www.linkedin.com/in/grayhemp

Jabber: gray(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +79160686204