Thread: dangling connections
Hello,
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com
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 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.stultz@opentempo.com
On Mon, May 14, 2012 at 8:58 PM, Daryl Stultz <daryl.stultz@opentempo.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.stultz@opentempo.com -- Sergey Konoplev a database and software architect http://www.linkedin.com/in/grayhemp Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204
On Tue, May 15, 2012 at 3:31 AM, Sergey Konoplev <gray.ru@gmail.com> wrote:
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.
Thanks, I currently believe the client side does not hold the connection, but I'll file this away for the next time it happens and I should have a better picture.
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com
On Tue, May 15, 2012 at 3:31 AM, Sergey Konoplev <gray.ru@gmail.com> wrote: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.
My system is running fine at present, but I thought I'd just mess with the above. Oddly, it seems Tomcat is holding 56 connections while PG is holding only 51.
netstat -pnao | grep 5432 | sort | head
Tomcat server
tcp 0 0 ::ffff:172.16.103.122:33440 ::ffff:172.16.101.157:5432 ESTABLISHED 14423/java off (0.00/0/0)
tcp 0 0 ::ffff:172.16.103.122:33441 ::ffff:172.16.101.157:5432 ESTABLISHED 14423/java off (0.00/0/0)
tcp 0 0 ::ffff:172.16.103.122:33442 ::ffff:172.16.101.157:5432 ESTABLISHED 14423/java off (0.00/0/0)
tcp 0 0 ::ffff:172.16.103.122:33443 ::ffff:172.16.101.157:5432 ESTABLISHED 14423/java off (0.00/0/0)
tcp 0 0 ::ffff:172.16.103.122:33444 ::ffff:172.16.101.157:5432 ESTABLISHED 14423/java off (0.00/0/0)
tcp 0 0 ::ffff:172.16.103.122:33775 ::ffff:172.16.101.157:5432 ESTABLISHED 14423/java off (0.00/0/0)
tcp 0 0 ::ffff:172.16.103.122:33776 ::ffff:172.16.101.157:5432 ESTABLISHED 14423/java off (0.00/0/0)
tcp 0 0 ::ffff:172.16.103.122:35117 ::ffff:172.16.101.157:5432 ESTABLISHED 14423/java off (0.00/0/0)
tcp 0 0 ::ffff:172.16.103.122:35118 ::ffff:172.16.101.157:5432 ESTABLISHED 14423/java off (0.00/0/0)
tcp 0 0 ::ffff:172.16.103.122:35119 ::ffff:172.16.101.157:5432 ESTABLISHED 14423/java off (0.00/0/0)
Postgresql server
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 5666/postmaster off (0.00/0/0)
tcp 0 0 172.16.101.157:5432 172.16.103.122:33775 ESTABLISHED 17410/postgres: otr keepalive (4815.62/0/0)
tcp 0 0 172.16.101.157:5432 172.16.103.122:33776 ESTABLISHED 17411/postgres: otr keepalive (4815.63/0/0)
tcp 0 0 172.16.101.157:5432 172.16.103.122:35117 ESTABLISHED 16921/postgres: pos keepalive (2277.28/0/0)
tcp 0 0 172.16.101.157:5432 172.16.103.122:35118 ESTABLISHED 16922/postgres: pos keepalive (2277.29/0/0)
tcp 0 0 172.16.101.157:5432 172.16.103.122:35119 ESTABLISHED 16923/postgres: otr keepalive (2280.08/0/0)
tcp 0 0 172.16.101.157:5432 172.16.103.122:35120 ESTABLISHED 16926/postgres: pos keepalive (2290.73/0/0)
tcp 0 0 172.16.101.157:5432 172.16.103.122:35121 ESTABLISHED 16927/postgres: pos keepalive (2290.73/0/0)
tcp 0 0 172.16.101.157:5432 172.16.103.122:35122 ESTABLISHED 16928/postgres: pos keepalive (2290.74/0/0)
tcp 0 0 172.16.101.157:5432 172.16.103.122:35123 ESTABLISHED 16929/postgres: pos keepalive (2290.74/0/0)
Note the bolded ports missing from the PG side. This is the opposite of the condition I originally presented. Anyone have an explanation for this?
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com
On Wed, May 16, 2012 at 7:38 AM, Daryl Stultz <daryl.stultz@opentempo.com> wrote:
On Tue, May 15, 2012 at 3:31 AM, Sergey Konoplev <gray.ru@gmail.com> wrote:select client_addr, client_port from pg_stat_activity;
My system failed again last night. After shutting down Tomcat netstat shows the machine having a dozen or so connections in FIN_WAIT1 state. I guess this just means they are in the process of shutting down. I didn't check again later to see if they went away.
With tomcat shut down, I ran the suggested query against PG. I also ran netstat on the database server. Both showed active connections (ESTABLISHED in the case of netstat) with the app server. Netstat on the app server shows no connections for the ports listed by the DB server. Checking on things the next morning I find that the app server has connections to the database server that the database server doesn't think it has open.
Anyone have any ideas?
Thanks.
/Daryl
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com