Re: Default Connection Pooling

On Tue, Aug 09, 2005 at 03:12:43AM -0700, Shibu Chacko Manalil wrote:
> Hello Smullyan,
>   Sorry to disturb you again.

Hi Shibu --

Not all.  I'm cc-ing the list in case anyone else is using pooling and
running into problems, or not.

> I didn't find a test_pool2 in tests/test_base.py and also
> Instructions for running the tests are in tests/README.tests. I am
> using a PYDO-2.0b0 version from this website
> http://download.berlios.de/skunkweb/.

As I just checked in the test yesterday in response to your email, it
isn't in an old beta release, but in subversion, and you'd have to
check it out following the instructions on berlios:

  https://developer.berlios.de/svn/?group_id=2506

The module you'd want to check out is PyDO, so you'd do it this way:

  svn co svn://svn.berlios.de/skunkweb/trunk/PyDO 

> At the same time I found some progress, internal connection pooling
> is working . pydo.drivers.oracleconn.OracleDBI is creating one time,
> but the OracleConnection are creating as specified in the
> ConnectionPool for example 4 or 10.

>    But I am bit confused now, how/when it is creating/retreiving
> connection, For example If I set a pool max size is 4, For each
> refresh in browser creating a new Connection until 4 times, then the
> next refresh sometime it work, without creating new connection ,
> sometime it disply error connection pool is full or something
> simillar. If I put commit in any of the PYDO object it always using
> the same connection, i think that is fine. If I am not doing commit
> , the problem occur.
>  
> It will be helpful if you explain it ?

max_poolsize is the maximum number of connections that will be created
at once; if you reach that number, the pool won't ever grow to the
number, even temporarily.  If it is zero, there is no maximum.  It is
a hard limit, so I'd suggest either turning it off by making it zero
or using as large a number as you can afford.  keep_poolsize is the
maximum number of connections that will be kept persistently; if
max_poolsize is > 0, you want max_poolsize > keep_poolsize.  If max is
100 and keep is 5, then in a peak you may create up to a 100
connections, but all but five will be closed as soon as they are
committed or rolled back, or otherwise closed.

The behavior at the hard limit is that the pool will sleep, waiting
for a connection to become available; you can configure the sleep
interval and the number of retries.  If it can't get one after its
retries are used up, it will raise an error.  Writing all your code to
catch this sort of error isn't fun, but I don't see an alternative if
you must enforce limits of this kind in the pool.  That's why I'd use
max_poolsize=0 if at all possible.

The connections handed out by the pool, I should point out, are
returned to the pool when you are done with them -- either by the
invocation of their destructor, being closed, or committed and rolled
back.  (The real connection is hidden underneath a wrapper which
overloads __del__ and close but delegates everything else.)

Without seeking to understand the full context of your application, I
would suppose that by setting max_poolsize very low and refreshing the
browser 1+max_poolsize times, you are sometimes getting connection
reuse because the older thread has given up the old connection, and
sometimes not, and as a result a new connection has to be created.  I
suggest that in every request in which you get a connection you
dispose of it, either implicitly by calling commit() or rollback()
somewhere, or explicitly; waiting for it to be garbage-collected may
not be fast enough in this situation.  Of course, you could also be
sloppy and simply turn max_poolsize way up (or off) -- 4 does seem
awfully low anyway -- and garbage collection would periodically clean
out old connections and send them either to the pool or to perdition.

Cheers,

js


>  
> Jacob Smullyan <[email protected]> wrote:
> Hi Shibu -- 
> 
> As a first step to resolving your issue, I've checked in a test that
> tries to verify that a pool hands out a different connection in each
> thread when the all connections in other threads are in use. The test
> in question is test_pool2 in tests/test_base.py. Could you please run
> it and tell me if it works for you? 
> 
> Instructions for running the tests are in tests/README.tests. Once
> you have a config file set up, this will run just this specific test:
> 
> runtests.py -d oracle -p pool2
> 
> but running all the tests wouldn't be a bad idea!
> 
> I haven't studied all the code you supplied; if the new test works for
> you, then the pool is behaving as I expect it to, and the question
> would then be why it isn't behaving as your code expects. 
> 
> j
> 
> -- 
> Jacob Smullyan
> 
> 		
> ---------------------------------
>  Start your day with Yahoo! - make it your home page 
-- 
Jacob Smullyan

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFC+KYCuqamFyFXXLIRAl0GAKDPlSM5xWhkZQarbWrn540nVdx2rgCdFNcA
8n9bPfLspNMYv2XQIZFUUL4=
=6j2a
-----END PGP SIGNATURE-----