A Generic Connection Pool

DB connection pooling

Problem

  1. Creating many connections on-going. If the remote system is slow, your app will create a lot of connections and wait. It will make open file count to exceed in your machine. Usually, a socket means a file.
  2. A good portion of application memory is acquired by connection objects made. Your application is slow as there’s less memory for other tasks.
  3. Third party system may crash as well as your app creates a lot of connections towards it.
  4. Upon a connection break, OS will take time to clean up resources so that you can try again creating new connections. Creating too many connections and discarding them frequently is an overhead to the OS.
  5. Overall slowness in application as you negotiate a new connection every time it needs to communicate with a third party system.

Reason

Solution

Connection creation

  • Connection pool needs to be initialised at the start of the application. It may use passed configurations, security keys etc.
  • If a connection pool initialisation fail application should fail.

Connection usage

  • When application is running, it will borrow a connection, use it and then release it back to the pool.
  • There can be situations such as there is no connection available in the pool (all are utilised by other threads of the application). When application is requesting a connection, on such a situation that thread can wait forever or wait with a timeout until a connection is released by another thread and it is available. These are features of a blocking pool implementation.
  • Sometimes, you should not block the application threads for any reason. If there is no connection to grab, it will return with null. It is up to the application to try again or discard. This behaviour is provided by non-blocking pool implementations.

Connection validation

  • Before application get a connection, better to validate the connection and get it. Otherwise operation may fail.
  • Before releasing the connection back to the pool, better validate and release. We are not sure if connection is spoilt by the application :)
  • Sometimes, better allow application to mark the connection as invalid. There can be unknown cases validation logic might not capture.
  • Upon invalidation of a connection, connection pool implementation should create a new connection and maintain correct pool size.

Connection re-creation

  • When a connection is invalidated, it should be evicted from the pool and a new connection needs to be created.
  • Here, if failed to establish the connection it needs to retry in an exponential back-off manner.
  • The application threads should not execute this retry. It should be done by a separate thread in the connection pool implementation.

Connection pool shutdown

  • When application is shutdown, connections needs to be shutdown properly.
  • New connection requests should be served only when pool operational. When marked for shutdown it should not serve connections.
  • Wait until all connections are returned and close() is executed on them gracefully.

Variants of the solution

  • Blocking pool implementation : Fixed size, threads will wait
  • Non-blocking pool implementation : Fixed side, threads will not wait

Bonus !!

  • Expanding pool implementation : Has an initial size, expands as per the request load to a max size. If load reduces automatically reduce back to initial size.

--

--

--

Associate Technical Lead at WSO2, Blogger

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Activating the Vagus Nerve Might Lower Your Covid-19 Risk

Top 10 Companies using Python in Backend

Python in backend

Building Golang Rest API With Fiber & Gorm in 5 days — Day 1 Go Fiber Inn

Polkadot APP Wallet User Guide

!!!!!Automate Webserver!!!!!! Using the AWS Cloud and Terraform Tool.

How to control flows in BusinessWorks 6.X and BusinessWorks Container Edition

Bringing Product Ideas to Life: How I built an app with zero code

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Hasitha Hiranya Abeykoon

Hasitha Hiranya Abeykoon

Associate Technical Lead at WSO2, Blogger

More from Medium

How to implement autocomplete with aggregation— Elastic Search

~Self User Registration~ WSO2 Identity Server

HDP Certified Administrator (HDPCA) Series || # 2 Add || Kafka || Strom || Services