cx-oracle-users

[cx-oracle-users] DRCP connections not being returned to the pool

[David S. <ds...@ds...>]

We are seeing a strange issue where DRCP connections are not being returned
to the pool even after the connection is closed. My sample code (
http://pastebin.com/UB5cPtsZ) shows a really simple example where I create
more processes than I have connections in the pool. I expected the
connection to be available for reuse by the time the child process sleeps.
Then I would expect to see a stream of output and the processes would all
exit in about 10 seconds.

What I'm actually seeing is every process making a connection, but the
queries for processes above the number of connections in the pool block
until some of the processes die. So every 10 seconds or so a few more
queries run.

Does the process really need to die for the connection to be released?

Thanks!

-- 
David
blog: http://www.traceback.org
twitter: http://twitter.com/dstanek
www: http://dstanek.com

Re: [cx-oracle-users] DRCP connections not being returned to the pool

[Amaury F. d'A. <ama...@gm...>]

2013/3/21 David Stanek <ds...@ds...>

> We are seeing a strange issue where DRCP connections are not being
> returned to the pool even after the connection is closed. My sample code (
> http://pastebin.com/UB5cPtsZ) shows a really simple example where I
> create more processes than I have connections in the pool. I expected the
> connection to be available for reuse by the time the child process sleeps.
> Then I would expect to see a stream of output and the processes would all
> exit in about 10 seconds.
>
> What I'm actually seeing is every process making a connection, but the
> queries for processes above the number of connections in the pool block
> until some of the processes die. So every 10 seconds or so a few more
> queries run.
>
> Does the process really need to die for the connection to be released?
>

It has been a while, but I remember that at some point con.close() would
call OCISessionEnd but not OCIServerDetach.
Only the object destructor would call OCIServerDetach.
You could try a "del con" just before the sleep.

Looking at the HISTORY, this was fixed in version 5.1.1.
Are you using an older version?

-- 
Amaury Forgeot d'Arc

Re: [cx-oracle-users] DRCP connections not being returned to the pool

[David S. <ds...@ds...>]

Thanks for the quick reply. In a previous test I had a 'del cur' and 'del
con' right before the sleep and it still wasn't working.  I'm wondering now
if it's possibly because of the test.

I am using 5.1.1.  I did download the code from the SF website, but the
only call to OCIServerDetach is in Connection_Free.  Should it also be in
Connection_Close?



On Thu, Mar 21, 2013 at 3:57 PM, Amaury Forgeot d'Arc <ama...@gm...>wrote:

> 2013/3/21 David Stanek <ds...@ds...>
>
>> We are seeing a strange issue where DRCP connections are not being
>> returned to the pool even after the connection is closed. My sample code (
>> http://pastebin.com/UB5cPtsZ) shows a really simple example where I
>> create more processes than I have connections in the pool. I expected the
>> connection to be available for reuse by the time the child process sleeps.
>> Then I would expect to see a stream of output and the processes would all
>> exit in about 10 seconds.
>>
>> What I'm actually seeing is every process making a connection, but the
>> queries for processes above the number of connections in the pool block
>> until some of the processes die. So every 10 seconds or so a few more
>> queries run.
>>
>> Does the process really need to die for the connection to be released?
>>
>
> It has been a while, but I remember that at some point con.close() would
> call OCISessionEnd but not OCIServerDetach.
> Only the object destructor would call OCIServerDetach.
> You could try a "del con" just before the sleep.
>
> Looking at the HISTORY, this was fixed in version 5.1.1.
> Are you using an older version?
>
> --
> Amaury Forgeot d'Arc
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://p.sf.net/sfu/appdyn_d2d_mar
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>


-- 
David
blog: http://www.traceback.org
twitter: http://twitter.com/dstanek
www: http://dstanek.com

Re: [cx-oracle-users] DRCP connections not being returned to the pool

[David S. <ds...@ds...>]

Hmmm... It looks like I was looking at the 5.1 code.  I do see it in the
5.1.1 code.


On Thu, Mar 21, 2013 at 4:09 PM, David Stanek <ds...@ds...> wrote:

> Thanks for the quick reply. In a previous test I had a 'del cur' and 'del
> con' right before the sleep and it still wasn't working.  I'm wondering now
> if it's possibly because of the test.
>
> I am using 5.1.1.  I did download the code from the SF website, but the
> only call to OCIServerDetach is in Connection_Free.  Should it also be in
> Connection_Close?
>
>
>
> On Thu, Mar 21, 2013 at 3:57 PM, Amaury Forgeot d'Arc <ama...@gm...>wrote:
>
>> 2013/3/21 David Stanek <ds...@ds...>
>>
>>> We are seeing a strange issue where DRCP connections are not being
>>> returned to the pool even after the connection is closed. My sample code (
>>> http://pastebin.com/UB5cPtsZ) shows a really simple example where I
>>> create more processes than I have connections in the pool. I expected the
>>> connection to be available for reuse by the time the child process sleeps.
>>> Then I would expect to see a stream of output and the processes would all
>>> exit in about 10 seconds.
>>>
>>> What I'm actually seeing is every process making a connection, but the
>>> queries for processes above the number of connections in the pool block
>>> until some of the processes die. So every 10 seconds or so a few more
>>> queries run.
>>>
>>> Does the process really need to die for the connection to be released?
>>>
>>
>> It has been a while, but I remember that at some point con.close() would
>> call OCISessionEnd but not OCIServerDetach.
>> Only the object destructor would call OCIServerDetach.
>> You could try a "del con" just before the sleep.
>>
>> Looking at the HISTORY, this was fixed in version 5.1.1.
>> Are you using an older version?
>>
>> --
>> Amaury Forgeot d'Arc
>>
>> ------------------------------------------------------------------------------
>> Everyone hates slow websites. So do we.
>> Make your web apps faster with AppDynamics
>> Download AppDynamics Lite for free today:
>> http://p.sf.net/sfu/appdyn_d2d_mar
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>
>
> --
> David
> blog: http://www.traceback.org
> twitter: http://twitter.com/dstanek
> www: http://dstanek.com
>



-- 
David
blog: http://www.traceback.org
twitter: http://twitter.com/dstanek
www: http://dstanek.com

Re: [cx-oracle-users] DRCP connections not being returned to the pool

[Amaury F. d'A. <ama...@gm...>]

2013/3/21 David Stanek <ds...@ds...>

> Thanks for the quick reply. In a previous test I had a 'del cur' and 'del
> con' right before the sleep and it still wasn't working.  I'm wondering now
> if it's possibly because of the test.
>
> I am using 5.1.1.  I did download the code from the SF website, but the
> only call to OCIServerDetach is in Connection_Free. Should it also be in
> Connection_Close?
>

I just checked the 5.1.1 link on http://cx-oracle.sourceforge.net/ , and
OCIServerDetach is already in Connection_Close.
Anyway the diff is here:
http://cx-oracle.svn.sourceforge.net/viewvc/cx-oracle/trunk/Connection.c?r1=348&r2=355



-- 
Amaury Forgeot d'Arc

Re: [cx-oracle-users] DRCP connections not being returned to the pool

[Christopher J. <chr...@or...>]

On 03/21/2013 11:51 AM, David Stanek wrote:
> We are seeing a strange issue where DRCP connections are not being returned to the pool even after the connection is closed. My sample code (http://pastebin.com/UB5cPtsZ) shows a really simple example where I create more processes than I have connections
> in the pool. I expected the connection to be available for reuse by the time the child process sleeps. Then I would expect to see a stream of output and the processes would all exit in about 10 seconds.
>
> What I'm actually seeing is every process making a connection, but the queries for processes above the number of connections in the pool block until some of the processes die. So every 10 seconds or so a few more queries run.
>
> Does the process really need to die for the connection to be released?
>
> Thanks!

Try an explicit session pool:

   import os
   import time
   import cx_Oracle

   def do_connection():
       print 'starting do_connection ' + str(os.getpid())
       mypool = cx_Oracle.SessionPool(user=user,password=pw,dsn=dsn,min=1,max=2,increment=1)
       con = cx_Oracle.connect(user=user, password=pw, dsn=dsn, pool = mypool, cclass="blah", purity=cx_Oracle.ATTR_PURITY_SELF)
       cur = con.cursor()
       print 'Querying ' + str(os.getpid())
       cur.execute("select to_char(systimestamp) from dual")
       print cur.fetchall()
       cur.close()
       mypool.release(con)
       print 'Sleeping ' + str(os.getpid())
       time.sleep(30)
       print 'finishing do_connection ' + str(os.getpid())

   user = 'hr'
   pw = 'welcome'
   dsn = 'localhost/orcl:pooled'
   for x in range(100):
       pid = os.fork()
       if not pid:
	  do_connection()
	  os._exit(0)


Mod_python etc users can create the session pool outside the handler,
otherwise re-authentication occurs for every connect and
NUM_AUTHENTICATIONS will equal NUM_REQUESTS in V$CPOOL_CC_STATS.

Chris

-- 
chr...@or...  http://twitter.com/ghrd
Newly updated, free PHP & Oracle book:
http://www.oracle.com/technetwork/topics/php/underground-php-oracle-manual-098250.html

Re: [cx-oracle-users] DRCP connections not being returned to the pool

[David S. <ds...@ds...>]

On Thu, Mar 21, 2013 at 5:36 PM, Christopher Jones <
chr...@or...> wrote:

> Try an explicit session pool:


Thanks!  It looks like this does the trick.

-- 
David
blog: http://www.traceback.org
twitter: http://twitter.com/dstanek
www: http://dstanek.com

Re: [cx-oracle-users] DRCP connections not being returned to the pool

[Christopher J. <chr...@or...>]

On 03/21/2013 02:49 PM, David Stanek wrote:
> On Thu, Mar 21, 2013 at 5:36 PM, Christopher Jones <chr...@or... <mailto:chr...@or...>> wrote:
>
>     Try an explicit session pool:
>
>
> Thanks!  It looks like this does the trick.

That's good news.

I blogged about it at https://blogs.oracle.com/opal/entry/python_cx_oracle_and_oracle

Chris

-- 
chr...@or...  http://twitter.com/ghrd
Newly updated, free PHP & Oracle book:
http://www.oracle.com/technetwork/topics/php/underground-php-oracle-manual-098250.html