[Zope] SLQ-connection Pooling

[Dario Lopez-Kästen]

Hello!

I was being *very* unclear in what I meant previosuly. Here goes again:

The question is the following - does a Zope DA_connection object keep its
communication with the database open or not.

Short answer: YES! It seems that the obvious answer is yes, based on further
pondering of the question (and looking at the ZMI for the object :) and on
Bo's comments.

Longer answer & background:
The problem we are having is a variant of the same old problem I've always
had. Zope overloads our database server when we are requesting large
datasets out of it. In previous zope-versions, both the zope server and the
db-server would go near 100% CPU usage.

The problem is not new to us, but has re-appeared due to deployment and
usage of a new module of our zope-app. We have a fairly good idea of what
the problem is. It always appears when we have a ZSQL method that has to
return more than a 100 or so records. The symptoms are long waiting times,
big load on the DB server and frozen or stuck zope-process with heavily
increased memory usage. Earlier we have had to restart the DB server and the
zope server.

Using Zope 2.5.1 it seems that only the DB server goes skyrocking in CPU
usage. The Zope server is at normal CPU Usage, however it has stopped
responding to requests. Shutting down the zope process releases the grip
that zope has on the DB server and the DB server goes back to normal CPU
Usage under normal load.

We have a workaround, that I clumsliy described in my previous mail. We have
an external method that manages the connection to the db, execution of a
query and packaging of the result set. We use a PythonScript inside Zope to
act as a replacement for the ZSQL-method.

This solution has dramatically decreased both the execution time of the
query, the congestion at the db-server and the memory consumption on the
zope-server. The figures 1-3 seconds for the execution fo the query is for
the PS-ExtMeth solution. 30-120 seconds is for the solution using
ZSQL-methods. As an added benefit, using the PS-ExtMeth solution, we also
gain per-user connection and bind-variable functionality, none of which is
available from ZSQL methods.

So, what does this have to do with DA-Connection objects?

Well, I was thinking of whether it was more efficient to connect to the DB
server from the ExtMethod using the existing DA-connection object, or if it
was more efficient to just do a per-request connect-disconnect directly in
the ExtMethod.

After writing this mail and reading Bo's comments, it would seem that the
most efficient way in our particular setting would be to use the
connect-disconnect method, especially when (as in our case) the problems
arise because zope is unable to free the connection object after the query
has been executed.

Thanks to Bo for his comments.

Cheers,

/dario

- --------------------------------------------------------------------
Dario Lopez-Kästen, dario@ita.chalmers.se        IT Systems & Services
System Developer/System Administrator     Chalmers University of Tech.