Fog Creek Software
Discussion Board




Database Pooling - Is this required for Applicatio

There was a very interesting thread on database pooling wih regard to a college software project in the thread below.

I was wondering if database pooloing is a requirement for web based commercial applications?

i.e. can the connections be made only at when a database query is conducted? Will this negate the need for database pooling?

Magna Carta
Wednesday, July 14, 2004

every commercial web app that I've ever worked on that I can remember has required and implemented db connection pooling because its just so expensive to open a connection.

"can the connections be made only at when a database query is conducted?"

yes.

"Will this negate the need for database pooling?"

no.

I'd rewrite your post like this:

"[since] the connections [are] made [EVERY TIME a]  database query is conducted... [you should implement] database pooling [especially when you're dealing with a potentially global userbase]"

code monkey
Wednesday, July 14, 2004

It's possible to initiate a new connection whenever a query is run against the database from the web app, but it's generally not a good way to do things.

The problem with it is that opening a new connection to a database server takes an order of magnitude more time (and for some db's I think perhaps several orders of magnitude more time) than grabbing an available existing connection from a connection pool.  Things can thus clog up quickly if you're not maintaining a pool of connections. 

The idea is to open a set of connections just once (the operation that takes time), place them in the pool, then have them maintained as open even when they're not in use, so that they an already-existing connection will be available for quick use by the web app to access the db.

Herbert Sitz
Wednesday, July 14, 2004

This needs to be qualified though. A small guestbook or comment system on your web site probably doesn't demand connection pooling - though if it's available by all means use it.

Matthew Lock
Wednesday, July 14, 2004

Connection pooling is a problem if you're using a CGI application though, because there's no way to hold the connection.  You'll need to use a server extension of some sort to take advantage, and server extensions mean you need to be more careful of memory allocation.

My experiences with MySQL, SQLite and Berkeley DB indicate that their connections aren't too expensive to open on each instance.  If your database is expensive though, and you're using CGI, some variety of application server is going to be necessary.  That comes by way of being tedious.

Clay Dowling
Wednesday, July 14, 2004

Write FastCGI apps, not CGI apps.

http://www.fastcgi.com/

Essentially, you just turn your CGI into a program that loops, calling the 'accept' function to get the new request.  Open the connection before the loop.  Everything else pretty much stays the same.

I've previously served up database-backed web applications in the millions of dynamic pages/month range using it.  Among other things, you can run fastcgi apps on different servers than the web server, which allows for scaling.

Depending on the web server, you can configure how many copies of your app will run (preventing runaway loads under high hit rates), and things like automatically shutting down unneeded copies of your app.

I've been using it since 1997, so it's not exactly some newfangled fad.

Phillip J. Eby
Wednesday, July 14, 2004

*  Recent Topics

*  Fog Creek Home