[Building Sakai] High db CPU Load in 2.9.2

[Paul Dagnall]

Nevermind... Our DBA misspoke and we were not CPU bound. We're going to
study our thread-dumps and see what we learn.

Paul Dagnall
Application Developer & Administrator
University of Dayton


On Tue, Sep 3, 2013 at 10:43 AM, Paul Dagnall <pdagnall1 at udayton.edu> wrote:

> 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/fa00d9cb/attachment.html