[Zope-DB] Re: SQL Relay

[Kent Hoxsey]

> So if I understand you correctly, both queries are doing SELECTs only ?!

Correct. There is no indication that the blocking we see in Zope is due to any
sort of database issue. Intuitively, the 3-tier design we are using, with Zope
in as the tier 2 appserver, should allow us to continue to serve content pages
even while the database server processes long-running sql or complex analytics.

>> To re-state the problem: Zope will not serve an index_html request once I start
>> a second ZSQL method running.

> I've never seen such a problem with Zope before

I believe this is probably because very few people have any need to struggle with
such slow and ineffective SQL as I am saddled with. The problem is much harder
to see when executing efficient SQL, since the ZSQL method returns quickly enough
that the blocking of new requests appears to simply be a load factor.

> so this must
> be related to the settings on your database or the database interface
> you are using, e.g. there might be calls to C APIs that the underlying
> Oracle interface blocks when running the query and that the interface
> does not release the global Python interpreter lock for.

That was my initial theory, prior to talking with Umberto. I now believe
this hypothesis has been disproven. First fact: Umberto experienced the
same behavior using Firebird, a database with no commonality with Oracle
whatsoever. Second fact (or rather, observation): if the underlying libraries
were causing the blocking, I would expect the behavior to be thread-specific.
If that were the case, I would expect to be able to process more concurrent
queries by increasing the number of threads running in Zope. This is not
the case.

> I'd suggest you connect to the Zope process using gdb or a
> similar debugger and check where the threads are spending all
> their time.

That sounds like an excellent idea. I believe that is what I will do today.

> If there's something we could do in the mxODBC Zope DA to help
> prevent the problem, we'd be most happy to do so.

Cool. If it's DA related, that would resolve a number of issues for me, and
would probably help Umberto as well.

Kent