Database Connection in Web Application

Question

The following are the different techniques we can use for database connection in a web application envrioment.

1. Application Context Database Connection: only one database connection shared among all requests.
2. Database Pooling: open a fixed number of database connection and share the connection among all requests.
3. New Database Connection per request: Open a connection for each request.

What are the advantage and disadvantage of these techniques? Which one should a developer use?

Answer 1

Option 1. Sharing a single database connection among all web users is bound to fail for one reason or another. One long running query and your entire server will grind to a halt. That's an hard and fast "no" for 99.9% of all modern applications, even non-web based applications.

Option 2. Connection pooling. Probably the 2nd most commonly used technique for a web app to connect to a DB. First, the benefits are extremely limited if the DB and the Pool/web app are on the same machine. The pool and the web app, however can easily exist on the same hardware. The benefit here is that a database connections are expensive to open AND to a lesser extent, expensive to keep open. Opening a connection requires CPU usage and memory allocation. Pooling connections can keep a few dozen connections available to be "attached to" nearly instantly. The memory will already be allocated and most of the setup work done already, so connecting and disconnecting from the pool is relatively cheep. Pools usually keep a certain number of connections open at all times and grow as traffic increases. In times of excessive traffic, pools typically queue connection requests so that the DB is not overloaded.. users at the back of the queue experience delays, but the system churns away and generally is less likely to grind to a halt due to lack of memory and disk swapping.

Option 3. New Database Connection per request. On a light to moderately utilized system, it's not a terrible option, and it's usually easy to upgrade to a pooler at a later date. Keep in mind, however, that each database connection (every page load) will require opening and closing a DB connection, which involved CPU and Memory Allocation. In practice, if your pages load quickly, your userbase is small and consistent, and your traffic is fairly consistent, it can work just fine. Many DEV, CAT and QA environments are connected directly without a pooler. The disadvantage is #1, there is absolutely no way to control connections to the DB. If queries hang, you can have hundreds of connections suddenly killing your DB and sometimes a reboot or restart of the DB is required to rectify the situation.

For example: You write 1 bad query that's on the homepage of your site, that causes it to take 3 seconds to run instead of .3 seconds. Eventually, your site that had 1-2 pages running at any one moment, now may bump up to 10-20. Now those 10-20 pages = 10-20 DB connections opening and closing constantly, with 10-20 being open on average. This problem will creep up, using more and more memory until you reach the connection limit (or worse, use up all memory and everythings now swapping). At this point, everything's ground to a halt.

Keep in mind that connections take up resources both on the DB and the app server/pooler. When your DB is paging to disk, most of the time, all hope is lost for a graceful recovery without resetting something-- Obviously it can happen, but without a code fix, you usually reboot to give yourself some more time until the problem inevitibly happens again, and hopefully by that time, you've found the bad query, or bad config and fixed it.

Option 2 gives you the most options. It's usually not a management headache, but if you're running it all on 1 machine, the benefits are limited. If you have at least 2 machines (app server and DB server), it's an easy solution that usually prevents many system overloads.

Answer 2

What about a corporate web application, where users stay connected all day, could I maintain one connection per user? That would be option four:

4. New DB connection per connected user: Open a New connection with the given user after a successfull login and stick it to the web session. Must be closed when the session gets invalidated (logout or timeout).