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

[Umberto Nicoletti]

Dieter Maurer wrote:
> Dario Lopez-Kästen wrote at 2003-6-6 09:39 +0200:
>  > 
>  > ----- Original Message ----- 
>  > From: "Dieter Maurer" <dieter@handshake.de>
>  > 
>  > 
>  > > Dario Lopez-Kästen wrote at 2003-6-4 23:10 +0200:
>  > >  > We have very similar problems. We have 4 threads. 3 of them are usually
>  > >  > constantly frozen (ie they have been on hold for like 70.000 seconds,
>  > 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 you
>  > 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 execute. I
>  > do not blaim this on Oracle, but reather the consultant that wrote the SQL.
> 
> 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
> side.
> 
>  > ...
>  > This has increased the stability of our application a lot.
>  > 
>  > However, there still seems to be problems with queries that take a long 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).
> 

That's possible, but then why JSP-JDBC does not block for the same 
amount of time, provided that both queries are the same and the hw/os 
identical?

umberto

> 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?
> 
> 
> Dieter