BoneCP connection pooling – Some useful Tips

Friends , while developing an application which require high availability of application. It is often a requirement to use any kind of connection pooling. BoneCP connection pooling is a database connection pooling which has a good features that i have explained in my earlier post.There are some minor fine tuning require to work with BoneCP.  which are listed below.

1. Always keep setPartitionCount to less then or equal to 3.

In order to reduce lock contention and thus improve performance, each incoming connection request picks off a connection from a pool that has thread-affinity, i.e. pool[threadId % partition_count]. The higher this number, the better your performance will be for the case when you have plenty of short-lived threads. Beyond a certain threshold, maintenence of these pools will start to have a negative effect on performance (and only for the case when connections on a partition start running out).

 

• maxConnectionsPerPartitionThe number of connections to create per partition. Setting this to 5 with 3 partitions means you will have 15 unique connections to the database. Note that BoneCP will not create all these connections in one go but rather start off with minConnectionsPerPartition and gradually increase connections as required.
• minConnectionsPerPartitionThe number of connections to start off with per partition.
• acquireIncrementWhen the available connections are about to run out, BoneCP will dynamically create new ones in batches. This property controls how many new connections to create in one go (up to a maximum of maxConnectionsPerPartition). Note: This is a per partition setting.Default: 10

 

2. To keep alive the connection use

config.setIdleConnectionTestPeriodInMinutes(10);
config.setConnectionTestStatement("/* ping */ SELECT 1"):

so in this way the application will use the above query to perform test connection to server whenever require.

3. Always commit() after performing transaction to avoid unwanted errors.

 

Database Connection Pooling

 Friends in software engineering, a connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required.

There are various types of Connection pooling. which are listed below.

1. DBCP2 Connection pooling

https://commons.apache.org/proper/commons-dbcp/

2. BoneCP

http://jolbox.com/

3. HikariCP

http://brettwooldridge.github.io/HikariCP/

4. C3PO

http://www.mchange.com/projects/c3p0/

By Default Hibernate uses a C3P0 Connection pooling for creating connections. From performance point of view HikariCP is best as it can handle large number of concurrent connections. while BoneCP is alternative to C3PO & provide following good features

• Highly scalable, fast connection pool
• Callback (hook interceptor) mechanisms on a change of connection state.
• Partitioning capability to increase performance
• Allows direct access to a connection/statements
• Automatic resizing of pool
• Statement caching support
• Support for obtaining a connection asynchronously (by returning a Future<Connection>)
• Release helper threads to release a connection/statement in an asynchronous fashion for higher performance.
• Easy mechanism to execute a custom statement on each newly obtained connection (initSQL).
• Support to switch to a new database at runtime without shutting down an application
• Ability to replay any failed transaction automatically (for the case where database/network goes down etc)
• JMX support
• Lazy initialization capable
• Support for XML/property configuration
• Idle connection timeouts / max connection age support
• Automatic validation of connections (keep-alives etc)
• Allow obtaining of new connections via a datasource rather than via a Driver
• Datasource/Hibernate support capable
• Debugging hooks to highlight the exact place where a connection was obtained but not closed
• Debugging support to show stack locations of connections that were closed twice.
• Custom pool name support.
• Clean organised code. 100% unit test branch code coverage (over 180 JUnit tests).
• Free, open source and written in 100% pure Java with complete Javadocs.