[Zope-DB] Re: SQL Relay

[Kent Hoxsey]

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

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

> 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