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.
_______________________________________________ Zope-Dev maillist - [EMAIL PROTECTED] http://mail.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope )
