Re: database connection pooling

On Tue, Sep 06, 2005 at 12:18:02AM +0200, Charl P. Botha wrote:
> I'm currently working on a SkunkWeb application that's based on
> Oracle.  I have to work with the database directly in order to make
> use of some vendor-specific functionality.
> 
> I would appreciate any information on what you guys think about DB
> connection pooling.  How do you do this for SkunkWeb?  Is there some
> way of keeping for example a connection per SkunkWeb process around?

The latter is very possible; pooling, strictly speaking, isn't, if you
mean by pooling having a pool of connections that are shared by all
the skunkweb processes.  (Well, that can be done by keeping an
external pool that you connect to with lightweight connections --
pgpool and sqlrelay do something like this.)

But the normal SkunkWeb approach is to keep one db connection around
per process.  There are connection caching services in the skunkweb
distribution for oracle, postgresql, and mysql.  I have never used the
oracle service; it uses DCOracle, so if you want to use cx_oracle
you'll need to modify/clone it.

The functionality provided by these caching services is quite trivial
-- if you request a connection from them, they'll keep it around and
call rollback on them at the end of every request cycle.  (This is
important for a web application -- if you don't do this, any db error
breaks your web app until you restart.)  Some of them (I don't think
oracle) can run a test function on the connection before handing it
out, so that skunkweb can survive a database restart.

If you are using PyDO, in either version PyDO will cache the database
connections for you anyway.  Version 1 integrates with the
above-mentioned services, so you get the nice rollback hook; version 2
doesn't, so you don't, but a service that implements it should be
pretty simple to cook up. 

j

-- 
Jacob Smullyan


-------------------------------------------------------
SF.Net email is Sponsored by the Better Software Conference & EXPO
September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf