Spring JDBC Template with BoneCP Connection Pooling Configuration

Question

I have one java spring application with MySQL as a database. The MySQL connection limit is approx 12k. But my current application contain only basic configuration so my MySQL hang up while client connection reached up to 1500 to 1600 connection only. Does anyone have an idea how to configure BoneCP connection pool for my current MySQL.

Configuration

<bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close">
<property name="driverClass" value="com.mysql.jdbc.Driver"/>
<property name="jdbcUrl" value=“{URL}”/>    
<property name="username" value=“{USER}”/>
<property name="password" value=“{PASSWORD}”/>
<property name="idleConnectionTestPeriodInMinutes" value="60"/>
<property name="idleMaxAgeInMinutes" value="240"/>
<property name="maxConnectionsPerPartition" value="30"/>
<property name="minConnectionsPerPartition" value="10"/>
<property name="partitionCount" value="3"/>
<property name="acquireIncrement" value="5"/>
<property name="statementsCacheSize" value="100"/>
<property name="releaseHelperThreads" value="3"/>
<property name="connectionTestStatement" value="Select    1"/>
</bean>

With your current config your application will use max 90 connection, are you sure your code is closing connections (which will return connections to the pool)? — Mark Rotteveel, Jan 09 '17 at 09:28
Thanks. I am using Spring JDBC template so i think Spring JDBC template internally handle for close connection. I have 10 server under my load balancer so please tell me what are the values for configuration — Hitendra Boda, Jan 09 '17 at 10:07

Vipul Panth · Answer 1 · 2017-01-09T10:52:24.207

Please see this updated configuration thanks.

  public static void main(String[] args) {

    BoneCP connectionPool = null;
    Connection connection = null;

    try {
        // load the database driver (make sure this is in your classpath!)
        Class.forName("org.hsqldb.jdbcDriver");
    } catch (Exception e) {
        e.printStackTrace();
        return;
    }

    try {

        // setup the connection pool
        BoneCPConfig config = new BoneCPConfig();
        config.setJdbcUrl("jdbc:hsqldb:mem:test");
        // url
        // specific to
        // your database
        config.setUsername("sa");
        config.setPassword("");
        config.setMinConnectionsPerPartition(50);
        config.setMaxConnectionsPerPartition(200);
        config.setPartitionCount(50);
        config.setLazyInit(false);
        connectionPool = new BoneCP(config); // setup the connection pool
        for (int i = 0; i < 200000; i++) {
            connection = connectionPool.getConnection(); // fetch a //connection
            System.out.println("Connection" + connection);

            if (connection != null) {
                System.out.println("Number of Connection successful " + i);
                Statement stmt = connection.createStatement();
                ResultSet rs = stmt.executeQuery("SELECT 1 FROM INFORMATION_SCHEMA.SYSTEM_USERS"); // do
                // something
                // with
                // the
                // connection.
                while (rs.next()) {
                    System.out.println(rs.getString(1)); // should print out
                    // "1"'
                }

            }
        }
        connectionPool.shutdown(); // shutdown connection pool.
    } catch (SQLException ex) {
        ex.printStackTrace();
    } finally {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

}

Thanks. But i have no idea about the value of following fields `idleConnectionTestPeriodInMinutes, idleMaxAgeInMinutes, maxConnectionsPerPartition, minConnectionsPerPartition, partitionCount, acquireIncrement, statementsCacheSize, releaseHelperThreads` for mysql connection limit up to 12000. — Hitendra Boda, Jan 09 '17 at 08:23
This doesn't answer the question at all; you are just dumping some random configuration code. — Mark Rotteveel, Jan 09 '17 at 09:29
i have increased MinConnectionsPerPartition to 50 and MaxConnectionsPerPartition to 100 and PartitionCount to 50 this will giving me around 12000 connection from the pool , for testing purpose i am using hsql database and as the documentation for BoneCp is weak can't find other ways, you can check with mysql database and test this, thanks — Vipul Panth, Jan 09 '17 at 10:58

Spring JDBC Template with BoneCP Connection Pooling Configuration

1 Answers1