[Building Sakai] High db CPU Load in 2.9.2

[Paul Dagnall]

Hi
We're running 2.9.2 and we've been getting hit with some high database CPU
load. Performance has gotten pretty bad a couple times. I noticed this dev
list post<http://sakai-project-mail-list-archives.1343168.n2.nabble.com/Building-Sakai-Sakai-2-9-2-and-Kernel-1-3-2-problems-during-load-testing-td7591146.html#a7591300>and
also this Jira (
SAKAI-23841 <https://jira.sakaiproject.org/browse/SAK-23841>).

The behavior we see is that the system becomes very slow when under higher
than average load. We also are considering looking into
KNL-1060<https://jira.sakaiproject.org/browse/KNL-1060>.


The specific queries that are the CPU pigs are:

1.
select enrollment0_.ENROLLMENT_ID as ENROLLMENT1_32_,
enrollment0_.VERSION as VERSION32_,
enrollment0_.LAST_MODIFIED_BY as LAST3_32_,
enrollment0_.LAST_MODIFIED_DATE as LAST4_32_,
enrollment0_.CREATED_BY as CREATED5_32_,
enrollment0_.CREATED_DATE as CREATED6_32_,
enrollment0_.USER_ID as USER7_32_,
enrollment0_.STATUS as STATUS32_,
enrollment0_.CREDITS as CREDITS32_,
enrollment0_.GRADING_SCHEME as GRADING10_32_,
enrollment0_.DROPPED as DROPPED32_,
enrollment0_.ENROLLMENT_SET as ENROLLMENT12_32_
 from CM_ENROLLMENT_T enrollment0_, CM_ENROLLMENT_SET_T enrollment1_
 where enrollment0_.ENROLLMENT_SET=enrollment1_.ENROLLMENT_SET_ID
 and enrollment1_.ENTERPRISE_ID=:1

and

2.
SELECT 1 FROM SAKAI_REALM_RL_FN srrf, SAKAI_REALM_FUNCTION srf, (select
realm_key, role_key from SAKAI_REALM_RL_GR where ACTIVE = '1' and USER_ID =
:1
 union select -1 as realm_key, -1 as role_key from dual) srrg WHERE rownum
= 1 AND srrf.realm_key in (select realm_key from SAKAI_REALM where  SAKAI_R
EALM.REALM_ID IN (:2,:3,:4,:5,:6,:7,:8)) AND srrf.function_key =
srf.function_key AND srf.function_name = :9 AND ((srrf.role_key =
srrg.role_key AND s
rrg.realm_key in (select realm_key from SAKAI_REALM where
 SAKAI_REALM.REALM_ID IN (:10,:11,:12,:13,:14,:15,:16))) OR srrf.role_key
in (2,1))


I wanted to solicit your thoughts on this. Our plan moving forward is to:

   1. Set iframe.xframe.cachetime=0 in sakai.properties.
   2. Look into adding
SAKAI-23841<https://jira.sakaiproject.org/browse/SAK-23841>
   3. Try clicking the Clear Server Cache button when performance gets
   stinky.
   4. Upgrade to 2.9.3 so we can get
KNL-1060<https://jira.sakaiproject.org/browse/KNL-1060>
   .
   5. Become better load testers :)

Please, let me know if you have any suggestions for us.

Thank you!

Paul Dagnall
Application Developer & Administrator
University of Dayton
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20130903/d05e444b/attachment.html