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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
