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.)