Java

JDBC Connection Pools

Last updated Mar 11, 2005.

JDBC connection pools round out our top three tuning parameters, because most applications are backed by a database. Although there is no significant analysis required to determine whether you need to change the size of the pool, here are some initial setting recommendations:

  1. Count the number of request-types in your use cases that use a database connection.
  2. Compute the relative balance of requests that use a database connection to derive an estimate of the percentage of total requests that result in a database call.
  3. Analyze your existing user pattern (in a production application using access logs) or estimate your projected user pattern; the goal is to try to determine the frequency and duration of each request.
  4. Multiply the frequency of requests by the percentage that use database connections (result from step 2).

The number that you arrived at in step 4 is a good starting value for your connection pool size. It should be something like 30 requests per second.

Note that we have not normalized the frequency of requests by the duration of requests. Most applications do not hold on to database requests for the entire duration of a request, but rather obtain a database connection for a pool, use it, and return it to the pool. And most database queries (business requirements possibly inhibiting) are sub-second interactions.

Our goal is to determine a good starting point, so if the number you computed seems unreasonable then adjust it. As a rule of thumb, for average applications, 30 is a good number of connections to start with. Begin a load test of your application, and observe the following behaviors of your connection pool:

If you never have any threads waiting for a connection, and your connections in use never reaches 75%, then you may consider reducing the size of your pool. However, in a production application, it is best to watch the behavior over a week (or more) to determine whether peak times might require extra connections.

If you see that your connection pool usage frequently reaches 100%, and you have threads waiting for a connection, you need to increase the size of your connection pool. The number of connections by which to increase the connection pool will be less than the number of pending threads. As the connection pool is fully used, requests backup, waiting for a connection. With more connections, you can service the demand before the number of pending threads grows as high as it did in your test. Usually, you should start by increasing the connection pool size by half of the number of pending threads.

The end-all goal is to maintain a connection pool that peaks between 75% and 85% usage, and in which you never see pending requests. This gives you a buffer for extreme times and helps you efficiently manage resources.

Wait-based Application Server Tuning

The approach that I take in tuning a J2EE environment is what I refer to as "Wait-based" tuning. The premise is to open all request pathways from the HTTP server to the database (or external resource call) to determine the capacity of the end component (in this case, a database connection pool or JCA connection pool). Once you have that resource configured to its capacity, you work your way backwards until you reach your application server thread pools.

Both IBM and Oracle define tuning in terms of "waits." In simple terms: where does your application wait during request processing? Consider a summary of the top three:

Figure 61 shows the pathway of a request through wait points.

Figure 61

Figure 61. Request Wait Points

Using Figure 61 to follow a request from a browser through your application backend resources:

  1. It arrives at a Web server, either an embedded Web server or external Web server such as Apache.
  2. The request is forwarded to a Servlet container (which maintains its own thread pool).
  3. If the connection requires use of an EJB, it is forwarded to an EJB Container. (If the EJB container is in the same JVM, the same thread is used; but if the request passes to an EJB Container running in a different JVM, it uses its own thread pool.)
  4. The last layer depends on your architecture and the behavior of the specific request, but the meaning is essentially the same: does it have to wait for something? Such as a database connection, external resource JCA connection, or a cached object?

IBM points out in its tuning documents that the best place for a request to wait is at the first point that can service it. If your database is your bottleneck, there is no point in flooding all requests to wait on your database connection pools. It is better to determine exactly how many threads (in each tier) can maximize the database usage and only use that thread count. The result is that we push the wait as far "left" in the request wait point list, with all requests that the application server cannot handle waiting at the Web server. This approach minimizes the use of the application server's resources.

I have used this approach countless times in varying environments. In my experience, it has been the quickest approach to find the optimal tuning configuration.

Summary

In this series I am relating information about engagements that I have been on over the past nine months tuning J2EE enterprise applications. Thus far, we have looked at the three most common problems: misconfigured heaps, inappropriately sized and allocated thread pools, and connection pools. After this I shared my general approach to tuning: the wait-based tuning methodology; wait-based tuning is nothing new and as a matter of fact Oracle 9i databases (and later) provide these metrics natively – in J2EE the waits still need to be inferred, but the approach is solid! I hope that analyzing your application server performance in this capacity will help you improve your application performance.