Best Practices for Sizing the JDBC Connection Pool

1. Introduction

In this tutorial, we’ll discuss the best strategies for sizing the JDBC connection pool.

2. What Is a JDBC Connection Pool, and Why Is It Used?

A JDBC connection pool is a mechanism used to manage database connections efficiently. Creating a database connection involves several time-consuming steps, such as:

• opening a connection to the database
• authenticating the user
• creating a TCP socket for communication
• sending and receiving data over the socket
• closing the connection and the TCP socket

Repeating these steps for each user request can be inefficient, especially for applications with many users. A JDBC connection pool addresses this problem by creating a pool of reusable connections ahead of time. When the application starts, it creates and maintains database connections in a pool. A Pool Connection Manager manages these connections and handles their lifecycle.

When a client requests a connection, the Pool Manager provides one from the pool instead of creating a new one. Once the client is done, the connection is returned to the pool for reuse rather than being closed. This reuse of connections saves time and resources, significantly improving application performance.

3. Why Is Deciding an Optimal Size for the JDBC Connection Pool Important?

Deciding the optimal size for a JDBC connection pool is crucial for balancing performance and resource utilization. A small pool might result in faster connection access but could lead to delays if there aren’t enough connections to satisfy all requests. Conversely, a large pool ensures that more connections are available, reducing the time spent in queues but potentially slowing down access to the connection table.

The following table summarises the pros and cons to consider when sizing connection pools:

4. Key Points to Consider While Deciding the JDBC Connection Pool Size

When deciding on the pool size, we need to consider several factors. First, we should evaluate the average transaction response time and the amount of time spent on database queries. Load testing can help determine these times, and it’s advisable to calculate the pool size with an additional 25% capacity to handle unexpected loads. Second, the connection pool should be capable of growing and shrinking based on actual needs. We can also monitor the system using logging statements or JMX surveillance to adjust the pool size dynamically.

Additionally, we should consider how many queries are executed per page load and the duration of each query. For optimal performance, we start with a few connections and gradually increase. A pool with 8 to 16 connections per node is often optimal. We can also adjust the Idle Timeout and Pool Resize Quantity values based on monitoring statistics.

5. Basic Settings That Control the JDBC Connection Pool

These basic settings control the pool size:

6. Best Practices for Sizing the JDBC Connection Pool

Here are some best practices for tuning the JDBC connection pool to ensure healthy connectivity to the database instance.

6.1. Validate Connection SQL

First, we add a validation SQL query to enable connection validation. It ensures connections in the pool are tested periodically and remain healthy. It also quickly detects and logs database failures, allowing administrators to act immediately. Choose the best validation method (e.g., metadata or table queries). After sufficient monitoring, switching off the connection validation can provide a further performance boost. Below is an example for configuring it using the most popular JDBC connection pool i.e. Hikari:

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:your_database_url");
config.setUsername("your_database_username");
config.setPassword("your_database_password");
config.setConnectionTestQuery("SELECT 1");
HikariDataSource dataSource = new HikariDataSource(config);

6.2. Maximum Pool Size

We can also tune the maximum pool size to be sufficient for the number of users using the application. It is necessary to handle unusual loads. Even in non-production environments, multiple simultaneous connections may be needed:

config.setMaximumPoolSize(20);

6.3. Minimum Pool Size

We should then tune the minimum pool size to suit the application’s load. If multiple applications/servers connect to the same database, having a sufficient minimum pool size ensures reserved DB connections are available:

config.setMinimumIdle(10);

6.4. Connection Timeout

An additional aspect we can consider is configuring the blocking timeout in milliseconds so that connection requests wait for a connection to become available without waiting indefinitely. A rational value of 3 to 5 seconds is recommended:

config.setConnectionTimeout(5000);

6.5. Idle Timeout

Set the idle timeout to a value that suits the application. For shared databases, a lower idle timeout (e.g., 30 seconds) can make idle connections available for other applications. A higher value (e.g., 120 seconds) can prevent frequent connection creation for dedicated databases:

config.setIdleTimeout(30000);

// or


config.setIdleTimeout(120000);

6.6. Thread Pool Tuning

Use Load/Stress testing to estimate minimum and maximum pool sizes. A commonly used formula to estimate pool size is connections = (2 * core_count) + number_of_disks. This formula provides a starting point, but requirements might differ based on I/O blocking and other factors. Start with a small pool size and gradually increase it based on testing. Monitoring tools like pg_stat_activity can help determine if connections are idling too much, indicating a need to reduce the pool size:

int coreCount = Runtime.getRuntime().availableProcessors();
int numberOfDisks = 2; // assuming two no. of disc
int connections = (2 * coreCount) + numberOfDisks;
config.setMaximumPoolSize(connections);
config.setMinimumIdle(connections / 2);

6.7. Transaction Isolation Level

Choose the best-performing isolation level that meets concurrency and consistency needs. Avoid specifying the isolation level unless necessary. If specified, set the Isolation Level Guaranteed to false for the applications that do not change the isolation level programmatically:

try (Connection conn = dataSource.getConnection()) {
    conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
} catch (SQLException e) {
    e.printStackTrace();
}

7. Conclusion

In this article, we discuss how to set up the JDBC connection pool. By understanding the factors influencing connection pool size and following best practices for tuning and monitoring, we can ensure healthy database connectivity and improved application performance.