In this blog I am going to describe connection pooling in scala. Before going into the details I want to explain why and when connection pooling should be used.
Why connection pooling ??
For efficient use of database connection we need to care about following two things.
1. Creating a connection is an expensive operation. It is not senseful to create new connection each and every time when database access is required.
2. It is also important to close connection and other database resources.
Connection pooling provide one stop solution of above described issues.
In connection pooling , connection or set of connection objects are created single time ( usually at the start of application) and would be maintained so that the connections can be reused when future requests to the database are required. Connection pools are used to enhance the performance of executing commands on a database.
When use connection pooling
If database access is required more frequently in application then connection pooling must be used.
There are many open source connection pooling library available. I preferred BoneCP library.(In this discussion i am using a postgres database for connection pooling.)
1. add boneCP dependencies in build.sbt
2. Create connection pool:
Creation of connection pool requied some configuration parameter:
1. jdbcUrl- Jdbc database connection Url.
2. username- Database username.
3. password- Database password.
4. PartitionCount- 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).
Default: 1, minimum: 1, recommended: 3-4 (but very app specific)
5. maxConnectionsPerPartition- The 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.
6. minConnectionsPerPartition-The number of connections to start off with per partition.
for more configuration information: http://jolbox.com/bonecp/downloads/site/apidocs/com/jolbox/bonecp/BoneCPConfig.html
3. Take connection from connection pool and after use return connection to connection pool by calling close method on connection.(Here close method not closing connection, it return connection to connection pool)
4. Run sbt project:
Download complete code example from: here