[Deploying Sakai] Performance optimisation with Oracle

[Julian M. Morley]

On Wed, Apr 28, 2010 at 3:00 PM, Martin B. Smith <smithmb at ufl.edu> wrote:
> 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?

It certainly sounds like the expected behavior of RAC 10g trying to
write to the same table from two different nodes, doesn't it?

Remi, FWIW, I'd recommend either using 10g in primary/failover mode
(all queries go to one node, second node used only if the primary
fails) or upgrading to 11g if possible - 11g includes some
improvements for these kinds of write-contention issues in RAC. And
remember that RAC is for redundancy, not for performance. :-)

-- 
Julian M. Morley
CourseWork Systems Administrator
Stanford University