title: Java Connection Pooling best practices?
body: <p>After getting fed up with c3p0's constant locking I'm turning to BoneCP for an alternative connection pool for my database. I have a server app that processes around 7,000 items per minute and needs to log those items into our MySQL database. I currently have 100 worker threads and have set up my pool like such:</p>
  
  <pre><code>BoneCPConfig config = new BoneCPConfig();
        config.setJdbcUrl("jdbc:mysql://"+Settings.MYSQL_HOSTNAME+"/"+Settings.MYSQL_DATABASE+"?autoReconnectForPools=true" ); 
        config.setUsername(Settings.MYSQL_USERNAME); 
        config.setPassword(Settings.MYSQL_PASSWORD);
        config.setMinConnectionsPerPartition(5);
        config.setMaxConnectionsPerPartition(10);
        config.setPartitionCount(5);
        config.setAcquireIncrement(5);
        connectionPool = new BoneCP(config); // setup the connection pool
  </code></pre>
  
  <p>Are those acceptable settings for such an app? I'm asking because after a minute or two into running I was getting BoneCP exceptions when trying to call <code>getConnection</code> on the pool. Thanks for the help.</p>
  
  <p>Here is the code I was using for the db calls in my worker threads, it can't fail on the <code>dbConn = this.dbPool.getConnection()</code> line. Am I not closing connections properly?</p>
  
  <pre><code>private void insertIntoDb() {
      try {  
          Connection dbConn = this.dbPool.getConnection();
  
          try {
              PreparedStatement ps3 = dbConn.prepareStatement("INSERT IGNORE INTO test_table1 SET test1=?, test2=?, test3=?");
              ps3.setString(1, "some string");
              ps3.setString(2, "some other string");
              ps3.setString(3, "more strings");
              ps3.execute();
              ps3.close();
  
              PreparedStatement ps4 = dbConn.prepareStatement("INSERT IGNORE INTO test_table2 SET test1=?, test2=?, test3=?");
              ps4.setString(1, "some string");
              ps4.setString(2, "some other string");
              ps4.setString(3, "more strings");
              ps4.execute();
              ps4.close();
  
          } catch(SQLException e) {
              logger.error(e.getMessage());
          } finally {
              try {
                  dbConn.close();
              } catch (SQLException e) {
                  logger.error(e.getMessage());
              }
          }
      } catch(SQLException e) {
          logger.error(e.getMessage());
      }
  }
  </code></pre>
  
  <p>This is the error I was seeing:</p>
  
  <pre><code> [java]  WARN [com.google.common.base.internal.Finalizer] (ConnectionPartition.java:141) - BoneCP detected an unclosed connection and will now attempt to close it for you. You should be closing this connection in your application - enable connectionWatch for additional debugging assistance.
   [java]  WARN [com.google.common.base.internal.Finalizer] (ConnectionPartition.java:141) - BoneCP detected an unclosed connection and will now attempt to close it for you. You should be closing this connection in your application - enable connectionWatch for additional debugging assistance.
   [java]  WARN [com.google.common.base.internal.Finalizer] (ConnectionPartition.java:141) - BoneCP detected an unclosed connection and will now attempt to close it for you. You should be closing this connection in your application - enable connectionWatch for additional debugging assistance.
   [java]  WARN [com.google.common.base.internal.Finalizer] (ConnectionPartition.java:141) - BoneCP detected an unclosed connection and will now attempt to close it for you. You should be closing this connection in your application - enable connectionWatch for additional debugging assistance.
   [java]  WARN [com.google.common.base.internal.Finalizer] (ConnectionPartition.java:141) - BoneCP detected an unclosed connection and will now attempt to close it for you. You should be closing this connection in your application - enable connectionWatch for additional debugging assistance.
   [java]  WARN [com.google.common.base.internal.Finalizer] (ConnectionPartition.java:141) - BoneCP detected an unclosed connection and will now attempt to close it for you. You should be closing this connection in your application - enable connectionWatch for additional debugging assistance.
   [java]  WARN [com.google.common.base.internal.Finalizer] (ConnectionPartition.java:141) - BoneCP detected an unclosed connection and will now attempt to close it for you. You should be closing this connection in your application - enable connectionWatch for additional debugging assistance.
  
  ERROR pool-2-thread-39 2010-09-04 13:36:19,798 com.test.testpackage.MyTask - null
  java.sql.SQLException
      at com.jolbox.bonecp.BoneCP.getConnection(BoneCP.java:381)
  </code></pre>