[Zope-DB] Re: SQL Relay

[Kent Hoxsey]

Chris Withers wrote:

> I'm moving this onto the zope-db list since it's revelent there...

Relevent, but redundant. We've been through this discussion a couple times in the
past couple months.

><snip>
>> The problem is that some of the SQL runs for a long time (1-2 minutes). Obviously,
>> the load is on the database server, not the Zope server, so you would think the Zope
>> server would be able to handle an amazing number of these requests.

> Well, each Zope thread has to wait for the DB that it's accessing. Zope has 4 
> threads, by default, IIRC.

As noted elsewhere, the 2-ZSQL-executing problem is unrelated to the number of
Zope threads executing. Raising the number of threads (and the ZODB threads they
depend upon) does not change the number of ZSQL methods that can execute at
one time.

>> that turns out not to be the case. Once Zope starts running the second long ZSQL,
>> it effectively stops accepting requests until one of the two ZSQLs completes.

> Hmm, that sounds like something somewhere is blocking... What database adapters 
> does this happen with? What adapters does it not happen with?

My long-running queries go against very large tables in Oracle. I have encountered
this problem with the DCOracle DA, ZODBCDA, mxODBCDA, SQLRelayDA, and straight
python External Methods creating their own DCOracle database connections. As you
might infer from the above list, the problem is the same on Linux and Win2k.

>> I figured it was a threading issue, so I raised the number of threads for Zope

> Nope, it ain't that. Might well be your back end database...

There's nothing wrong with the back end database. The problem is that Zope won't serve
normal (non-ZSQL) pages once I've kicked off two ZSQL methods.

>What databases does it affect? Only Oracle?

The only interesting databases I work with are Oracle, where all my inventory and order
transactions live. All other databases I work with are too small (single-digit gigs) to have
long-running sql. Umberto might have had experience with large databases other than
Oracle...

I keep thinking I'll get a day or two to chase this issue down, but it hasn't happened yet.

Kent