[Zope-DB] Re: SQL Relay

M.-A. Lemburg mal at lemburg.com
Fri Oct 31 12:31:16 EST 2003

Kent Hoxsey wrote:
>>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.

Interesting. In that case the Python interpreter lock looks like
the most probable cause. Note that the PIL blocks the Python interpreter
as a whole - regardless of whether other threads continue to run or not;
Python will simply not continue to evaluate any more byte-codes until
the PIL is released.

>>>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.

We are testing the mxODBC Zope DA using three or more parallel
queries against various backends (MySQL, SAP DB, SQL Server) and
have so far not noticed such a behaviour. The log files that are
generated by the DA in debug prove that ZSQL methods are in fact
executed in parallel.

>>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.

See above. Releasing the PIL must be done in the Python interface
to the underlying database driver at C level. It is very
easy to forget releasing it and sometimes the database interfaces
can play foul with the programmer in making otherwise trivial
APIs have a long I/O-time. Typical cases are lazy evaluations
or asking for the number of rows in a result set...

>>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.

Marc-Andre Lemburg

Professional Python Software directly from the Source  (#1, Oct 31 2003)
 >>> Python/Zope Products & Consulting ...         http://www.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::

More information about the Zope-DB mailing list