Skip site navigation (1) Skip section navigation (2)

dangling connections

From: Daryl Stultz <daryl(dot)stultz(at)opentempo(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: dangling connections
Date: 2012-05-14 16:58:47
Message-ID: CANYnOKQFCh1KVAQviiJKKGcYwZ7WFhTOOxPxV9He=Ckg6xYCiw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
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
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

Responses

pgsql-admin by date

Next:From: Benjamin KrajmalnikDate: 2012-05-14 17:02:30
Subject: CPU Load question / PgBouncer config
Previous:From: Elizandro GallegosDate: 2012-05-14 13:52:32
Subject: FW: pg_dump: schema with OID 2200 does not exist

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group