[Zope-DB] Re: SQL Relay

[Umberto Nicoletti]

Hi Kent,
I see you have not solved your problem yet....

I am posting to this newsgroup to support your (and my) findings:
there are definitely issues in the way Zope handles database queries.

Comments inline...

Kent Hoxsey wrote:
>>>>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.
> 
> 
>>That's an interesting view of "long time" :-)
> 
> 
> An Oracle database running on appropriate hardware for the load, with appropriate
> table structures, indexes, and partitioning, should be able to return arbitrarily large
> data sets at wire speed. Complex queries can take longer than the 10 or so seconds
> users are willing to wait, but it has been my experience that ad-hoc sql taking longer
> than that to begin returning rows is in need of restructuring, tuning, or another look
> at indexing. Or, the DBAs have not run statistics since the previous election...
> 
> For everyone's entertainment, the queries I am trying to provide service to are 
> poorly structured and partially inaccurate. The underlying database is inappropriately
> indexed, and would benefit from a few hours of DBA attention. All of these things may
> be within my abilities, but they are outside of my control. So I am trying to use Zope
> to insulate my users from the vagaries of my corporate IT support staff.
> 
> 
>>You have to be more specific about the kinds of queries you are running.
>>It is likely that they are imposing exclusive row locks on some of
>>the more important tables in your database.
> 
> 
> I have deep, specific knowledge of the queries I am running. Oracle does not
> impose row locks on tables when reading (select only), it is considered a cardinal
> sin these days for a database server to block on read locks. If I were enforcing
> consistent-read transaction isolation on my query, I would expect additional lag
> time as the system managed block copies to the redo logs for rows within my 
> query that were touched by other processes in the system.
> 
> However, I am not concerned about consistent reads, and so log activity is not
> an issue. But more to the point: I am completely comfortable with the issues
> of managing sql queries within Oracle. I do not have a problem with (or at least, 
> have no control over) the response speed for the database query.  I do have
> a problem with Zope's seeming inability to serve pages when more than one
> ZSQL method is running.
> 
> To re-state the problem: Zope will not serve an index_html request once I start
> a second ZSQL method running.
> 

That was exactly what I found: the whole Zope app is 'frozen'!

> 
>>In that case, the problem
>>is not related to Zope, but a question of database and query design.
> 
> 
> Emphatically: the database design is not at issue. Zope should be able to serve
> web pages to clients up to the capacity limits of the machine it runs upon, no
> matter what kind of processing is blocked in the background. If the scalability
> were dependent upon the number of running threads, I would be happy. If the
> scalability were based upon some other parameter, I would be happy. The 
> system could then be tuned. But this is not the case. Zope will run two ZSQL
> methods at one time, and that is all.
> 

In my case (see my previous posts) I had a rather large database 
(hundreds of thousands records) that was properly tuned and indexed and 
was (well, it still is) running on absolutely performant hw (Compaq 
Alphaserver with RAID arrays especially optimized for Oracle). The db is 
tuned by our DBA (Oracle Certified), so the issue is NOT the db.
Queries were painfully slow in Zope while in JSP they were much faster 
(see the measurements). Additionally in Zope they would block the whole 
application that would refuse to serve even static pages.

After seeing that we wanted to make sure that it was not Oracle and it 
was not Python, but it was ZOPE's fault, so we spent two days coding a 
small app in python and java that would put their different thread 
implementations to the test: the results were posted from Kent and are 
available below in this reply.
Basically what they say is that python runs threads almost as well as 
Java (actually without eating tens of MB...).
In this second case the db was Interbase Firebird 1.0, not Oracle, and 
the queries were exactly the same for both python and java.

So if it's not python and it's not Oracle, is it Zope?
And if it is Zope, what can we do to resolve this issue?

<rant>
I posted (and keep posting) all my findigs here because we have LOTS of 
apps running on Zope and is is VERY expensive to rewrite them all. We 
are NOT anti-zope, we just want to find the better way to deploy our 
apps, and if Zope does not provide the performance we need, then we 
ditch it and switch to Java Platform.
So please stop blaming the db, the programmer or the solar winds!
</rant>

> 
>>You would experience the same problem with any other software that
>>runs these queries in parallel.
> 
> 
> Untrue, and simple to disprove. I can start up far more than two sqlplus sessions
> on my zope machine, kick off the long running queries, and continue both to use
> the machine as well as to serve pages out of Zope.
> 
> More to the point, Umberto Nicoletti encountered this same problem, and devised
> a set of test programs to verify the Zope issue. His message to me follows:
> ==============
> 
> We wrote a simple application that connects to a database and executes 
> three stored procedures. The application can execute the procedures for 
> a number of times that can be specified on the command line and can 
> issue each call serialized or threaded.
> The application was written in both java and python to verify whether 
> the problem was with python threading itself or Zope.
> We found out that Python performs relative well compared to Java, as 
> shown by the numbers below.
> We are thus led to believe that the problem we both experience is 
> Zope-related, rather than Python related. Unfortunately this is not 
> really useful in finding a quick solution to the performance issues 
> affecting our apps.
> 
> I can post source code, if you want.
> 
> Results:
> 
> the number on the left indicates how many iterations or threads are 
> used. Each thread has itw own connection that is pre-opened.
> The number on the right indicates total execution time.
> The database is firebird on Linux.
> 
> JAVA
> 
> 20 serial (no thread) 8.6s
> 20 threads 8.1s
> 
> 40 serial (no thread) 21.6s
> 40 threads 15.8 s
> 
> PYTHON
> 
> 20 serial (no thread) 9.7s
> 20 threads 8.8s
> 
> 40 serial (no thread) 24.1s
> 40 threads 19.3s
> 
> Hope this is useful,
> Umberto

Plase help us out of this issue, the whole zope community will benefit 
from it!

Best regards,
Umberto