1

How would you go about dynamically configuring the maximum number of connections in a DB connection pool?

I've all but given up on using a "hard coded" (configuration file, but still) number of connections. Some of the time, more connections provide better performance. On other times, less connections do a better job. What measurement would you use to determine if you've opened too many connections and are actually hurting performance by it? Please keep in mind I can't just "stop the world" to run a performance test - I need something that I could my own query responses (of which I have no specific measurement - some are slow, some are fast, and I can't know in advance which is which) to determine.
(please note I'm using Java JDBC with underlying DataDirect drivers)

Is this approach used somewhere (and was it successful)? If not, how would you go about solving the "what is the optimal number of connections" when you have to support both Oracle and MS SQL, both for several versions and the queries vary wildly in nature (indexed lookup / non-indexed lookup / bulk data fetching / condition matching (indexed and non indexed, with and without wildcards))?

[I know this is similar to optimal-number-of-connections-in-connection-pool question, but I'm asking about dynamic configuration while he's asking about static one]

Community
  • 1
  • 1
Ran Biron
  • 6,317
  • 5
  • 37
  • 67

3 Answers3

0

If you queue users to wait for a free database connection, they are waiting on something unknown or artificially imposed.

If you let them through to the database, you'll at least find out what resource is being fought over (if any). For example, if it is disk I/O, you may move your files around to spread activity against more or different disks or buy some SSD or more memory for cache. But at least you know what your bottleneck is and can take steps to address it.

If there is some query or service hogging resource, you should look into resource manager to segregate/throttle those sessions.

You probably also want to close off unused sessions (so you may have a peak of 500 sessions at lunch, but drop that to 50 overnight when a few bigger batch jobs are running).

Gary Myers
  • 34,963
  • 3
  • 49
  • 74
  • not sure I understand. You're saying that I don't need a maximum size in the connection pool at all, just "close connection after being unused for X seconds" time? What about "sanity" size (real maximum to avoid application crash) - 500? 1k? more? – Ran Biron Dec 13 '10 at 06:12
0

You need free flowing connection pool which auto adjusts according to the load. So it should have:- 1) Min size: 0 2) Max size: as per ur DB configuration 3) increment by 1 if available connections are out of stock 4) abandon connection if it is idel for X (configured time) seconds 5) Connection pool should release the abandoned connections.

Witht this settings the connection pool should manage the number of connections based on the load dynamically.

Naishadh
  • 1
  • 1
    That's exactly what we're using. The question is - what values to assign to the minimal connections number? what value to assign to maximal connection number? – Ran Biron Feb 12 '11 at 07:27
0

closing to lack of interest. We ended up using a high maximal value and it didn't seem to bother the DB much.

Ran Biron
  • 6,317
  • 5
  • 37
  • 67