[Deploying Sakai] Performance optimisation with Oracle

[Martin B. Smith]

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?

Shutting down everything but one node may help you isolate the problem
either way. What table engine held these tables when you were on MySQL?

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?

Hope this gives you some ideas --
 please do share if you find anything interesting :)
-- 
Martin B. Smith
smithmb at ufl.edu - (352) 273-1374
CNS/Open Systems Group
University of Florida

-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 5497 bytes
Desc: S/MIME Cryptographic Signature
Url : http://collab.sakaiproject.org/pipermail/production/attachments/20100428/98dfb68a/attachment.bin