Обсуждение: Problem with leaking connections
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
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
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
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