Connection Pooling to Improve Database Performance

Introduction

In order to improve database performance, we usually employ some of the common caching mechanisms like caching the most frequent data and improve latency and/or read & write throughput. One of the other prominent way to improve database performance is to do connection pooling.

Before that, let’s see what all steps are part of a DB connection:

  • Opening a DB connection with DB server

  • Open TCP socket to read/write data

  • Read/write data

  • Close DB connection

  • Close TCP socket

What is Connection Pooling?

When clients make a request to DB server, every connection is followed by either start a new connection, continue an existing one or close the connection request. With each of these requests, a connection is established between client and DB. Every such connection consumes memory and if the number of connections increase, it can very easily become a bottleneck.

Connection Pooling is the process of caching database connections so that same connections can be reused for future requests and helps with query performance.

For example,

Without connection pool:

  • client creates a DB connection (performs appropriate authentication, authorization checks etc.)

  • client sends a query

  • client receives the query response

  • client ends the connection

With connection pool:

  • a new client will request a connection from connection pool

  • if there’s any connection available, that will be returned. Otherwise connection pool will create a new connection after performing security checks

  • client sends query and receives the query response over that connection

  • client closes the connection which will return it to the connection pool

Sample Implementation

/**
** A sample MySQL implementation for DB connection pooling
with fixed pool size and pre-created connections

*/

public class FixedSizeConnectionPool implements CPool {
 
  private String url;
  private String user;
  private String password;
  private List<Connection> connectionPool;
  private List<Connection> usedConnections = new ArrayList<>();
  private static int POOL_SIZE = 15;
  
  FixedSizeConnectionPool(String url, String user, String password, List<Connection> connectionPool) {
    this.url = url;
    this.user = user;
    this.password = password;
    this.connectionPool = connectionPool;
  }
  
  // creates 15 connections to DB and stores them to current pool
  public static FixedSizeConnectionPool create(
    String url, String user, 
    String password) throws SQLException {

      List<Connection> pool = new ArrayList<>(POOL_SIZE);
      for (int i = 0; i < POOL_SIZE; i++) {
          pool.add(createConnection(url, user, password));
      }
      return new FixedSizeConnectionPool(url, user, password, pool);
  }

  @Override
  public Connection getConnection() {
      // get a connection from the pool
      Connection connection = connectionPool
        .remove(connectionPool.size() - 1);
      // add it to the list of currently used connections
      usedConnections.add(connection);       
      return connection;
  }
  
  @Override
  public boolean freeUpConnection(Connection connection) {
      connectionPool.add(connection);
      return usedConnections.remove(connection);
  }
  
  // creates a DB connection using driver manager
  private static Connection createConnection(
    String url, String user, String password) 
    throws SQLException {
      return DriverManager.getConnection(url, user, password);
  }

  // this should be equal to total pool size (15 in our case)
  public int getSize() {
      return connectionPool.size() + usedConnections.size();
  }
}

// defines interface for connection pool
public interface CPool {
    Connection getConnection();
    boolean freeUpConnection(Connection connection);
    String getDBUrl();
    String getUser();
    String getPassword();
}

Another approach:

/**
** A sample MySQL implementation for DB connection pooling where connections are added when required
*/

public class OnDemandConnectionPool implements CPool {
 
  private String url;
  private String user;
  private String password;
  private List<Connection> connectionPool;
  private List<Connection> usedConnections = new ArrayList<>();
  private static int POOL_SIZE = 15;
  private int currentSize = 0;
  

OnDemandConnectionPool(String url, String user, String password) {
    this.url = url;
    this.user = user;
    this.password = password;
    this.connectionPool = new ArrayList<>(POOL_SIZE);;
  }

  // creates upto 15 connections to DB and stores them to current pool
  public static OnDemandConnectionPool create(
    String url, String user, 
    String password) throws SQLException {
      // don't create any connections until required
      return new OnDemandConnectionPool(url, user, password);
  }

  @Override
  public Connection getConnection() {
      // if there are no existing connections, create a new one and add it to used connections
      if(usedConnections.size() ==  POOL_SIZE) {
        // add a custom exception to know when pool size is reached
        throw new maxConnectionReachedException();      
      }
      if(connectionPool.size() == 0) {
        connectionPool.add(createConnection(url, user, password));
      }
      Connection connection = connectionPool
        .remove(connectionPool.size() - 1);
      usedConnections.add(connection);
      return connection;
  }
  
  @Override
  public boolean freeUpConnection(Connection connection) {
      connectionPool.add(connection);
      return usedConnections.remove(connection);
  }
  
  // creates a DB connection using driver manager
  private static Connection createConnection(
    String url, String user, String password) 
    throws SQLException {
      return DriverManager.getConnection(url, user, password);
  }

  // this should be equal to total pool size (15 in our case)
  public int getSize() {
      return connectionPool.size() + usedConnections.size();
  }
}

// defines interface for connection pool
public interface CPool {
    Connection getConnection();
    boolean freeUpConnection(Connection connection);
    String getDBUrl();
    String getUser();
    String getPassword();
}

[Repl.it Link to play around with these implementations further: here and here]

These are just sample implementations that gives a high level idea on how we can add a connection pool to mysql connections.

Key Concepts

While trying to implement a DB connection pool, some of the factors should be kept in mind:

  • Clients with similar properties should be the ones eligible for connection pooling.

  • Have some sort of mechanism to hold the queries if the number of queries exceed the total number of connections in the entire pool.

  • Unique user or auth mechanisms create new connection pools (since these create unique connection strings which in turn creates unique connections), hence users under same access mechanism should be grouped together to use common connection pools.

Conclusion

In short, keeping a certain number of connections pre-created and opened in connection pool are more memory and performance efficient than opening a new connection every time it’s needed.


If you like the post, share and subscribe to the newsletter to stay up to date with tech/product musings.

(The contents of this blog are of my personal opinion and/or self-reading a bunch of articles and in no way influenced by my employer.)