Re: Problem with leaking connections

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 you hit 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 out or 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 supposed to keep open connections. (and having a pooler "aggressively close" seems counter productive... why even bother with it then?)

-Andy