Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Optimum Database Connection Pool Size

DZone's Guide to

Optimum Database Connection Pool Size

Want to figure out the best size for your connection pool? Here's how the math pans out, courtesy of some simple logic.

Free Resource

Finding a database that fits with a container-based deployment model can be frustrating. Learn what to look for in a Docker database

Long ago, I was working on a project where I dealt with a situation — what should be the optimum size of connection pool?

While I was aware that it had to be configured on the application server side (web sphere application server v.5.x, in this case), I did not know the optimum pool size.

So I started having a discussion with my peers to try to understand more about it. I received some input and one of them was good enough, backed by strong reasoning, for me to recommend it to the admin. So what was it?

It’s quite simple and straightforward — look for the maximum size of the web container thread pool.

What Is a Web Container Thread Pool?

This pool consists of a number of threads that an application server will create and use to process the requests concurrently. So, for example, if the thread pool size is 50 (not realistic figure), the maximum number of requests an application may process concurrently will be 50.

So, the connection pool size has to be lower than the maximum size of the web container thread pool. Does it look reasonable and logical?

Let’s Analyze It

Max size of web container thread pool: 50

Max size of connection pool size: 65

Now if there are 50 concurrent requests coming to our application server, only 50 connections will be consumed by those 50 requests. But we are still left with 15 more connections — a sheer waste of resource. So, keeping performance statistics in mind; the connection pool size should be lower than the maximum thread pool size.

It may be 30/35 in our case.

There is another factor/attribute that should be considered, connection timeout. This should be calculated by obtaining the average time a database operation takes in our application, plus a few seconds more (let’s say 5).

I believe this post will be quite enlightening for many, and I  hope to receive your input so that I may update my post accordingly.

I always believe that knowledge is free and should always be free.

When you're looking for a SQL database that can scale elastically, while still preserving ACID guarantees, you only have a few choices. Find out how these elastic SQL databases perform in thishead-to-head YCSB benchmark.

Topics:
database ,connection pool ,concurrency ,thread pool

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}