BoneCP

Recently switched to over from using the default connection pool provided by tomcat 7 (which is a re-repackage of DBCP) to BoneCP. Why did we switch? noticed that connection pool wasn’t performing as well as we wanted, under heavy load, the synchronzied blocks that DBCP uses is a performance bottleneck.

Updating tomcat to use BoneCP datasource is answered in this stackoverflow post.

Set the “type” to the BoneCPDataSource (we take advantage of the fact, tomcat supports javax.sql.DataSource out of the box) and the “factory” needs to the Tomcat provideded BeanFactory implemenation.

 <Resource name="jdbc/myReourceName"
         type="jcom.jolbox.bonecp.BoneCPDataSource"
         factory="org.apache.naming.factory.BeanFactory"
         auth="Container"
         <!-- the BoneCPDataSource properties -->
          maxConnectionsPerPartition="10"
          maxConnectionsPerPartition = "2"
         
</Resource>

BoneCP uses lock-free techniques to achieve it’s great performance. One neat thing, BoneCP provides is ConnectionCook you can set on the Pool, to get call-back events when new connection is created or statement is about to be executed / completed, in addition, it provides, a debugHandle on the ConnectionHandle to allow to store your own tracking information, this is for useful for example if we wish store the SPID for connections (to log the spid of the connection that is executing the sql).

// What CustomConnectionHook might look like
public CustomConnectionHook extends AbstractConnectionHook {
  @Override
  public void onAcquire(ConnectionHandle connectionHandle) {
    // get the raw Internal Connection from ConnectionHandle
    // execute query on the raw internal connection to get the SPID
    connectionHandle.setDebugHandle(mySPID);
  }

  @Override
  public void onBeforeStatementExecute(ConnectionHandle connectionHandle, StatementHandle statement, String sql, Map<Object, Object> params) {
    if (logEnabled) {
      // if you are managing multiple db pools, it might be useful to log the poolname
      String poolName = connectionHandle.getPool().getConfig().getPoolName();
      //get out the conn spid from connectionHandle debug variable
      Integer spid = (Integer) connectionHandle.getDebugHandle();
 
      // log "poolname" + "spid" + "sql"  --- Outputting the SPID so that DEVOPS team can track the SPID on the Database monitoring tools
    }
  }
}

Leave a Reply

Your email address will not be published. Required fields are marked *