[Zope-dev] Zope threads on 2.7.0, python 2.3.3

[Dario Lopez-Kästen]

Heimo Laukkanen wrote:

>> Do you use any DA, nad have long running SQL queries?
> 
> 
> Yes, extensively. We use DCOracle2 that had to be patched to work with  
> UTF-8. And we combine it with badly scripted code too. We started to do  
> prototype by using own Archetypes storage layer that stores data from  
> content objects to the Oracle database in certain format - so every 
> access  to an object actually creates a load of queries to the Oracle 
> database.
> 
> Connections to the databse are handled by the Oracle adapter in Zope, 
> so  we have not done any own connections but rather just run queries 
> through  Zope adapter.

We use a lot of queries (minimum 10-15) for each request, accros around 
7 DA connected to various Oracle schemas. Most of the queries are 
fairsly simple, but there are a couple of queries that are often used 
that are just plain bad, however they are so bad that it is very 
difficult to fix them.

These queries generate a lot of load on the DB server (they are *very* 
bad SQL). So these tend to take a long tiem to respond.

>> We have no fix for this, but have resigned to do restarts every now 
>> and  then. We use Zope 2.5.1, Redhat 7.3
> 
> How did you debug or pinpoint the culprit to be DCOracle connection?

Gut feeling, doing some basic optimisation of the not-so complicated 
cases. I did receive a few tips on how to do real debugging from Matt, 
Dieter and others, but those tips assume a level of knowlegde that I do 
not posses yet - also I have until recently not had any hardware to do 
that kind of debugging on.

I just made some basic observations - some times Zope would stop 
responding and angry users would call, and I woudl find threads that had 
been running for more thatn 70000 seconds, while at the same time I 
could observe queries on the database that had been running for similar 
lenght of time, occasionally even blocking the database altoghther.

Some times it seemed like Zope wold "lose" the connection to the 
database (we still get that randomly from now and then) but I am not 
100% sure that this is only zope's fault - it may be that the database 
was under so much load that it "lost" the connection to zope, thus 
triggering some kidn of "wait for reply" loop on zope's side.

Chris withers has done some work in improving DCOracle2's connections 
and general bug-fixning. If you haven't used it, grab the latest 
DCOracle2 from cvs - it is much better.

We see this behaviour mostly under heavy load - many users accessing the 
database all the time. Using the latest DCOracle and improving parts of 
our code has removed a lot of the problems, however it believe it is 
still unclcear what the problem is - in our setting, my stance is that 
we have only cured the symptoms, not the real problems...

> Have you tried cx_Oracle?
> 

No I was not aware that they had a Zope adaptor.

/dario

-- 
-- -------------------------------------------------------------------
Dario Lopez-Kästen, IT Systems & Services Chalmers University of Tech.