Thread: Problem with leaking connections

Problem with leaking connections

From
"Mark Lange"
Date:

Hi,

 

we have an webapplication running on tomcat6  with hibernate.

We are using the apache DBCP connection pool (tomcat built in) and an postgres 8.19 database server on an different host.

Every few days  the pool is  running out of connections.

The pool is configured to aggressively close connections when it is exhausted, but the application gets very slow when this happens, mostly we have to restart

the tomcat server.

#ps –aux | grep postgres on the database host shows me many open connections in the select or in transaction state (that never get closed until tomcat or postgres restart).

Is there a way to figure out which statement didn’t closed the connections?

 

In the application we found a few places where the connections not closed and fixed them.

But they are hard to find.

 

Is it possible to configure postgres to close connections after a timeout or something like this (maybe statement_timeout)?

Are there any other possibilities?

 

Thanks

Mark

 

 

Re: Problem with leaking connections

From
"Joshua D. Drake"
Date:
On Thu, 2009-09-03 at 17:55 +0200, Mark Lange wrote:
> Hi,

> The pool is configured to aggressively close connections when it is
> exhausted, but the application gets very slow when this happens,
> mostly we have to restart
>
> the tomcat server.
>
> #ps –aux | grep postgres on the database host shows me many open
> connections in the select or in transaction state (that never get
> closed until tomcat or postgres restart).
>
> Is there a way to figure out which statement didn’t closed the
> connections?
>
It isn't a statement it is your app code. If you have a ps aux that is
showing select it means you have selects running that aren't finished.
If it shows in transaction state (I assume idle in transaction) it also
means your app code is not properly committing or rollingback
transactions.

> Is it possible to configure postgres to close connections after a
> timeout or something like this (maybe statement_timeout)?
>

statement_timeout will terminate a query not a connection.


> Are there any other possibilities?
>

Fix your code. Not to sound harsh but this is blatant code issues.

Joshua D. Drake

>
>
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering

Re: Problem with leaking connections

From
Andy Colson
Date:
Mark Lange wrote:
> Hi,
>
>
>
> we have an webapplication running on tomcat6  with hibernate.
>
> We are using the apache DBCP connection pool (tomcat built in) and an
> postgres 8.19 database server on an different host.
>
> Every few days  the pool is  running out of connections.
>
> The pool is configured to aggressively close connections when it is
> exhausted, but the application gets very slow when this happens, mostly
> we have to restart
>
> the tomcat server.
>
> #ps –aux | grep postgres on the database host shows me many open
> connections in the select or in transaction state (that never get closed
> until tomcat or postgres restart).
>
> Is there a way to figure out which statement didn’t closed the connections?
>
>
>
> In the application we found a few places where the connections not
> closed and fixed them.
>
> But they are hard to find.
>
>
>
> Is it possible to configure postgres to close connections after a
> timeout or something like this (maybe statement_timeout)?
>
> Are there any other possibilities?
>
>
>
> Thanks
>
> Mark
>


I'm gonna guess you are not leaking connections.  That, in itself, would not make pg slow.  It would cause errors when
youhit the max_connections setting though. 


> #ps –aux | grep postgres on the database host shows me many open
> connections in the select or in transaction state

If you mean you see "idle in transaction", then that's what's causing the slow down.  And you really don't want to time
outor force close them, because the transaction would be rolled back. 

I think you're only option is to fix the code.  You really need to commit transactions.

If, on the other hand, the ps -aux shows you many that are "idle", then that's what you want.  The connection pooler is
supposedto keep open connections.  (and having a pooler "aggressively close" seems counter productive... why even
botherwith it then?) 

-Andy

Re: Problem with leaking connections

From
"Joshua D. Drake"
Date:
On Thu, 2009-09-03 at 17:55 +0200, Mark Lange wrote:
> Hi,

> The pool is configured to aggressively close connections when it is
> exhausted, but the application gets very slow when this happens,
> mostly we have to restart
>
> the tomcat server.
>
> #ps –aux | grep postgres on the database host shows me many open
> connections in the select or in transaction state (that never get
> closed until tomcat or postgres restart).
>
> Is there a way to figure out which statement didn’t closed the
> connections?
>
It isn't a statement it is your app code. If you have a ps aux that is
showing select it means you have selects running that aren't finished.
If it shows in transaction state (I assume idle in transaction) it also
means your app code is not properly committing or rollingback
transactions.

> Is it possible to configure postgres to close connections after a
> timeout or something like this (maybe statement_timeout)?
>

statement_timeout will terminate a query not a connection.


> Are there any other possibilities?
>

Fix your code. Not to sound harsh but this is blatant code issues.

Joshua D. Drake

>
>
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering