Comparision Between Connection Pooling Frameworks

1. Introduction :

A connection pool is a collection of connections that are established and can then be shared between different threads.

Prior to connection pooling, when a session was disconnected, the connection was destroyed, the server process was terminated and the network connections were dropped. In summary, when disconnecting all of the work done in establishing the session was wasted.

Obviously, it would be much more efficient to retain a session once a thread had finished with it and then re-use it for subsequent threads. The session pool contains all sessions that are either in use or free. ('Free' sessions as are those that have been disconnected and are ready to be 'recycled'.)

   

2. How Connection pooling Works :

2.1 Architecture :

 

2.2 Implementation using BoneCP Framework :

BoneCPDBPool.java

        {

            ....

            Class.forName(Constants.DRIVER_NAME);

            BoneCPConfig config = new BoneCPConfig();

            config.setJdbcUrl(Constants.JDBC_URL);

            config.setUsername(Constants.DB_USERNAME);

            config.setPassword(Constants.DB_PASSWORD);

            config.setMinConnectionsPerPartition(5);

            config.setMaxConnectionsPerPartition(15);

            config.setPartitionCount(4);

            config.setConnectionTimeout(60, TimeUnit.SECONDS);

            config.setAcquireIncrement(5);

            config.setIdleConnectionTestPeriod(60, TimeUnit.SECONDS);

            config.setIdleMaxAge(50, TimeUnit.SECONDS);

            static BoneCP connectionPool = new BoneCP(config);

            ....

            ....

            Connection connection = connectionPool.getConnection();

            ....

        }

   

2.3 Implementation using C3P0 Framework:

    C3P0DBPool.java

        {

            ....

            static ComboPooledDataSource cpds = new ComboPooledDataSource();

            cpds.setDriverClass( Constants.DRIVER_NAME );

            cpds.setJdbcUrl(Constants.JDBC_URL);

            cpds.setUser(Constants.DB_USERNAME);

            cpds.setPassword(Constants.DB_PASSWORD);

           

            // the settings below are optional -- c3p0 can work with defaults

            cpds.setInitialPoolSize(5);

            cpds.setMinPoolSize(5);

            cpds.setMaxPoolSize(60);

            cpds.setAcquireIncrement(5);

            cpds.setAcquireRetryAttempts(5);

            cpds.setMaxStatements(0);

            cpds.setNumHelperThreads(1);

            cpds.setIdleConnectionTestPeriod(60);

            cpds.setMaxConnectionAge(60);

            cpds.setMaxIdleTime(60);

            cpds.setMaxIdleTimeExcessConnections(60);

            ....

            ....

            Connection connection = cpds.getConnection();

            ....

            ....

        }

   

2.4 Implementation using Snaq DBPool Framework:

        SnaqDBPool.java

        {

            ....
            ....
            ConnectionPoolManager connManager =                                  ConnectionPoolManager.getInstance("DBPool.properties");  
            Connection connection = connManager.getConnection("pool-mysql");
            ....
            ....

        }

   

    DBPool.properties

   

        # Local Database Details

        name=pool-mysql

        drivers=com.mysql.jdbc.Driver

        pool-mysql.url=jdbc:mysql://localhost:3306/testing

        pool-mysql.user=root

        pool-mysql.password=

PASSWORD

        pool-mysql.minpool=5

        pool-mysql.maxpool=60

        pool-mysql.maxsize=60

        pool-mysql.idleTimeout=60

        pool-mysql.validator=snaq.db.AutoCommitValidator

2.5 Test Case Result   

Frameworks	Min Connections	Max Connections	JMeter-Number of Threads	JMeter-Ramp-Up Period (in seconds)	JMeter-Loop Count	JMeter-Server	Tomcat - Max Thread Parameter	Errors
BoneCP	20	60	50000	1	1	Local	200/400	0
C3P0	5	60	50000	1	1	Local	400	0
Snaq DBPool	5	60	50000	1	1	Local	200/400	8