Re: database connection pooling from mod_python app:
"1. Reused Transaction vs Connection Pool Leaks
It is critical that a persistent connection's transaction is *always*
completed - either with commit or rollback. If you forget to do this
then database operations during the next request that the process
handles will use the same transaction ... which could be disasterous.
A connection pool will only give out a connection that is 'closed' and,
typically, rollback() is called by the pool manager as a connection is
returned. This avoids the problem of transactions spanning multiple
requests. A common problem when using connection pools are connection
leaks - a connection is not closed and hence is never returned to the
pool - but IMHO this is less serious. I would rather see an application
hang waiting for a connection to become available than have corrupt data.
The reused transaction and connection leak problems are both programming
errors (use a finally block to avoid the problem) but it is easier to
spot a connection pool leak - set the maximum pool size to a low number
and hammer the application. You'll soon find out if there are any
problems . Add some logging to the pool implementation to record who
opens and closes connection and you can easily find offending code that
forgets to call close().
2. Apache forking
As Ian mentions, Apache creates multiple processes to handle requests. A
default installation of Apache 1.x on Debian has a MaxClients of 150,
i.e. 150 processes. If each one of those processes maintains a
persistent connection you can easily hit your database server's
connection limit. 150 is a *lot* of connections to keep open anyway but
many requests will not even need database access, i.e. images, CSS,
JavaScript etc.
Perhaps you've tuned your Apache config and set MaxClients to something
more approriate, 30 for instance. Even then, it's likely that the number
of simultaneous connections that are required is much lower than 30.
Matt Goodall, Pollenation Internet Ltd
w: http://www.pollenation.net
"
