Connection pooling seems to be broken

[vhazrati]

We have this object from which we are fetching our queryEvaluator

object MyDataSource { val queryFactory = new SqlQueryFactory val apachePoolingDatabaseFactory = new ApachePoolingDatabaseFactory( "SELECT 1", 10, 10, new Duration(1000), new Duration(10), true, new Duration(30000)) val queryEvaluatorFactory = new StandardQueryEvaluatorFactory(apachePoolingDatabaseFactory, queryFactory) val queryEvaluator = queryEvaluatorFactory(dbConfig.driverClassName, dbConfig.url, dbConfig.username, dbConfig.password) }

We are not explicitly closing connections, assuming that it is handled. We seem to be running out of connections on our MySQL db.

We are using the following dependencies

libraryDependencies += "com.twitter" % "querulous" % "1.2.0-generic"

libraryDependencies += "mysql" % "mysql-connector-java" % "5.1.12"

[tsuna]

It seems that I have the same problem. I'm using an AsyncQueryEvaluator that's pooling connections with ApachePoolingDatabase to a cluster of slaves. When doing a loadtest, my Finagle server quickly ran into java.io.IOException: Too many open files and lsof was showing that virtually all FDs were open connections to my various MySQL nodes.

I put up my code @ https://gist.github.com/1344664

edit: I'm using "com.twitter" % "querulous" % "2.5.0" in my sbt file.

[vhazrati]

tsuna are you using scala 2.8.x or 2.9x. Did you recompile querulous 2.5.0 to work with 2.9.x?

[tsuna]

I'm using querulous-core-2.5.0.jar downloaded by SBT from Twitter's Maven repo, in conjunction with Scala 2.8.1, Finagle 1.9.5, mysql-connector-java 5.1.13, commons-pool-1.5.4.jar, and commons-dbcp-1.4.jar.

[tsuna]

Halp! :)

Am I doing something obviously wrong? Maybe I shouldn't be getting a new QueryEvaluator for each query?

This issue is a show stopper for me right now :(

[vhazrati]

Same here, i am using Scala 2.9.1 but querulous 2.5.0 does not seem to work with that and i am forced to use 1.2.0-generic. I have posted on stackoverflow as well but awaiting a response http://stackoverflow.com/questions/7962766/querulous-connection-pooling-does-not-seem-to-work

[vhazrati]

also i seem to be getting the same MyDataSource.queryEvaluator still the issue.

[tsuna]

I can still reproduce with v 2.5.1

I also run into this one:

java.lang.IllegalStateException: Timer already cancelled.
        at java.util.Timer.sched(Timer.java:354) 
        at java.util.Timer.schedule(Timer.java:184) 
        at com.twitter.util.JavaTimer.schedule(Timer.scala:121) 
        at com.twitter.util.Future$$anonfun$within$1.apply(Future.scala:265) 
        at com.twitter.util.Future$$anonfun$within$1.apply(Future.scala:264) 
        at com.twitter.util.Future$.makePromise(Future.scala:174) 
        at com.twitter.util.Future.within(Future.scala:264) 
        at com.twitter.querulous.async.BlockingDatabaseWrapper.checkoutConnection(BlockingDatabaseWrapper.scala:69) 
        at com.twitter.querulous.async.BlockingDatabaseWrapper.withConnection(BlockingDatabaseWrapper.scala:47) 
        at com.twitter.querulous.async.StandardAsyncQueryEvaluator.withTransaction(StandardAsyncQueryEvaluator.scala:77) 
        at com.twitter.querulous.async.StandardAsyncQueryEvaluator.selectOne(StandardAsyncQueryEvaluator.scala:35) 
        at com.twitter.querulous.async.AsyncQueryEvaluator$class.selectOne(AsyncQueryEvaluator.scala:100) 
        at com.twitter.querulous.async.StandardAsyncQueryEvaluator.selectOne(StandardAsyncQueryEvaluator.scala:28) 
        at my.package.MySQL.selectOne(mysql.scala:51) 

[tsuna]

I added some print statements in ApachePoolingDatabase in open() and close(), they're both called. According to the dbcp javadoc this is the right thing to do as close is supposed to return the connection to the pool.

I think either Querulous isn't using Apache's DBCP API properly, or there's a problem in Apache's code. All these layers of factories are giving me a nausea, I think I'll look at using another simpler connection pooling library or write my own.

[stevej]

This has been fixed, I believe, in the most recent versions. I ran into this issue a few weeks ago and fixed a race condition in FuturePool.

[tsuna]

Can you give us the link to the fix?

[stevej]

This fix made JavaTimer more resilient https://github.com/twitter/util/commit/253606a6cd8d3012bd9390075d9e344a3603fe72

This fixed a race condition in FuturePool https://github.com/twitter/util/commit/7d6898afde66bcb0291c755b51480d659d6b3820

[stevej]

If you can reproduce this with querulous 2.6.5+, please feel free to re-open this issue.

[tsuna]

OK, but FWIW I do not believe my problem had to do with these bugs. I guess I was using Querulous incorrectly, but I still don't see how.