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

[Umberto Nicoletti]

Hi Matthew,
good to hear from somebody...

Matthew T. Kromer wrote:
> Umberto Nicoletti wrote:
> 
>> OK, we'll try that that asap.
>>
>> Still this does not explain the perfomance gain when using query caching.
>> We ran the same test with query caching (settings: max 2000, cache 
>> 100, time 60) turned on and got MUCH better results ( only 7 times 
>> slower than Tomcat and average response time under 1 sec).
>> I would believe that those security check would be done also with 
>> cached results...
>>
>> Regards,
>> umberto
>>
>> Matthew T. Kromer wrote:
>>
>>> Umberto Nicoletti wrote:
>>>
>>>> Hi all,
>>>> we're proud users of Zope, but recently we ran into some performance
>>>> issues.
>>>>
>>>> We have some Zope applications that are database (Oracle) intensive and
>>>> recently experienced some *huge* slowdowns when under heavy usage. We
>>>> had no problems before because the usage was modest (under 5 concurrent
>>>> users).
>>>> Since this is SHOW STOPPER for us we decided to spend some time and 
>>>> investigate further. We took a server and installed Zope 2.6.1, 
>>>> python 2.1.3 and DCOracle2 1.3b on Suse
>>>> Linux 8.0. On the same server we installed jakarta tomcat 4.1.24 
>>>> with Sun JDK 1.4.1_02 and Oracle JDBC.
>>>>
>>>> On another server (identical HW and SW) we have an Oracle 8.1.7 
>>>> instance and so we ran some simple queries against it from Zope and 
>>>> Tomcat.
>>>>
>>>> Result is that with small pages in both size and number of records 
>>>> (under 5) displayed Zope is head to head with Tomcat.
>>>>
>>>> With larger pages (about 50 records displayed with no whatsoever html
>>>> tables or other embellishment) Tomcat is MUCH faster (see below).
>>>
>>>
>>>
>>>
>>>
>>> As Dieter pointed out, this is probably the security mechanim doing a 
>>> linearly increasing cost to check each result you want to display in 
>>> your ZPT.
>>>
>>> The "easiest" thing to do to effect a speedup is to put the 
>>> per-result-row renderer into an external method -- call it with your 
>>> query results, and let it format the HTML to be placed back into your 
>>> ZPT page.  This isn't necessarily elegant, but ought to do the job 
>>> for you.
>>>
>>> Alternatively, if you have tight control over your environment, you 
>>> CAN look at replacing the Zope Security Manager with one with checks 
>>> more optimized for your environment.  This isn't a trivial task, but 
>>> it can be done.
>>>
> 
> 
> Well, there are ways to trace DCOracle2's timing -- so you can look for 
> places where DCOracle2 is causing slowdowns.
> 

That's cool. Should I use the profiler to do this?

> However, the larger issue may simply be how many application threads you 
> have running, and how many database threads in Zope are configured.
> 
> It could be the case that by default you are launching 4 threads, and 
> each thread is entering Oracle and blocking -- and that you don't have 
> enough worker threads to continue to do work.
> 
> Try using the -t option to Zope's start to increase the number of 
> threads by a small amount (say, -t 8).

Be there, done that.
at this url you can find some more results about Zope performance with a 
variety of configurations. None of them showed any appreciable improvement.
http://www.zope.it/Members/mxaos/zopetom/talkback/1053942317/discussionitem_view

Unfortunately the post is in Italian, but please look at the numbers at 
the bottom, they should be rather self-explanatory. If you have ANY 
problem in understanding that please email me, and i'll fix it.
This is top priority for me now.

We tried other configurations with smaller values of t and pool_size 
with no luck.

> Note that Zope's security policy is still going to check access to every 
> element returned by a database adapter, whether or not it is cached.
> 
> 
That's what I supposed and Dieter confirmed.

Thanks to all of you helping me out...
Umberto