How I Reduced Startup Time By ~80% Using Database Connection pool?

A connection pool is a cache of database connections maintained so that the connections can be reused when needed, reducing the overhead of establishing new connections each time the application interacts with the database.

A database connection pool is a mechanism used to manage and reuse database connections in a Java backend application. It helps improve the efficiency and performance of interactions between the application and the database by minimizing the overhead of creating and closing connections for every database operation.

Here’s how a database connection pool works in a Java backend:

Setting Up the Connection Pool:

• During the application startup phase, a set of initial database connections is established and added to the connection pool.
• The connection pool maintains a minimum and maximum number of connections that it can hold at any given time.

Requesting a Connection:

• When the application needs to interact with the database, it requests a connection from the connection pool.
• If there is an available connection in the pool, it is handed over to the application.

Reusing Connections:

• After the application completes its database operation, it returns the used connection back to the connection pool instead of closing it.
• The connection remains open and is marked as available for reuse.

Connection Validation:

• Before handing over a connection to the application, the connection pool might perform validation checks to ensure it’s still valid and operational.
• If a connection has become stale or disconnected, the connection pool can replace it with a fresh connection.

Managing Connection Limits:

• If all connections in the pool are currently in use and the application requests a connection, the connection pool can create a new connection if the maximum limit has not been reached.
• If the maximum limit has been reached, the application might have to wait until a connection becomes available.

Closing the Connection Pool:

When the application is shutting down, it’s important to properly close and release all the connections in the connection pool.

If you’re using GlassFish Server, which is an application server that supports Java EE (Enterprise Edition), you can also take advantage of connection pooling for your database interactions. GlassFish provides built-in support for connection pooling through its JDBC connection pool configuration. Here’s how you can configure and use connection pooling in GlassFish:

1. Configuring JDBC Connection Pool:

• - Log in to the GlassFish Admin Console (usually accessible at http://localhost:4848/).
• - Navigate to Resources > JDBC > JDBC Connection Pools.
• - Click “New…” to create a new JDBC Connection Pool.
• - Provide the required properties such as database URL, username, password, etc.
• - Configure pool-specific settings like the initial pool size, maximum pool size, and other relevant options.

2. Creating JDBC Resources:

• - Once the connection pool is configured, you need to create a JDBC resource that links to this pool.
• - Navigate to Resources > JDBC > JDBC Resources.
• - Click “New…” to create a new JDBC Resource.
• - Specify the JNDI name for the resource and associate it with the previously created connection pool.

3. Using JDBC Resource in Your Application:

• - In your Java backend code, you can use the JNDI name of the JDBC resource to obtain a database connection.
• - GlassFish will manage the connections from the pool and provide them to your application.

Example of using the JDBC resource in a Java EE servlet:

import javax.annotation.Resource;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
@WebServlet("/database")
public class DatabaseServlet extends HttpServlet {
 @Resource(lookup = "jdbc/YourJdbcResourceName")
 private DataSource dataSource;
 @Override
 protected void doGet(HttpServletRequest request, HttpServletResponse response)
 throws ServletException, IOException {
 try (Connection connection = dataSource.getConnection()) {
 // Use the connection for database operations
 PreparedStatement statement = connection.prepareStatement("SELECT * FROM your_table");
 ResultSet resultSet = statement.executeQuery();
 // Process the result set
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }
}

Remember to replace `”jdbc/YourJdbcResourceName”` with the actual JNDI name you specified when creating the JDBC resource in GlassFish.

By utilizing the built-in connection pooling features of GlassFish Server, you can easily manage database connections and improve the performance of your Java EE applications.

Why I used connection pooling?

Optimizing application startup time is crucial for providing a seamless user experience. One effective way to achieve this is by configuring a database connection pool. A connection pool is a cache of database connections maintained so that the connections can be reused when needed, reducing the overhead of establishing new connections each time the application interacts with the database.

Let’s walk through an example of how configuring a database connection pool can lead to an 80% improvement in application startup time:

Before Connection Pooling:

In a scenario where an application needs to establish a new database connection every time it interacts with the database, the startup time and overall performance can suffer. Here’s a simplified example:

Application Startup:

• Application initializes and loads its configurations.
• During startup, the application establishes individual connections to the database for each request it will make.

Database Connection Overhead:

• Creating a new database connection is a resource-intensive process involving network communication and authentication.
• If the application receives multiple requests in a short time, it needs to establish a new connection for each request.

Slow Startup and Performance:

• The application experiences slow startup due to the time taken to establish multiple database connections.
• Each new connection introduces overhead, leading to increased response times for user requests.

After Connection Pooling:

By configuring a database connection pool, the application can reuse existing connections, resulting in significant performance improvements:

Application Startup:

• Application initializes and loads its configurations.
• The connection pool is set up with a certain number of pre-established database connections.

Reusing Connections:

• As user requests come in, the application retrieves a connection from the pool instead of creating a new one.
• After the request is processed, the connection is returned to the pool for future use.

Fast Startup and Improved Performance:

• Since connections are pre-established and reused, the application startup time is greatly reduced.
• Reusing connections minimizes the overhead of creating and closing connections, leading to faster response times.

Quantifying the Improvement:

Let’s assume that without connection pooling, the application took 10 seconds to start up due to the overhead of establishing individual connections. With connection pooling, the application startup time reduces to 2 seconds.

Calculation:
Improvement = (Old Time — New Time) / Old Time * 100
Improvement = (10 seconds — 2 seconds) / 10 seconds * 100 = 80%

Therefore, by configuring a database connection pool, the application achieved an 80% improvement in startup time.