• Advertisement
Sign in to follow this  

[web] Why use a connection pool for connecting to the database on a webserver?

This topic is 3629 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

I'm writing a java servlet, and when connecting to the database I've noticed people are using connection pools. I don't understand why, since I thought you would only need one connection to the database, so why would you need a pool? Thanks.

Share this post


Link to post
Share on other sites
Advertisement
Logging into the database server can often take more time than the actual query (depending on the load and conditions). To keep HTTP response times down, keeping a pool of open queries is a common technique. A process that's handling a page render grabs an open connection, queries the database for some data, renders the page, spits it back at the user and returns the connection to the pool.

Share this post


Link to post
Share on other sites
I think UziMonkey means thats when making a request the time taken to connect can be longer than the time taken to query, therefore a connection pool keeps some connections open to decrease response times.
I don't actually know anything about connection pools, so I may be wrong.

Share this post


Link to post
Share on other sites
Yeah that makes sense,

but I was thinking with a webserver it would just have one single database connection which is always connected.

But then again since I'm using servlets theres probably some hidden threading going on that would be sending simultaneous sql queries that would probably break that ... I guess.

Share this post


Link to post
Share on other sites
A java servlet requires an application server to be running services or applications in the background which are waiting to be queried or run and are always available. Thus your connection pool would be an application running in the background that has already allocated connections and is waiting to process queries for you.

A common use for this is abstracting the data retrieval backend from your application to allow the data to be retrieved from different types of applications. For example, if you have a website and stand alone applications that need access to your companies data, they will have no clue as to whether the data they request is coming from files, databases, or other servers. The only thing they know is a common interface they can use to request the said data, and the application with the connection pool would know all the details on where to get it as quickly as possible.

On the other hand a typical web server is the opposite. Things (pages or cgi modules) start to run as they are requested from the web server (Apache for example), and are only running as long as they are needed to complete the requested task. The only condition in which this may differ is if you have a custom setup where your server is internally running applications already that your scripts interact with through IPC or by executing them directly from the scripts. But obviously there are many different possible configurations in cases like that depending on the environment and the needs of the application.

Share this post


Link to post
Share on other sites
Connection pools eliminate the overhead caused by opening and closing connections.
When you close a pooled connection, the pool won't close it. Instead it will remain idle until some other thread needs a connection.

The reason there isn't a single connection for the entire server is due to the fact Connection is not thread safe. Every time a request comes in a new thread is spawned to handle it. So you would have threads waiting to use that single connection. By using multiple, pooled connections they can run all at the same time.

Share this post


Link to post
Share on other sites
i think the core answer is that requests spend time waiting on io and keeping a connection open whilst doing that, requests could share a connection then but they would need to be synchronized and you would have to take into account things like tables that exist for only that connection.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement