Mailing List Archive: Closing idle DCoracle2 Connections?

Closing idle DCoracle2 Connections?

Oct 20, 2005, 2:16 AM

Post #1 of 12 (4541 views)

Hello everyone,

As we start to use our zope-Ora connection more frequently our database
administrator complains that he sees a lot of idle conections.

I know theres "connection.close()" in DCOra2 but does anyone know of a
way to autmatically close a DCOra within Zope when idle soandso long? Or
any other way to adress such a problem?

I know that I wouldn't want the connection being closed from Oracles
side as I have read in the mailinglist that DCOracle would still think
the connection is open and generate an error.

Our Specs atm:
Oracle version strings:
* TNS for Solaris: : 9.2.0.5.0 (Production)
* PL/SQL : 9.2.0.5.0 (Production)
* NLSRTL : 9.2.0.5.0 (Production)
* Oracle9i Enterprise Edition : 9.2.0.5.0 (Production)
* DCOracle2: (cvs release) -- DCOracle2 1.103 (dco2: 1.137
-DORACLE8i -DORACLE9i -DUSEOBJECT -Dlinux -DNATIVENUMERIC=1 -D_REENTRANT
)

Thanks in Advance /Carsten

------------------------------------------------------------------------
Carsten Germer Deutsches Elektronen Synchrotron (Web-Office, IT)
phone: +49-40-8998-1661 Notkestr. 85
web: http://wof.desy.de 22607 Hamburg
e-mail: carsten.germer@desy.de Germany
------------------------------------------------------------------------
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Re: Closing idle DCoracle2 Connections? [ In reply to ]

dieter at handshake

Oct 20, 2005, 1:26 PM

Post #2 of 12 (4460 views)

Permalink

Germer, Carsten wrote at 2005-10-20 11:16 +0200:
> ...
>As we start to use our zope-Ora connection more frequently our database
>administrator complains that he sees a lot of idle conections.
>
>I know theres "connection.close()" in DCOra2 but does anyone know of a
>way to autmatically close a DCOra within Zope when idle soandso long? Or
>any other way to adress such a problem?

Modulo bugs, the connection will be closed when
the DA object is flushed from the ZODB cache.

The ZODB cache is controlled by a maximal size (number of objects)
and replaces objects with a least recently used policy.

Usually, there is not need for a sophisticated connection
management. Just do not create unnecessary DA instances.
Modulo bugs, the total number of needed connections
is number of DA objects times the number of worker threads.

If you see more open connections than this value, you see a bug.

Bugs should be fixed and not worked around with more complex
software (complex connection management).

>I know that I wouldn't want the connection being closed from Oracles
>side as I have read in the mailinglist that DCOracle would still think
>the connection is open and generate an error.

The DA catches OperationalErrors and reopens the connection.
It tries to do this transparently (though a bit wrong).

--
Dieter
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Re: Closing idle DCoracle2 Connections? [ In reply to ]

cnk+zope at caltech

Oct 20, 2005, 7:09 PM

Post #3 of 12 (4442 views)

Permalink

Quoting Germer, Carsten <carsten.germer@desy.de>:
> Hello everyone,
>
> As we start to use our zope-Ora connection more frequently our database
> administrator complains that he sees a lot of idle conections.

I am pretty sure there is a bug in DCOracle2. If you can, try using
DCOracle2 from the python prompt and close the connection. Now, can
you still talk to the database? It seemed to me that I could when I
tried that.

For my particular circumstance, this apparent leak in database
connections is slow enough that I just restart my Zope application
periodically. That clears the connections Oracle still thinks are
open. FYI just closing the database connection via the ZMI does not
release the idle connections Oracle still sees.

--
Cynthia Kiser

_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Re: Closing idle DCoracle2 Connections? [ In reply to ]

chris at simplistix

Oct 21, 2005, 8:25 AM

Post #4 of 12 (4447 views)

Permalink

Cynthia Kiser wrote:
> open. FYI just closing the database connection via the ZMI does not
> release the idle connections Oracle still sees.

Yes, that button actually does nothing ;-)

Chris

--
Simplistix - Content Management, Zope & Python Consulting
- http://www.simplistix.co.uk
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Re: Closing idle DCoracle2 Connections? [ In reply to ]

chris at simplistix

Oct 21, 2005, 8:27 AM

Post #5 of 12 (4450 views)

Permalink

Dieter Maurer wrote:
> Usually, there is not need for a sophisticated connection
> management. Just do not create unnecessary DA instances.

Yes, this is the most common mistake...

> Bugs should be fixed and not worked around with more complex
> software (complex connection management).

Well, it depends, there can be a need for lots of infrequently used DA's
connecting to the same database. Having these be able to share
connections can be useful.

>>I know that I wouldn't want the connection being closed from Oracles
>>side as I have read in the mailinglist that DCOracle would still think
>>the connection is open and generate an error.
>
> The DA catches OperationalErrors and reopens the connection.
> It tries to do this transparently (though a bit wrong).

Well, it doesn't succeed for "Not Connected" and several other salient
Oracle errors.

FWIW, if people can, I'd recommend moving to cxOracle. There's more life
there now :-(

Chris

--
Simplistix - Content Management, Zope & Python Consulting
- http://www.simplistix.co.uk
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Re: Closing idle DCoracle2 Connections? [ In reply to ]

dieter at handshake

Oct 21, 2005, 10:55 AM

Post #6 of 12 (4450 views)

Permalink

Cynthia Kiser wrote at 2005-10-20 19:09 -0700:
> ...
>I am pretty sure there is a bug in DCOracle2. If you can, try using
>DCOracle2 from the python prompt and close the connection. Now, can
>you still talk to the database? It seemed to me that I could when I
>tried that.
>
>For my particular circumstance, this apparent leak in database
>connections is slow enough that I just restart my Zope application
>periodically. That clears the connections Oracle still thinks are
>open. FYI just closing the database connection via the ZMI does not
>release the idle connections Oracle still sees.

It calls "close" on the connection -- but above, you tell us
that this does not really close...

And it can at most close its own connection (if this works),
there may be others in other ZODB caches (for different workers).
It is unable to reach them...

--
Dieter
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Re: Closing idle DCoracle2 Connections? [ In reply to ]

dieter at handshake

Oct 21, 2005, 11:01 AM

Post #7 of 12 (4453 views)

Permalink

Chris Withers wrote at 2005-10-21 16:27 +0100:
> ...
>Well, it depends, there can be a need for lots of infrequently used DA's
>connecting to the same database.

What prevents to replace these DA's by a single instance?

Looks much easier than a sophisticated connection pool management...

>Having these be able to share
>connections can be useful.

Some people perfer more complex solutions over simpler ones ...

> ...
>FWIW, if people can, I'd recommend moving to cxOracle. There's more life
>there now :-(

However, a colleague found out that for his (probably special kind
of) setup, "cxOracle" was much slower than DCOracle2 (by about
a factor of 4).

--
Dieter
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Re: Closing idle DCoracle2 Connections? [ In reply to ]

matt at bane

Oct 21, 2005, 5:39 PM

Post #8 of 12 (4456 views)

Permalink

>
> However, a colleague found out that for his (probably special kind
> of) setup, "cxOracle" was much slower than DCOracle2 (by about
> a factor of 4).

heh, well I like to write FAST code when I can ;) However, the Zope
DA/RDBMS machinery slows the db conection down enormously -- like 9x
if I recall. DCOracle2 is very fast at getting you to the Nth result
in a result set as long as you don't make it turn all the prior
results into python objects first (which is, sadly, the behavior of
the DA.)

What happens with Zope and DCOracle2 I think is when the DA
connection object is ghosted, it doesn't know about it, and thus
doesn't close its connection. There may be some grief that it also
doesn't close the right connection object (if I recall, there is an
actual close() method on the underlying C connection object, but I'm
not sure if the DA ever calls it). The close is implicit when the
connection object is destroyed, but usually there's so much stuff
loaded that *someone* has a reference to the connection.

However, the code is still geared around OCI8 and some Oracle 9 code,
its due for some refactoring in a big way to deal with the profusion
of handle-driven objects now much more prevalent in Oracle 10.
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Re: Closing idle DCoracle2 Connections? [ In reply to ]

dieter at handshake

Oct 22, 2005, 11:07 AM

Post #9 of 12 (4441 views)

Permalink

Matthew T. Kromer wrote at 2005-10-21 20:39 -0400:
> ...
>What happens with Zope and DCOracle2 I think is when the DA
>connection object is ghosted, it doesn't know about it, and thus
>doesn't close its connection.

