bonecp
BoneCP is a Java JDBC connection pool implementation that is tuned for high performance by minimizing lock contention to give greater throughput for your applications. It beats older connection pools such as C3P0 and DBCP but should now be considered deprecated in favour of HikariCP.
BoneCP - The fast Java JDBC Connection Pool >bonecp - the fast java jdbc connection pool
Does BoneCP (or any other pool) close connection's statements when connection is returned to pool? As I understand, it does not call actual connection's close method, so there is no automatic statement closing. So, does it close statements in any other way or do I need to close them manually?
Source: (StackOverflow)
For a Java app outside of a J2EE container, which connection pool library is the best?
- I heard c3p0 is getting outdated.
- Jakarta's common pool library is no longer under development
Therefore I'm left with BoneCP and DBPool. From what I can tell both have limited activity. The main difference I can see is performance, which BoneCP seems to win out with. However the documentation is pretty weak.
Which database pool library have you used in the real world and why? What was the good and bad?
Source: (StackOverflow)
Which JDBC connection pool library should I use for a new application project (not web application)?
- Apache DBCP has enough unresolved issues which are pushed until 2.0 I think.
- C3P0 development seems to be stopped.
- And both of them looks relatively slow.
- Proxool is dead.
- DBPool has almost no community (at least I've found no public one - no forums, no mailing lists...)
- Apache Tomcat Pool looks to be unusable without Tomcat
- I've found SQL Datasources article at Oracle website, but it seems, that it can be applied only to applets running in containers like servlets and web services.
Should I choose BoneCP may be? I don't have any huge requirements. I just need a good and easy to use database connection pool, that is in active development. Library, whose author can respond to bug reports, answer some specific question etc.
BTW, actually, I'm using MySQL only. I've found, that MySQL driver supports DriverManager interface, but I'm unsure if it actually pools connections or not.
Source: (StackOverflow)
I've just started using BoneCP and pulled the sample JDBC code from the authors site.
I have a function called getConnection() that returns a connection here is a snippet:
// setup the connection pool
BoneCPConfig config = new BoneCPConfig();
// Config goes here.
connectionPool = new BoneCP(config); // setup the connection pool
return connectionPool.getConnection(); // fetch a connection
Now, my questions:
1) Do I call connection.close() when I am finished using the connection that is returned from above function so it is returned to the pool OR does this close the connection completely? How do I return connection to pool?
2) How to cleanup the pool on application quit? Do I call connectionPool.shutdown() when i'm finishing up? And also, I read somewhere that I need to close all pooled connections individually? Is this true?
Thanks.
Source: (StackOverflow)
From official BoneCP doc: http://jolbox.com/index.html?page=http://jolbox.com/configuration.html
partitionCount In order to reduce lock contention and thus improve performance, each incoming connection request picks off a connection
from a pool that has thread-affinity, i.e. pool[threadId %
partition_count]. The higher this number, the better your performance
will be for the case when you have plenty of short-lived threads.
Beyond a certain threshold, maintenence of these pools will start to
have a negative effect on performance (and only for the case when
connections on a partition start running out).
Default: 2, minimum: 1, recommended: 3-4 (but very app specific)
But it is not so clear and does not have a good example. I am running a normal web service, with 0-500 simultaneous thread. Which is a good value and why?
Source: (StackOverflow)
I'm running a Play 2.2.0 app, using Postgres 9.2-1002-jdbc4, and the Anorm 2.2.0 API. Every so often I have connection issues which look like:
2013-11-19 19:45:36,544 [ERROR][a.d.Dispatcher][play-akka.actor.default-dispatcher-17] This connection has been closed.org.postgresql.util.PSQLException: This connection has been closed.
at org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:822) ~[org.postgresql.postgresql-9.2-1002-jdbc4.jar:na]
at org.postgresql.jdbc2.AbstractJdbc2Connection.setAutoCommit(AbstractJdbc2Connection.java:769) ~[org.postgresql.postgresql-9.2-1002-jdbc4.jar:na]
at com.jolbox.bonecp.ConnectionHandle.setAutoCommit(ConnectionHandle.java:1247) ~[com.jolbox.bonecp-0.8.0-rc1.jar:na]
at com.jolbox.bonecp.ConnectionHandle.<init>(ConnectionHandle.java:251) ~[com.jolbox.bonecp-0.8.0-rc1.jar:na]
at com.jolbox.bonecp.ConnectionHandle.recreateConnectionHandle(ConnectionHandle.java:273) ~[com.jolbox.bonecp-0.8.0-rc1.jar:na]
at com.jolbox.bonecp.ConnectionHandle.close(ConnectionHandle.java:476) ~[com.jolbox.bonecp-0.8.0-rc1.jar:na]
at play.api.db.AutoCleanConnection.close(DB.scala:485) ~[com.typesafe.play.play-jdbc_2.10-2.2.0.jar:2.2.0]
at play.api.db.DBApi$class.withConnection(DB.scala:84) ~[com.typesafe.play.play-jdbc_2.10-2.2.0.jar:2.2.0]
at play.api.db.BoneCPApi.withConnection(DB.scala:276) ~[com.typesafe.play.play-jdbc_2.10-2.2.0.jar:2.2.0]
at play.api.db.DBApi$class.withTransaction(DB.scala:97) ~[com.typesafe.play.play-jdbc_2.10-2.2.0.jar:2.2.0]
at play.api.db.BoneCPApi.withTransaction(DB.scala:276) ~[com.typesafe.play.play-jdbc_2.10-2.2.0.jar:2.2.0]
at play.api.db.DB$$anonfun$withTransaction$4.apply(DB.scala:185) ~[com.typesafe.play.play-jdbc_2.10-2.2.0.jar:2.2.0]
at play.api.db.DB$$anonfun$withTransaction$4.apply(DB.scala:185) ~[com.typesafe.play.play-jdbc_2.10-2.2.0.jar:2.2.0]
at scala.Option.map(Option.scala:145) ~[org.scala-lang.scala-library-2.10.2.jar:na]
at play.api.db.DB$.withTransaction(DB.scala:185) ~[com.typesafe.play.play-jdbc_2.10-2.2.0.jar:2.2.0]
There are no DB connectivity issues I know of; the DB runs on the same box. These connection issues occur intermittently around 2-3 times a day and recovers on its own.
I tried these ridiculously low connection test settings:
db.default.idleConnectionTestPeriod=10 seconds
db.default.idleMaxAge=30 seconds
db.default.maxConnectionAge=60 seconds
These connection settings seem to to reduce it though I'm wondering if something more fundamental is going on. Nothing appears in the postgres logs.
Source: (StackOverflow)
Our site get roughly 1M pv/day, and we use Tomcat for sure.
I couldn't find much information about jdbc-pool, not sure if it's stable enough for production. Anyone got experience on it? and any configuration/tuning stuff for reference?
As someone mentioned, BoneCP might be another choice. But seems it's discontinued (so sad...). Would it be a better choice?
btw, HikariCP is too young, I would keep an eye on it as it's the latest/fastest CP so far I found.
Thanks for any advice.
Source: (StackOverflow)
I have just started using BoneCP and this is my first time using a connection pool. I'm somewhat confused as to how I am supposed to use it. Currently I am saving the BoneCP-object as a static variable, and thus I can use it between different connections.
When I'm done with the connection, I close it with connection.close().
Should I do this, or should I not close it to enable it to be reused by the pool?
This is my current implementation to get a connection:
private static BoneCP connectionPool;
public Connection getConnection() throws SQLException {
if (connectionPool == null) {
initPool();
}
return connectionPool.getConnection();
}
private void initPool() throws SQLException {
BoneCPConfig config = new BoneCPConfig();
config.setJdbcUrl(DB_URL);
config.setUsername(DB_USERNAME);
config.setPassword(DB_PASSWORD);
config.setMinConnectionsPerPartition(5);
config.setMaxConnectionsPerPartition(10);
config.setPartitionCount(1);
connectionPool = new BoneCP(config);
}
Does this seem correct or have I misunderstood how I am supposed to use BoneCP?
Source: (StackOverflow)
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?
Source: (StackOverflow)
I have a problem with BoneCP (0.7.1 RELEASE). I though that BoneCP.getConnection() ensures it would return Connection object assuming that DB is alive.
Here is how I configured my pool
private void setupConnectionPool() throws SQLException
{
// setup the connection pool
String connectUri = "jdbc:mysql://master-mysql:3306/base?zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&useCompression=true";
BoneCPConfig config = new BoneCPConfig();
config.setJdbcUrl(connectUri);
config.setUsername("dbapp");
config.setPassword("meh");
config.setMinConnectionsPerPartition(5);
config.setMaxConnectionsPerPartition(10);
config.setPartitionCount(1);
config.setConnectionTimeoutInMs(5 * 1000);
this.connectionPool = new BoneCP(config); // setup the connection pool
}
then somewhere in the code I use it like this
// I'm using Apache DbUtils
QueryRunner run = new QueryRunner();
Object result = run.query(this.connectionPool.getConnection(), query, handler, start, limit);
Attempt to run this query throws SQLException with state 08S01 (Communications link failure).
Subsequent call to this.connectionPool.getConnection() returns good connection.
But isn't it a whole point of connection pool so that I don't have to handle cases of lost connection myself?
Source: (StackOverflow)
I have been using Play 2.0.2 in order to create a Java application. For a few days I'm running into a problem. After ~100 request the server starts to throw this exception:
[[SQLException: Timed out waiting for a free available connection.]]
I create a new instance of Connection with DB.getConnection(). I don't close the Connection instances because there's only one instance of each request and as far as I know it automatically closes Connection instances when the active TCP connection is closed.
I tried to increase db.default.connectionTimeout value to 100 seconds but it couldn't fix the problem. Then I checked active Postgresql connections and there was no active connection. Also I restarted Postgresql but it also couldn't fix the problem.
The only solution for this problem for now is killing the Play20 instance and starting a new one.
Here is the log Play2.0 created:
! @6cg9il6ki - Internal server error, for request [GET [AN URL]] ->
play.core.ActionInvoker$$anonfun$receive$1$$anon$1: Execution exception [[SQLException: Timed out waiting for a free available connection.]]
at play.core.ActionInvoker$$anonfun$receive$1.apply(Invoker.scala:134) [play_2.9.1.jar:2.0.2]
at play.core.ActionInvoker$$anonfun$receive$1.apply(Invoker.scala:115) [play_2.9.1.jar:2.0.2]
at akka.actor.Actor$class.apply(Actor.scala:318) [akka-actor.jar:2.0.2]
at play.core.ActionInvoker.apply(Invoker.scala:113) [play_2.9.1.jar:2.0.2]
at akka.actor.ActorCell.invoke(ActorCell.scala:626) [akka-actor.jar:2.0.2]
at akka.dispatch.Mailbox.processMailbox(Mailbox.scala:197) [akka-actor.jar:2.0.2]
Caused by: java.sql.SQLException: Timed out waiting for a free available connection.
at com.jolbox.bonecp.BoneCP.getConnection(BoneCP.java:503) ~[bonecp.jar:0.7.1.RELEASE]
at com.jolbox.bonecp.BoneCPDataSource.getConnection(BoneCPDataSource.java:114) ~[bonecp.jar:0.7.1.RELEASE]
at play.api.db.DBApi$class.getConnection(DB.scala:64) ~[play_2.9.1.jar:2.0.2]
at play.api.db.BoneCPApi.getConnection(DB.scala:273) ~[play_2.9.1.jar:2.0.2]
at play.api.db.DB$$anonfun$getConnection$1.apply(DB.scala:129) ~[play_2.9.1.jar:2.0.2]
at play.api.db.DB$$anonfun$getConnection$1.apply(DB.scala:129) ~[play_2.9.1.jar:2.0.2]
Source: (StackOverflow)
I was using BoneCP for my Java projects. But, unfortunately, I discovered that this pool is unable to recover after the database failure. I am not the only one with such problem (just look at the official forums if you want).
As I (and many others) never got answer on bonecp forums, it seems that this project is no longer supported and my issue is unlikely to be fixed.
So, I am looking for an alternative actively developed and supported connection pooling library which is able to handle database outages correctly and recover as soon as database is available again.
Source: (StackOverflow)
Locally when I am developing my application I launch my play2 application using sbt run
I love how I can make code changes, and then reload my browser to see my changes.
After about roughly 10 code changes or so, I get a postgresql too many connection error (see below).
My db connection is using the below DatabaseAccess.scala class.
I'm guessing on each reload it is creating a bunch of connections to postgresql. In my Global am currently doing:
override def onStart(app: Application) {
Logger.info("Global.onStart")
DatabaseAccess.loadConfiguration()
}
In production I am also worried that if I make multiple deploys in a short period of time, or start/stop my service I might run into this problem also.
How can I ensure all connections are destroyed? I know I can either put something in the onStart or OnStop but I'm not really sure how to clear the connections that might have existed previously. (assuming this isn't a bug)
I did create a release() method that I was calling on onStop but that didn't seem to work:
def release() {
configs = Map[String, BoneCPConfig]()
dataSources = Map[String, BoneCPDataSource]()
databases = dataSources.map { case(key, value) => (key, Database.forDataSource(value)) }
}
All I did was re-init the vars so I guess that wasn't really what I needed to do.
My data access pattern is as follows:
def getById(userId: Int): Option[User] = {
db.withSession { implicit session =>
return userDao.getById(userId)
}
}
My database access class looks like:
https://github.com/TechEmpower/FrameworkBenchmarks/blob/master/unfiltered/src/main/scala/DatabaseAccess.scala
My application.conf connection looks like:
#postgresql
db.default.driver="org.postgresql.Driver"
db.default.url = "jdbc:postgresql://localhost/testweb_development"
db.default.user = "testdbuser"
db.default.password = ""
db.default.minConnections = 4
db.default.maxConnections = 24
db.default.maxThreads = 2
Locally when developing after about approximately 10 code changes and sbt doing it's nice partial class reloads I get this error:
play.api.Application$$anon$1: Execution exception[[SQLException: Unable to open a test connection to the given database. JDBC url = jdbc:postgresql://localhost/testweb_development, username = testdbuser. Terminating connection pool (set lazyInit to true if you expect to start your database after your app). Original Exception: ------
org.postgresql.util.PSQLException: FATAL: sorry, too many clients already
at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:291)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:108)
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125)
at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:22)
at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:30)
at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24)
at org.postgresql.Driver.makeConnection(Driver.java:393)
at org.postgresql.Driver.connect(Driver.java:267)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:185)
at com.jolbox.bonecp.BoneCP.obtainRawInternalConnection(BoneCP.java:363)
at com.jolbox.bonecp.BoneCP.<init>(BoneCP.java:416)
at com.jolbox.bonecp.BoneCPDataSource.getConnection(BoneCPDataSource.java:120)
at scala.slick.jdbc.JdbcBackend$DatabaseFactoryDef$$anon$4.createConnection(JdbcBackend.scala:47)
at scala.slick.jdbc.JdbcBackend$BaseSession.conn$lzycompute(JdbcBackend.scala:302)
at scala.slick.jdbc.JdbcBackend$BaseSession.conn(JdbcBackend.scala:302)
at scala.slick.jdbc.JdbcBackend$BaseSession.close(JdbcBackend.scala:316)
at scala.slick.backend.DatabaseComponent$DatabaseDef$class.withSession(DatabaseComponent.scala:31)
at scala.slick.jdbc.JdbcBackend$DatabaseFactoryDef$$anon$4.withSession(JdbcBackend.scala:46)
at com.exampleapp.services.UserServiceImpl.getById(UserService.scala:37)
at controllers.UsersController$$anonfun$show$1.apply(UsersController.scala:84)
at controllers.UsersController$$anonfun$show$1.apply(UsersController.scala:76)
at play.api.mvc.ActionBuilder$$anonfun$apply$10.apply(Action.scala:221)
at play.api.mvc.ActionBuilder$$anonfun$apply$10.apply(Action.scala:220)
at controllers.ActionWithContext$.invokeBlock(BaseController.scala:42)
at play.api.mvc.ActionBuilder$$anon$1.apply(Action.scala:309)
at play.api.mvc.Action$$anonfun$apply$1$$anonfun$apply$4$$anonfun$apply$5.apply(Action.scala:109)
at play.api.mvc.Action$$anonfun$apply$1$$anonfun$apply$4$$anonfun$apply$5.apply(Action.scala:109)
at play.utils.Threads$.withContextClassLoader(Threads.scala:18)
at play.api.mvc.Action$$anonfun$apply$1$$anonfun$apply$4.apply(Action.scala:108)
at play.api.mvc.Action$$anonfun$apply$1$$anonfun$apply$4.apply(Action.scala:107)
at scala.Option.map(Option.scala:145)
at play.api.mvc.Action$$anonfun$apply$1.apply(Action.scala:107)
at play.api.mvc.Action$$anonfun$apply$1.apply(Action.scala:100)
at play.api.libs.iteratee.Iteratee$$anonfun$mapM$1.apply(Iteratee.scala:481)
at play.api.libs.iteratee.Iteratee$$anonfun$mapM$1.apply(Iteratee.scala:481)
at play.api.libs.iteratee.Iteratee$$anonfun$flatMapM$1.apply(Iteratee.scala:517)
at play.api.libs.iteratee.Iteratee$$anonfun$flatMapM$1.apply(Iteratee.scala:517)
at play.api.libs.iteratee.Iteratee$$anonfun$flatMap$1$$anonfun$apply$13.apply(Iteratee.scala:493)
at play.api.libs.iteratee.Iteratee$$anonfun$flatMap$1$$anonfun$apply$13.apply(Iteratee.scala:493)
at scala.concurrent.impl.Future$PromiseCompletingRunnable.liftedTree1$1(Future.scala:24)
at scala.concurrent.impl.Future$PromiseCompletingRunnable.run(Future.scala:24)
at akka.dispatch.TaskInvocation.run(AbstractDispatcher.scala:42)
at akka.dispatch.ForkJoinExecutorConfigurator$AkkaForkJoinTask.exec(AbstractDispatcher.scala:386)
at scala.concurrent.forkjoin.ForkJoinTask.doExec(ForkJoinTask.java:260)
at scala.concurrent.forkjoin.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1339)
at scala.concurrent.forkjoin.ForkJoinPool.runWorker(ForkJoinPool.java:1979)
at scala.concurrent.forkjoin.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:107)
Environment
I am running scala 2.10.3, play 2.2.3
I am launching my app using sbt run.
Postgresql driver version: 9.1-901.jdbc4
slick version: 2.0.1
bonecp version: 0.8.0.RELEASE
Source: (StackOverflow)
I'm using an embedded Jetty 8 Server with the jetty-maven-plugin. I cannot get BoneCP to log the statements that are executed. Am I doing anything wrong? Are there any workarounds?
This is my BoneCPDataSource:
<New id="DSTest" class="org.eclipse.jetty.plus.jndi.Resource">
<Arg></Arg>
<Arg>jdbc/DSTest</Arg>
<Arg>
<New class="com.jolbox.bonecp.BoneCPDataSource">
<Set name="driverClass">com.mysql.jdbc.Driver</Set>
<Set name="jdbcUrl">my_url</Set>
<Set name="username">my_username</Set>
<Set name="password">my_password</Set>
<Set name="partitionCount">5</Set>
<Set name="minConnectionsPerPartition">5</Set>
<Set name="maxConnectionsPerPartition">50</Set>
<Set name="acquireIncrement">5</Set>
<Set name="idleConnectionTestPeriod">30</Set>
<Set name="logStatementsEnabled">true</Set>
</New>
</Arg>
</New>
This is my log4j property file loaded by the servlet through the servlet context:
log4j.rootLogger=debug, stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] (%F:%L) - %m%n
Thanks
Source: (StackOverflow)
I know that, at some point between Grails 1.X and Grails 2.X, the default connection pooling library changed from commons-dbcp to tomcat-dbcp.
Now, I'm trying to configure either BoneCP or HikariCP as the connection pooling library for my Grails application.
However, I see that this answer offers a solution which might only apply to Grails 1.X.
I also found this Gist, but again, I don't know which Grails version it applies to.
So, is it possible to define a custom connection pool inside a Grails 2.3.6 application? Thanks!
Source: (StackOverflow)