Thursday, 5 December 2013

HikariCP (Connection Pool) with Pivotal GemFireXD

I decided to try out the HikariCP as per the link below it says it's the fastest Connection Pool and the most lightweight. It probably is so I thought I would set it up for GemFireXD.

Quote: There is nothing faster.1 There is nothing more correct. HikariCP is a "zero-overhead" production-quality connection pool. Coming in at roughly 50Kb, the library is extremely light.

https://github.com/brettwooldridge/HikariCP

Example Below.

1. Create a Pool class as follows

  
package pivotal.au.gemfirexd.demos.connectionpool;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.sql.Connection;
import java.sql.SQLException;

public class HikariGFXDPool
{

    private static HikariGFXDPool instance = null;
    private HikariDataSource ds = null;

    static
    {
        try
        {
            instance = new HikariGFXDPool();
        }
        catch (Exception e)
        {
            throw new RuntimeException(e.getMessage(), e);
        }

    }

    private HikariGFXDPool()
    {
        HikariConfig config = new HikariConfig();
        config.setMaximumPoolSize(10);
        config.setMinimumPoolSize(2);

        config.setDataSourceClassName("com.vmware.sqlfire.internal.jdbc.ClientDataSource");

        config.addDataSourceProperty("portNumber", 1527);
        config.addDataSourceProperty("serverName", "192.168.1.6");
        config.addDataSourceProperty("user", "app");
        config.addDataSourceProperty("password", "app");

        ds = new HikariDataSource(config);
    }

    public static HikariGFXDPool getInstance ()
    {
        return instance;
    }

    public Connection getConnection()  throws SQLException
    {
        return ds.getConnection();
    }

}
2. Create a Test Class as follows
  
package pivotal.au.gemfirexd.demos.connectionpool;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

public class TestPool
{
    private Logger logger = Logger.getLogger(this.getClass().getSimpleName());

    public void run () throws SQLException
    {
        Statement   stmt = null;
        ResultSet   rset = null;
        Connection  conn = null;

        HikariGFXDPool pool = HikariGFXDPool.getInstance();

        try
        {
            conn = pool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery("select * from dept order by 1");
            while (rset.next())
            {
                System.out.println(String.format("Dept[%s, %s, %s]",
                                        rset.getInt(1),
                                        rset.getString(2),
                                        rset.getString(3)));
            }
        }
        catch (SQLException se)
        {
            logger.log(Level.SEVERE, se.getMessage());
        }
        finally
        {
            if (stmt != null)
            {
                stmt.close();
            }
            
            if (rset != null)
            {
                rset.close();
            }
            
            if (conn != null)
            {
                conn.close();
            }

        }

    }

    public static void main(String[] args) throws SQLException
    {
        TestPool test = new TestPool();
        test.run();
    }
} 

3. Run Test class and verify output as follows


SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
Dept[10, ACCOUNTING, NEW YORK]
Dept[20, RESEARCH, DALLAS]
Dept[30, SALES, CHICAGO]
Dept[40, OPERATIONS, BRISBANE]
Dept[50, MARKETING, ADELAIDE]
Dept[60, DEV, PERTH]
Dept[70, SUPPORT, SYDNEY]

5 comments:

Brett Wooldridge said...

Hi, this is Brett Wooldridge, the main author of HikariCP. While your example works, you appear to be essentially wrapping the HikariCP connection pool around the Apache DBCP connection pool.

While this might work, the effects could be strange and actually lower performing than Apache DBCP by itself due to the additional layer.

I would suggest trying something like:

config.setDataSourceClassName("com.vmware.sqlfire.internal.jdbc.ClientDataSource");

Pas Apicella said...

Yes we don't seem to have a DataSource IMPL which is not internal. I tried using that but it failed so had to go back to Apache Pool.

We shouldn't be using the internal classes in any case.

Pas Apicella said...

Hi Brett,

Managed to get it to work using the internal ClientDataSource , need to find out if we can actually use this.

private HikariGFXDPool()
{
HikariConfig config = new HikariConfig();
config.setMaximumPoolSize(10);
config.setMinimumPoolSize(2);

config.setDataSourceClassName("com.vmware.sqlfire.internal.jdbc.ClientDataSource");

config.addDataSourceProperty("portNumber", 1527);
config.addDataSourceProperty("serverName", "192.168.1.6");
config.addDataSourceProperty("user", "app");
config.addDataSourceProperty("password", "app");

ds = new HikariDataSource(config);
}

Brett Wooldridge said...

I found it in the VMWare vFabric documentation:

http://pubs.vmware.com/vfabric5/index.jsp?topic=/com.vmware.vfabric.sqlfire.1.0/developers_guide/embedded_datasource.html

And in the GemFire XD documentation (essentially a copy of the above):

http://gemfirexd-05.run.pivotal.io/index.jsp?topic=/com.pivotal.gemfirexd.0.5/developers_guide/embedded_datasource.html

Pas Apicella said...

Yep all good.

This will now be my pool of choice...