[Deploying Sakai] Performance optimisation with Oracle

[Remi Saias]

Thanks Martin for the reply, and sorry for the delay, we have just 
started our 2nd pilot project this week!
My answers are inserted below.

-------- Message original --------
Sujet : Re: [Deploying Sakai] Performance optimisation with Oracle
De : Martin B. Smith <smithmb at ufl.edu>
Pour : production at collab.sakaiproject.org
Date : 2010-04-28 18:00
> On 4/28/2010 5:10 PM, Remi Saias wrote:
>   
>> Hello,
>>
>> We are running some load testing on one of our future Sakai 2.6.1 
>> production server and our DBA noticed a problem he described as 
>> "contention with frequent inserts and updates on 2 tables: sakai_session 
>> and sakai_presence which causes a lot of "CPU + Wait for CPU" wait events".
>>
>>  From my point of view I see that our current performance is at least 2 
>> times slower than what we achieved on our previous pilot instance which 
>> was running with mySQL and even more annoying is that the performance 
>> (in transaction per second) is quickly decreasing when I raise the 
>> number of simulated students in my tests (using The Grinder).
>>
>> We are using Oracle 10g in a RAC architecture. Anybody have encountered 
>> such problems or have any clue to how we could improve this situation?
>>
>> I can provide a lot more details if required! :-)
>>
>> Thanks in advance!
>> Remi Saias for the Sakai/OpenSyllabus team at HEC Montreal
>>
>>     
>
> Hi Remi,
>
> Can you tell if this is contention between RAC nodes for the blocks that
> comprise those 2 tables, or if you're seeing contention even when a
> single RAC node is running by itself?
>   
We saw contention even when running in single node.
> Shutting down everything but one node may help you isolate the problem
> either way.
We have just configured new instances of Sakai and Oracle dedicated to 
performance testing on the same
machine. This should help isolate the problem.
> What table engine held these tables when you were on MySQL?
>   
We were using the InnoDB engine.
> Also, are you using any query hints like parallel or append? Have you
> turned on sql_trace for one of these sessions to see exactly how long
> these inserts are spending doing work? Tried an explain plan on the
> problematic statements?
>   
To all these questions: not yet! :-) But these seems very interesting 
ideas. Thanks!
> Hope this gives you some ideas --
>  please do share if you find anything interesting :)
>   
Will do!
> ------------------------------------------------------------------------
>
> _______________________________________________
> production mailing list
> production at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/production
>
> TO UNSUBSCRIBE: send email to production-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/production/attachments/20100505/be354a44/attachment.html