[Zope] Oracle or PostGreSQL connection pool

[Jim Fulton]

Hannu Krosing wrote:
> 
> Michel Pelletier wrote:
> >
> > > -----Original Message-----
> > > From: Nemeth Miklos [mailto:nemeth@iqsoft.hu]
> > >
> > >
> > > Do the Oracle or PostgreSQL database adapters and the ZSQL technology
> > > support connection pooling?
> >
> > Oracle Yes, PostgreSQL is not written by DC so I don't know.  I suspect
> > yes.
> >
> > > If I create a connection object, supposedly it may only be used by a
> > > single thread.
> >
> > Not on Oracle.  The Oracle DA is fully concurrent.  Postgres, I don't
> > know.  Possible not.  Note that if a DA is not concurrent it's not
> > Zope's fault, it's usually because the client libraries to interface
> > that database are not concurrent.
> 
> I guess that what he meant is _not_ concurrency per se, but whetaher you
> can have multiple _connections_ with different transactions for each running
> thread.
> 
> So when I define a DA and I have 3 working threads running concurrently,
> will Zope automatically get a separate _connection_ for each thread,
> either form a pool or pre-allocated per thread ?

The short answer is yes.  

Here's a longer explanation.  Zope maintains a pool
of ZODB database connections.  Currently this pool size is
set to 7 (for non-version connections).  (There ought to be
a way to adjust this without hacking Python code, 
and there will be in 2.2.)  Practically speaking, 
the ZODB connection pool size is the lesser of the 
number of threads and 7. What does this have to do with
Oracle or Postgres connections?  

When you define a database connection object in Zope, 
there could be a copy of the connection object for each 
ZODB connection, depending on how heavily the connection 
object is used. Each copy of the connection object will 
have it's own connection to the underlying database.  
This means that there will be an RDBMS connection pool 
for each Zope database connection object. The size of 
this connection pool is the lesser of the number of
zope threads and the size of the ZODB connection pool. 
RDBMS connections will be added and removed from the connection 
pool based on need.

Consider the following example. Suppose we have a Zope process using
ZServer with the default thread/pool configuration and a single
ZOracleDA database connection object.  If the site is handling alot of 
requests that require access to Oracle, then there will probably be
4 oracle connections, since the default number of Zope threads is 4.
If the nuber of requests requiring Oracle support drops off, then 
will the number of Oracle connections.  In fact, the number of Oracle
connections could drop to zero. If the number of requests pick up, then
so will the number of Oracle connections.  This all happens automatically
as a by-product of the standard Zope management of ZODB database objects.

One caveate is that to get the benefit of connection pooling, the 
database adapter needs to be fully threaded. This is the case for Oracle.
I'm not sure what the statis is for the Postgres DA.

We plan to put together a more extensive explanation of this in the form
of a white paper or how to. I hope this explanation helps in the
mean time.

Jim

--
Jim Fulton           mailto:jim@digicool.com   Python Powered!        
Technical Director   (888) 344-4332            http://www.python.org  
Digital Creations    http://www.digicool.com   http://www.zope.org    

Under US Code Title 47, Sec.227(b)(1)(C), Sec.227(a)(2)(B) This email
address may not be added to any commercial mail list with out my
permission.  Violation of my privacy with advertising or SPAM will
result in a suit for a MINIMUM of $500 damages/incident, $1500 for
repeats.