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:
Connection Pool Size
Pros
Cons
Small Pool
Faster access to the connection table
We may need more connections to satisfy requests. Requests may spend more time in the queue.
Large Pool
More connections to fulfill requests. Requests spend less (or no) time in the queue
Slower access to the connection table
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:
Connection Pool Property
Description
Initial and Minimum Pool Size
The size of the pool when it is created and its minimum allowable size
Maximum Pool Size
The upper limit of the pool size
Pool Resize Quantity
The number of connections to be removed when the idle timeout expires. Connections idle for longer than the timeout are candidates for removal, stopping once the pool reaches the initial and minimum pool size
Max Idle Connections
The maximum number of idle connections allowed in the pool. If the number of idle connections exceeds this limit, the excess connections are closed, freeing up resources
Min Idle Connections
The minimum number of idle connections to keep in the pool
Max Wait Time
The maximum time an application will wait for a connection to become available
Validation Query
A SQL query used to validate connections before they are handed over to the application
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.
As always, the code is available over on GitHub.