BoneCP/Postgres connections leak

5,998 views
Skip to first unread message

scott clasen

unread,
Sep 26, 2012, 2:54:33 PM9/26/12
to play-fr...@googlegroups.com
So Im seeing what appears to be connections leaking from boneCP.  

In the app in question, there are 4 partitions, min 1/max 3 connection per partition, so 12 connections max per process. 2 processes are running so max 24 connections.

When I monitor the processes via codahale metrics and the BoneCP JMX beans, unsurprisingly the TotalCreatedConnections never goes above 12.

When I look in pg_stat_activity in postgres it tells me there are currently 38 connections, which is after the app being up for only an hour or 2. 
After a day there are over 100 open connections. 
If I restart the app, the count in postgres immediately drops to 0
All database access is in DB.withConnection{c=>} or DB.withTransaction{c=>}

Here is the config used.

db.default.url=${DATABASE_URL}
db.default.driver=org.postgresql.Driver
db.default.partitionCount=4
db.default.idleMaxAge=1 minute
db.default.idleConnectionTestPeriod=30 seconds
db.default.logStatements=true
db.default.maxConnectionAge=15 minutes
db.default.maxConnectionsPerPartition=3
db.default.minConnectionsPerPartition=1
db.default.disableJMX=false

Any config that I am missing to make connections get GCed appropriately?  My guess is that the conns are sitting in the ReferenceQueue and its not getting processed.


scott clasen

unread,
Sep 26, 2012, 3:44:54 PM9/26/12
to play-fr...@googlegroups.com
One question to add. Does BoneCP rely on (major) JVM CG to collect the old connections? I have the app tuned so that it (almost) never does a major GC. Could this be the cause?

Guillaume Bort

