[Zope-DB] Re: Zope-DCOracle2 vs Tomcat-JDBC performance

Dieter Maurer dieter@handshake.de
Fri, 6 Jun 2003 19:43:02 +0200

Dario Lopez-K=E4sten wrote at 2003-6-6 09:39 +0200:
 > ----- Original Message -----=20
 > From: "Dieter Maurer" <dieter@handshake.de>
 > > Dario Lopez-K=E4sten wrote at 2003-6-4 23:10 +0200:
 > >  > We have very similar problems. We have 4 threads. 3 of them are u=
 > >  > constantly frozen (ie they have been on hold for like 70.000 seco=
 > and so
 > >  > on), so Zope effetively is running in one thread.
 > >
 > > Can you check what these threads are doing?
 > > This may be possible with a thread sensitive debugger.
 > > Analyse the backtraces of the various threads.
 > hm... unfortunaltely, I really don't know how to do such a thing. Do y=
 > have any pointers to docs that explain those features?

The "SpinningZope" HowTo probably contains the necessary information.

 > ...
 > hm... we do have some badly written SQL that take a long time to execu=
te. I
 > do not blaim this on Oracle, but reather the consultant that wrote the=

Fine, but it might also be Oracle...

 > We have done the following to alleviate the problems:
 > * decrease the number of ZOracle Connection objects in use.
 >   We no have only one per Oracle Schema

The only effect of this should be less memory preasure on the server

 > ...
 > This has increased the stability of our application a lot.
 > However, there still seems to be problems with queries that take a lon=
g time
 > to execute. It seems that the DA sort of looses the connection to the
 > database and just sits there hanging.

More likely: the long running query blocks its thread until it finished.
When you have more of them, Zope will stop responding (because
all worker threads are waiting for database responses).

We have a problem with our firewall (between frontend and backend).
When the connection is idle for a long time (configurable, for us
about 30 minutes), it breaks the connection, apparently in a
way that out Postgres backend database does not recognize that
its connection is no longer available. The result is that
Postgres processes pile up until the maximal connection limit
is reached. From the out Postgres point of view, it looks similar
to what you describe for your Oracle client threads.
But, your queries should take really long for this to happen.

 > This happens on both ends, apparently
 > as we see oracle connectson that just sit there doing nothing.

That's what we see for our Postgres processes...

Maybe, similar effects?