how to choose maximum connection pool size?

Question

<property name="hibernateProperties">
  <props>           
    <prop key="hibernate.c3p0.max_size">?</prop>
  </props>
</property>

is it just a random number guess ? or are there any studies that suggest to use a particular range for a specific use case?

Answer 1

Well, assuming that you are talking about a production system, then "random number guess" is definitely not the answer. Actually, "random number guess" is never the answer for any sort of configuration in a production environment. Same goes for "just accepting the product's defaults".

Connection pool properties (such as max_size) depend on a few factors:

• Anticipated use. For example, if you are expecting a typical usage pattern of 10 concurrent users, with an occasional burst of 20 users, there is little point in setting the number 50 as a maximum size even if you think that your machine can handle it. While it seems harmless at first sight, you have to remember that database connections are an expensive resource and, sometimes, you might want to actually cap the usage to match your expectation, if only to put your own assumptions into test and getting to know the real, production-like, usage pattern of the system and to prevent your application from being a resource hog, potentially affecting other applications.

• Available resources. If you know (and that is easy to verify) that your database can only accept 30 connections at a time, then setting a number larger than 30 is senseless, regardless of the application's usage pattern.

• Application design. Is your application going to mainly use short-lived connections? long-lived connections? Are you setting up a timeout for JDBC calls, so your JDBC calls are time-limited to begin with? For example, there's a difference in how you would configure a connection pool when you know that you're setting a timeout of 30 seconds per operation, comparing to how you would define that pool knowing that you set a timeout of 2 minutes.

• Specific product considerations. I am not sure about c3p0, but certain containers that provide connection pooling mechanisms carry their own factors into the equation. If you are using the connection pooling functionality provided by a container, you should read that container's documentation to see whether the container's vendor has some insight with regards to configuring the connection pooling mechanism they provide you with.

... Just don't guesstimate a number.

... And don't just assume product's defaults.