unread,
Sep 26, 2012, 3:45:49 PM9/26/12
to play-fr...@googlegroups.com
Hum, I don't think there is a connection leak problem. I mean we are
using it on many production application at zenexity without any
problem (we are using mainly mysql but I don't think it is related).

Are you sure that there is no place in the application where a
connection is not properly closed?
> --
> You received this message because you are subscribed to the Google Groups
> "play-framework" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/play-framework/-/qYhPpgLmb6EJ.
> To post to this group, send email to play-fr...@googlegroups.com.
> To unsubscribe from this group, send email to
> play-framewor...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/play-framework?hl=en.



--
Guillaume Bort, http://guillaume.bort.fr

Justin Lee

unread,
Sep 26, 2012, 3:49:48 PM9/26/12
to play-fr...@googlegroups.com
if you're relying on GC to close your connections, you're doing it wrong.

--
You received this message because you are subscribed to the Google Groups "play-framework" group.
To view this discussion on the web visit https://groups.google.com/d/msg/play-framework/-/8XDBAiax3rkJ.

To post to this group, send email to play-fr...@googlegroups.com.
To unsubscribe from this group, send email to play-framewor...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/play-framework?hl=en.



--

scott clasen

unread,
Sep 26, 2012, 4:19:15 PM9/26/12
to play-fr...@googlegroups.com
If I do a "Find Usages" of play.api.db.DB in the project, they are all DB.withConnection or DB.withTransaction

any pointers on further diagnosis?

scott clasen

unread,
Sep 26, 2012, 5:07:44 PM9/26/12
to play-fr...@googlegroups.com
Guillaume-

 One observation.  if it was me leaking connections wouldnt BoneCp stop giving out new connections and have the app fail?

the app continues along just fine so it really seems like a bonecp thing....

Thanks
SC


On Wednesday, September 26, 2012 12:46:25 PM UTC-7, Guillaume Bort wrote:

scott clasen

unread,
Sep 26, 2012, 8:33:24 PM9/26/12
to play-fr...@googlegroups.com

James Roper

unread,
Sep 26, 2012, 8:42:30 PM9/26/12
to play-fr...@googlegroups.com
So this is in production, not dev right?

scott clasen

unread,
Sep 26, 2012, 8:43:56 PM9/26/12
to play-fr...@googlegroups.com
Ok have pretty much verified this. It is GC/BoneCP related.

Run my process with the config from above. Sampling with VisualVM

I believe the problem is related to  

       db.default.maxConnectionAge=15 minutes

During the first 15 min the number of com.jolbox.bonecp.ConnectionHandle instances is 13.  My guess is this is 12 from my pool plus one from the evolution check at startup.

After 15 min, the number starts to grow, and increases every 15 minutes.

Run a GC from VisualVM, and aha the number of com.jolbox.bonecp.ConnectionHandle instances is 12.

Wait 15 min and it grows again.

HYPOTHESIS: If you have tuned your app such that major GC is an almost never occurring event, dont use connection Max Age or you will start to leak connections. (Until the rare Major GC)


 







On Wednesday, September 26, 2012 2:07:44 PM UTC-7, scott clasen wrote:

scott clasen

unread,
Sep 26, 2012, 8:44:21 PM9/26/12
to play-fr...@googlegroups.com
Yes Production

scott clasen

unread,
Sep 26, 2012, 8:47:23 PM9/26/12
to play-fr...@googlegroups.com
Also just for completeness, postgres agrees with the connection numbers I am seeing here both pre and post triggering GC

scott clasen

unread,
Sep 26, 2012, 9:10:35 PM9/26/12
to play-fr...@googlegroups.com
Aslo the default play setting  for this, 1 hour, will cause connection leaks if you dont major GC at least once per hour.

scott clasen

unread,
Sep 26, 2012, 9:16:27 PM9/26/12
to play-fr...@googlegroups.com
boneCP appears to be doing just this

James Roper

unread,
Sep 26, 2012, 10:01:44 PM9/26/12
to play-fr...@googlegroups.com
Great find.  Good news is, the bug has already been reported and fixed in bonecp, about a month ago:


Now we just need a release of bonecp to fix this...  For now setting db.default.maxConnectionAge=0 is a suitable work around.
Message has been deleted

Laurent Marchal

unread,
Sep 27, 2012, 4:23:30 PM9/27/12
to play-fr...@googlegroups.com
I'm glad you guys found the problem. I have been experiencing this problem for quite a while now and I could not get any support from the BoneCP community (it seems pretty dead looking at the forums)

Here's my previous post about this problem:

In the BoneCP  forum it has been talked about setting the releaseHelperThreads parameter to 0 to avoid a bug with leaking connections, but unfortunately this parameter is not available through Play (a bug has been filed) :


Thanks
Laurent.

Kevin Bowling

unread,
Nov 25, 2012, 10:14:55 PM11/25/12
to play-fr...@googlegroups.com
I'm still seeing this in Play 2.1-RC1 although it seems BoneCP is at version 0.7.1?  For me it seems to be related to running in dev mode and having some queries that run in Async tasks?

Regards,
Kevin

Ivan Meredith

unread,
Nov 26, 2012, 2:51:45 AM11/26/12
to play-fr...@googlegroups.com
Hi Kevin, I have made https://github.com/hadashi/play2-c3p0-plugin
which ruled out bonecp for me, but give it a try.

I also had problem with Async tasks in 2.1, I moved db access out fo
async and all was fine. Btw it was production for me.
> --
>
>

Ivan Meredith

unread,
Nov 26, 2012, 2:55:51 AM11/26/12
to play-fr...@googlegroups.com
Btw what ExecutionContext are you using? the scala one or the play one.

Kevin Bowling

unread,
Nov 27, 2012, 1:50:25 AM11/27/12
to play-fr...@googlegroups.com
Maybe similar symptom but unrelated..

import play.api.libs.concurrent.Execution.Implicits._

val task = Akka.system.scheduler.schedule(10 seconds, 360 minutes) {
...calls a Model.action() using Squeryl here.

Regards,
Kevin

Ivan Meredith

unread,
Nov 27, 2012, 4:10:34 AM11/27/12
to play-fr...@googlegroups.com
Actaully very related.

Thomas Felix

unread,
Dec 4, 2012, 3:18:35 AM12/4/12
to play-fr...@googlegroups.com
Since the beginning my website suffers from this symptom too. Every now and then (sometimes after weeks of running) I get this exception that no free connection is aviable. (currently I have no access to my server to provide the stack trace). I posted here before but got no reply. :/. Just today my webapp crashed again.

So a maybe stupid question but how to I close a Database connection? I am accessing my model like in the examples e.g.:
// Delete a task by ID
Task.find.ref(34L).delete();

is a manual closing needed in this case? and if yes how is it done?
What is the trick if I do a DB query in a async task?

Greetings,
Thomas
Message has been deleted

Erik Bakker

unread,
Feb 8, 2013, 5:06:27 AM2/8/13
to play-fr...@googlegroups.com
This is not yet fixed in BoneCP 0.7.1.

Laurent

unread,
Feb 8, 2013, 10:06:56 AM2/8/13
to play-fr...@googlegroups.com
They released v0.8.0-rc1 recently and it should be fixed in this version. That'd be cool if play was upgraded to this version.

Laurent.
--
You received this message because you are subscribed to the Google Groups "play-framework" group.
To unsubscribe from this group and stop receiving emails from it, send an email to play-framewor...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

Luis Ángel Vicente Sánchez

unread,
Feb 9, 2013, 4:40:42 AM2/9/13
to play-fr...@googlegroups.com

You can add the dependency to your project and sbt will use that instead of the default one.

Joao Caxaria

unread,
Feb 10, 2013, 5:49:28 AM2/10/13
to play-fr...@googlegroups.com
As a reference, we were having the same issue. We did this and it appears (after 24h) to have fixed it.

we added this to the appDependencies:
    "com.google.guava" % "guava" % "14.0-rc1",
    "com.jolbox" % "bonecp" % "0.8.0-rc2-SNAPSHOT",

and this to the resolvers:
      resolvers += "Sonatype OSS Snapshots" at "https://oss.sonatype.org/content/repositories/snapshots"

Hope it helps.

Laurent

unread,
Feb 11, 2013, 12:35:07 PM2/11/13
to play-fr...@googlegroups.com
Yeah that helps, thank for the suggestions guys.

Laurent.

Thomas Koeppen

unread,
Feb 13, 2013, 11:56:57 AM2/13/13
to play-fr...@googlegroups.com
thanks Joao.

we had exactly the same behaviour.

We have a B2B app, Play 2.1, BoneCP (pool config, min. 2x5, max. 2x15).
During the week everything was fine, max 10 or 15 connections were used.
But at the weekend (when we had only a few users and additional a running scheduled task which access the db pool each 10 minutes) we saw the behaviour that pool shrinking did not work anymore.
First we thought it was our own code that we haven't released any transaction, but then we came across this BoneCP issue [1].

We run now on testing with bonecp 0.8.0-rc2-SNAPSHOT and pool shrinking works as expected.

Best regards,
Thomas

[1] https://bugs.launchpad.net/bonecp/+bug/999114
[2] our connection pool usage that shows our pool shrinking problem, ever growing pool size before switching to bonecp 0.8.0-rc2-SNAPSHOT
postgres_connections_ALL-day_20130202_2042.png

Zsolt Szász

unread,
Feb 22, 2013, 2:25:01 PM2/22/13
to play-fr...@googlegroups.com
We've experienced the same connection leak issue with the stock BoneCP 0.7.1. We've upgraded to 0.8.0-rc2 and the leak is gone, but there are a lot of "com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed." exceptions in the log, which haven't occurred with 0.7.1.

0.8.0-rc2 users, would you mind posting your BoneCP configuration? We have:

db.default.partitionCount=1
db.default.maxConnectionsPerPartition=24
db.default.minConnectionsPerPartition=8
db.default.acquireIncrement=2
db.default.acquireRetryAttempts=10
db.default.acquireRetryDelay=5 seconds
db.default.connectionTimeout=5 second
db.default.idleMaxAge=5 minutes
db.default.idleConnectionTestPeriod=1 minute
db.default.initSQL="SELECT 1"
db.default.logStatements=false
db.default.disableJMX=false
db.default.maxConnectionAge=1 hour

Marco

unread,
Mar 15, 2013, 9:51:52 AM3/15/13
to play-fr...@googlegroups.com
Hi, i use a mysql database on Heroku and i had the same problem.
After a lot of debug i see that the connections in the pool after about 40-50 seconds in idle have same problems and then the next ping to keep the connection alive raise the exception.
I try with BoneCP 0.7.2-SNAPSHOT and 0.8.1-rc1  that supports the idle test period in seconds and with 30-35 seconds of idleConnectionTestPeriod the connection pool works fine.
My configuration is:

db.default.idleMaxAge=10 minutes
db.default.idleConnectionTestPeriod=35 seconds
db.default.connectionTimeout=20 second
db.default.connectionTestStatement="SELECT 1"
db.default.maxConnectionAge=30 minutes

Mariusz Zieliński

unread,
Mar 17, 2013, 7:06:48 PM3/17/13
to play-fr...@googlegroups.com
Hey, I've updated to bonecp-0.8.1-rc2 and now instead of having problem with connection pool I have now problem with killing connections...
Exact error I get is:

"[ERROR] - from com.jolbox.bonecp.ConnectionHandle in BoneCP-keep-alive-scheduler
Database access problem. Killing off this connection and all remaining connections in the connection pool. SQL State = 57P01"

Does any have any clues how to deal with it? I'm currently trying configuration as posted above:

db.default.idleMaxAge=10 minutes
db.default.idleConnectionTestPeriod=35 seconds
db.default.connectionTimeout=20 second
db.default.connectionTestStatement="SELECT 1"
db.default.maxConnectionAge=30 minutes

But it doesn't help. 

Thanks in advance :)

Ivan Meredith

unread,
Mar 17, 2013, 11:31:42 PM3/17/13
to play-fr...@googlegroups.com
I've been using c3p0 with success.

https://github.com/hadashi/play2-c3p0-plugin

A lot of people seem to use BoneCP with success, but I just wonder if
there is an issue when you have a small max connections.

Mariusz Zieliński

unread,
Mar 18, 2013, 4:59:47 AM3/18/13
to play-fr...@googlegroups.com
Thanks Ivan, I'll try it tonight. Seems it might be the best option as I see a lot of people using Postgres seem to be having problems with bonecp and that's not only with play.

Ivan Meredith

unread,
Mar 18, 2013, 5:23:00 AM3/18/13
to play-fr...@googlegroups.com
Its a rough cut, with a hard coded pool size, just clone the repo if
you want to change anything. And do a PR if you feel like making it
better.

somatik

unread,
Sep 26, 2013, 4:28:41 PM9/26/13
to play-fr...@googlegroups.com
bonecp 0.8.0-rc3 is now available in maven central, let's see if that helps to get rid of these "PSQLException: This connection has been closed"

Andrew Gaydenko

unread,
Sep 26, 2013, 4:54:59 PM9/26/13
to play-fr...@googlegroups.com
Why bonecp when pg has own pool in jdbc driver?

    val ds = new org.postgresql.ds.PGPoolingDataSource
    ds.setServerName(Dx.hostCfg(...)
    ds.setDatabaseName(...)
    ds.setUser(...)
    ds.setPassword(...)
    ds.setMaxConnections(math.min(Runtime.getRuntime.availableProcessors, 10))
    Database.forDataSource(ds)   

Lev Tverdokhlebov

unread,
Dec 5, 2013, 1:23:02 PM12/5/13
to play-fr...@googlegroups.com
I've added "com.jolbox" % "bonecp" % "0.8.0-SNAPSHOT" to the first line of appDependencies.

But play keeps downloading 0.8.0-rc1.

It seems it resolves both successfully, because in repository\cache\com.jolbox\bonecp I have files for both versions:
ivy-0.8.0-rc1.xml
ivy-0.8.0-rc1.xml.original
ivy-0.8.0-SNAPSHOT.xml
ivy-0.8.0-SNAPSHOT.xml.original
ivydata-0.8.0-rc1.properties
ivydata-0.8.0-SNAPSHOT.properties

but play downloads only 0.8.0-rc1 version:
[info] [SUCCESSFUL ] com.jolbox#bonecp;0.8.0-rc1!bonecp.jar(bundle) (383ms)
[info] Done updating.

and in repository\cache\com.jolbox\bonecp\bundles folder I have only one jar: bonecp-0.8.0-rc1.jar

How to make in download 0.8.0-SNAPSHOT version. Please help!

On Sunday, February 10, 2013 11:49:28 AM UTC+1, Joao Caxaria wrote:

virtualeyes

unread,
Dec 5, 2013, 1:35:06 PM12/5/13
to play-fr...@googlegroups.com
This was a bit of a sticky wicket; I tried to exclude Play's dep on BoneCp:
"com.typesafe.play" %% "play-jdbc" % "2.2.0" exclude("com.jolbox", "bonecp")

but then I couldn't get the bonecp snapshot dep pulled in due to a maven versioning issue (this was as of a couple of weeks ago)

The easiest thing to do is add above config line, grab the snanpshot jar and throw it in your lib managed directory.

Lev Tverdokhlebov

unread,
Dec 5, 2013, 3:39:20 PM12/5/13
to play-fr...@googlegroups.com
Thx for lightning response!

After moving to 0.8.0-SNAPSHOT I have an error happening from time to time on different queries:

play.api.Application$$anon$1: Execution exception[[PersistenceException: Query threw SQLException:An I/O error occured while sending to the backend.
Bind values:[3]
Query was:
select t0.id as c0, t0.tag as c1, t0.region_id as c2 from country t0 where t0.id = ?  


]]
 at play
.api.Application$class.handleError(Application.scala:293) ~[play_2.10.jar:2.2.0]
 at play
.api.DefaultApplication.handleError(Application.scala:399) ~[play_2.10.jar:2.2.0]
 at play
.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$2$$anonfun$applyOrElse$3.apply(PlayDefaultUpstreamHandler.scala:261) ~[play_2.10.jar:2.2.0]
 at play
.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$2$$anonfun$applyOrElse$3.apply(PlayDefaultUpstreamHandler.scala:261) ~[play_2.10.jar:2.2.0]
 at scala
.Option.map(Option.scala:145) ~[scala-library-2.10.1.jar:na]
 at play
.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$2.applyOrElse(PlayDefaultUpstreamHandler.scala:261) ~[play_2.10.jar:2.2.0]
Caused by: javax.persistence.PersistenceException: Query threw SQLException:An I/O error occured while sending to the backend.
Bind values:[3]
Query was:
select t0.id as c0, t0.tag as c1, t0.region_id as c2 from country t0 where t0.id = ?  




 at com
.avaje.ebeaninternal.server.query.CQuery.createPersistenceException(CQuery.java:790) ~[avaje-ebeanorm.jar:na]
 at com
.avaje.ebeaninternal.server.query.CQuery.createPersistenceException(CQuery.java:767) ~[avaje-ebeanorm.jar:na]
 at com
.avaje.ebeaninternal.server.query.CQueryEngine.findMany(CQueryEngine.java:202) ~[avaje-ebeanorm.jar:na]
 at com
.avaje.ebeaninternal.server.query.DefaultOrmQueryEngine.findMany(DefaultOrmQueryEngine.java:77) ~[avaje-ebeanorm.jar:na]
 at com
.avaje.ebeaninternal.server.core.OrmQueryRequest.findList(OrmQueryRequest.java:265) ~[avaje-ebeanorm.jar:na]
 at com
.avaje.ebeaninternal.server.core.DefaultServer.findList(DefaultServer.java:1460) ~[avaje-ebeanorm.jar:na]
Caused by: org.postgresql.util.PSQLException: An I/O error occured while sending to the backend.
 at org
.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:283) ~[postgresql-8.4-703.jdbc4.jar:na]
 at org
.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:510) ~[postgresql-8.4-703.jdbc4.jar:na]
 at org
