How do I decide the number of connections required in connection pooling?

Question

I am using hibernate 3.2.2 in my application. For connection pooling, we are using c3p0 0.9.1. I am using Generic DAO Pattern and Open Session in View pattern to do database operation.
We are working on new website of existing website. Right now, the no of visits is half million page visit in existing application. I am confused with the c3p0 configuration. At what benchmark, i decide the no of connection to be opened. max-connection, min-connection, idletime, timeout etc....

Answer 1

Profile your test instances, making small config changes and then finally verify with load testing.

Answer 2

You first need to determine what the pool will do if a request comes in and there is no free connection to serve it. Does it throw an exception? Return null? Block until another connection is returned to the pool?

Once you know what will happen when the capacity is exceeded, think about how you could deal with this in the calling code, and in what situations it is acceptable for this to happen. At some point as the number of connections increases you're going to have to start refusing to serve some requests, but only you can decide what that point is. The actual point depends on a lot of factors, including such things as

• your current idle and busy request rates
• the volatility of these rates (you want more "breathing room" if the rates jump around a lot)
• any future projections for rise in capacity compared with hardware improvements and developer time budgeted to revise this code (if you're planning to upgrade it in a couple of months, you need less overhead than if it's meant to keep running for a couple of years)
• any guarantees your company makes about processing capacity
• the ability of clients to comprehend "try again later" requests - e.g. if you know it's an automated script on the other end that sleeps for a minute on a 503 and tries again, that's better than a human getting a "capacity temporarily exceeded" message and both are much better than a batch script that gets a non-200 response and just quits with an error.
• the urgency of the requests - some requests (looking at pictures of kittens) can reasonably be delayed, but others (stock trading orders) may be very time-sensitive.

And so on and so forth.

Hopefully from the above you should be able to come up with the number of requests you need to be able to process concurrently (and if there are different types of request, you may need to take this into concern too). Then it's just a matter of looking at how the incoming requests acquire and use connections, reasoning and profiling until you discover the number of connections in the pool that is required to sustain your target rate of connections.

Don't forget to account for things like non-request threads (e.g. worker pools) getting their own connections from the same pool (pool needs to be bigger), as well as requests only holding a connection for part of their execution (pool can be smaller).