6

I have an app on heroku that uses play. It was working fine for the longest time, but somewhat recently I started getting this:

Caused by: java.sql.SQLException: Timed out waiting for a free available connection.
at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:169) ~[hibernate-core-4.1.9.Final.jar:4.1.9.Final]
at com.jolbox.bonecp.BoneCP.getConnection(BoneCP.java:503) ~[bonecp-0.7.1.RELEASE.jar:0.7.1.RELEASE]

which is caused by 

org.postgresql.util.PSQLException: FATAL: too many connections for role "ejmatdbwywaugk"

Now this is pretty obviously a connection leak, except that I'm using JPA.em(). The Play examples never close an entity manager obtained like this. I tried closing it, but then the app blows up saying the entity manager is closed.

Any ideas?

radu--
  • 108
  • 1
  • 6

4 Answers4

8

Try with BoneCP 0.8.0-rc1 and use this configuration:

db.default.idleMaxAge=10 minutes
db.default.idleConnectionTestPeriod=30 seconds
db.default.connectionTimeout=20 second
db.default.connectionTestStatement="SELECT 1"
db.default.maxConnectionAge=30 minutes
Marco
  • 1,494
  • 13
  • 23
  • This has gone for two days now without exhibiting the problem. I believe it is indeed the problem with the 0.7.1 version of BoneCp. For future reference, this is what you should have in your Build.scala file under appDependencies: "com.jolbox" % "bonecp" % "0.8.0-rc1" – radu-- Mar 21 '13 at 14:27
  • 2
    I don't know if is a issue of BoneCP but i saw the problems started after about 40 second of idle connection...if you set idleConnectionTestPeriod to 40 seconds i think the problem return. – Marco Mar 21 '13 at 14:53
  • Thank you. This seems to work for me. It should be documented somewhere. – yzernik Feb 03 '14 at 01:23
2

There is a defect in how BoneCP/Heroku/Play deal with connections. I made a fix to BoneCP on github under:

https://github.com/wwadge/bonecp/pull/10

Also, the compiled library is available by adding the following to Build.scala:

val appDependencies = Seq(
"com.jolbox" % "bonecp" % "0.8.0-rc2-SNAPSHOT-20130712-14382677.jar",
....

val main = PlayProject(appName, appVersion, appDependencies, mainLang = JAVA).settings(
  libraryDependencies += "com.jolbox" % "bonecp" % "0.8.0-rc2-SNAPSHOT-20130712-14382677.jar" from "https://dl.dropboxusercontent.com/u/36714110/libraries/bonecp-patches/bonecp-0.8.0-rc2-SNAPSHOT-20130712-14382677.jar"
,resolvers += ...
Abbas Valliani
  • 81
  • 1
  • 2
0

There is a known issue with connection leaks for version 0.7.1 of BoneCP (which is currently the stable release) - Bug 999114. Switching to a later version as mentioned by @MaFo should fix it. There is also a workaround mentioned in BoneCP/Postgres connections leak

In the end I switched to another connection pool Tomcat JDBC Connection Pool on a project I was working on as I hope it is (will be) better supported as it part of the apache project.

Mark Sivill
  • 825
  • 1
  • 9
  • 18
-1

I had the same issue using a remote postgres instance. I added the following to my application.conf db setup:

db.default.partitionCount=1
db.default.maxConnectionsPerPartition=5
db.default.minConnectionsPerPartition=5

That did the trick for me. I am using the org.postgresql.Driver driver.

ben
  • 11
  • 4