.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:386) ~[postgresql-8.4-703.jdbc4.jar:na]
 at org
.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271) ~[postgresql-8.4-703.jdbc4.jar:na]
 at com
.jolbox.bonecp.PreparedStatementHandle.executeQuery(PreparedStatementHandle.java:174) ~[bonecp-0.8.0-SNAPSHOT.jar:0.8.0-SNAPSHOT]
 at com
.avaje.ebeaninternal.server.query.CQuery.prepareBindExecuteQuery(CQuery.java:377) ~[avaje-ebeanorm.jar:na]
Caused by: java.io.IOException: Stream closed
 at sun
.nio.cs.StreamEncoder.ensureOpen(StreamEncoder.java:45) ~[na:1.7.0_25]
 at sun
.nio.cs.StreamEncoder.flush(StreamEncoder.java:140) ~[na:1.7.0_25]
 at java
.io.OutputStreamWriter.flush(OutputStreamWriter.java:229) ~[na:1.7.0_25]
 at org
.postgresql.core.PGStream.flush(PGStream.java:522) ~[postgresql-8.4-703.jdbc4.jar:na]
 at org
.postgresql.core.v3.QueryExecutorImpl.sendSync(QueryExecutorImpl.java:1136) ~[postgresql-8.4-703.jdbc4.jar:na]
 at org