Why not?

When the DA instance is ghosted, the "db" object is deleted
(last reference released).

In an optimal world, the deletion of the "db" object would release
the last reference to the DCOracle connection and this
would close the connection to Oracle before the memory
is released.

> ...
>The close is implicit when the
>connection object is destroyed, but usually there's so much stuff
>loaded that *someone* has a reference to the connection.

That's the bug.

There should be no references to DCOracle resources outside
the "db" object (at least, Zope's DA/ZSQL do not require
such references outside). Thus, when the "db" object goes away,
the "DCOracle" resources should, too.

>However, the code is still geared around OCI8 and some Oracle 9 code,
>its due for some refactoring in a big way to deal with the profusion
>of handle-driven objects now much more prevalent in Oracle 10.

--
Dieter
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

RE: Closing idle DCoracle2 Connections? [ In reply to ]

carsten.germer at desy

Oct 24, 2005, 4:32 AM

Post #10 of 12 (4450 views)

Permalink

Ok, that's a bit much for my question I guess...
I gather from all your mails that I can not "close" a database
connection periodically or on event from Zope (Script or whatever)?
Any hands-down clues what I can do to prevent db-conns from ideling
around?
/Carsten

>-----Original Message-----
>From: Dieter Maurer [mailto:dieter@handshake.de]
>Sent: Saturday, October 22, 2005 8:07 PM
>To: Matthew T. Kromer
>Cc: Chris Withers; Germer, Carsten; zope-db@zope.org
>Subject: Re: [Zope-DB] Closing idle DCoracle2 Connections?
>
>
>Matthew T. Kromer wrote at 2005-10-21 20:39 -0400:
>> ...
>>What happens with Zope and DCOracle2 I think is when the DA
>>connection object is ghosted, it doesn't know about it, and thus
>>doesn't close its connection.
>
>Why not?
>
> When the DA instance is ghosted, the "db" object is deleted
> (last reference released).
>
> In an optimal world, the deletion of the "db" object would release
> the last reference to the DCOracle connection and this
> would close the connection to Oracle before the memory
> is released.
>
>> ...
>>The close is implicit when the
>>connection object is destroyed, but usually there's so much stuff
>>loaded that *someone* has a reference to the connection.
>
>That's the bug.
>
>There should be no references to DCOracle resources outside
>the "db" object (at least, Zope's DA/ZSQL do not require
>such references outside). Thus, when the "db" object goes away,
>the "DCOracle" resources should, too.
>
>>However, the code is still geared around OCI8 and some Oracle
>9 code,
>>its due for some refactoring in a big way to deal with the profusion
>>of handle-driven objects now much more prevalent in Oracle 10.
>
>--
>Dieter
>
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Re: Closing idle DCoracle2 Connections? [ In reply to ]

chris at simplistix

Oct 27, 2005, 9:20 AM

Post #11 of 12 (4396 views)

Permalink

Germer, Carsten wrote:
> Ok, that's a bit much for my question I guess...
> I gather from all your mails that I can not "close" a database
> connection periodically or on event from Zope (Script or whatever)?
> Any hands-down clues what I can do to prevent db-conns from ideling
> around?

Fewer database instances, few threads to your Zope server.
...or dig out my branch of ZOracleDA and get it working ;-)

Chris

--
Simplistix - Content Management, Zope & Python Consulting
- http://www.simplistix.co.uk
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

RE: Closing idle DCoracle2 Connections? [ In reply to ]

dieter at handshake

Nov 1, 2005, 2:23 AM

Post #12 of 12 (4394 views)

Permalink

Germer, Carsten wrote at 2005-10-24 13:32 +0200:
>Ok, that's a bit much for my question I guess...
>I gather from all your mails that I can not "close" a database
>connection periodically or on event from Zope (Script or whatever)?

Modulo bugs, this happens automatically (when the DA object is flushed
from the ZODB cache). You can also flush explicitely. Locally,
by calling "obj._p_deactivate()" (affects only the current connection)
and globally via "Control_Panel" --> "Database management" --> "your database"
--> "Flush" (affects all objects in all connections).

However, apparently, there are bugs in the "DCOracle2/ZOracle" code.
Then, you will need to find and fix them...

--
Dieter
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db