.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:256) ~[postgresql-8.4-703.jdbc4.jar:na]

It happens unpredictably on different queries without any logic.

Any ideas why?

Francis De Brabandere

unread,
Dec 5, 2013, 4:12:44 PM12/5/13
to play-framework
You should not be using the 0.8.0-SNAPSHOT any more as 0.8.0.RELEASE
has been released and is normally pulled in by play 2.2.1

Cheers,
Francis
> You received this message because you are subscribed to a topic in the
> Google Groups "play-framework" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/play-framework/RM1QwOFz-2A/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
Message has been deleted

Francis De Brabandere

unread,
Dec 5, 2013, 6:55:53 PM12/5/13
to play-framework
There also is a bug in bonecp 0.8.0.RELEASE which causes the actual
SQLException to be swallowed and all connections to be killed:
https://bugs.launchpad.net/bonecp/+bug/1258339

Cheers,
Francis

virtualeyes

unread,
Dec 6, 2013, 2:40:02 AM12/6/13
to play-fr...@googlegroups.com
I missed that, it's actually 0.8.1-snapshot that I am using -- BoneCP issued this, the latest version, to fix another issue with prepared statement caching.

YMMV, 0.8.0-release may be just fine, but I'd like prepared statement caching to, you know, work all of the time ;-)
Reply all
Reply to author
Forward
0 new messages