[Deploying Sakai] database load

Tom Hall thall at brocku.ca
Thu Dec 2 06:36:15 PST 2010 

Hi Alan

We are at Sakai 2.7.0 and msgcntr 2.7.2.  We went down entirely due to 
the issue with msgcntr 2.7.1 at the end of September and I had to some 
emegency patching until 2.7.2 was released.

Tom


  On 12/2/2010 9:09 AM, Berg, Alan wrote:
> Hi Tom,
>
> There were performance issues for Message Center in 2.7 that was resolved in 2.7.1 , which version are you running?
>
> Alan
>
> Alan Berg
> QA Director - The Sakai Foundation
>
> Senior Developer / Quality Assurance
> Group Education and Research Services
> Central Computer Services
> University of Amsterdam
>
> http://home.uva.nl/a.m.berg
>
> ________________________________________
> From: production-bounces at collab.sakaiproject.org [production-bounces at collab.sakaiproject.org] on behalf of Tom Hall [thall at brocku.ca]
> Sent: 02 December 2010 15:04
> To: Stephen Marquard
> Cc: sakai; Berg, Alan
> Subject: Re: [Deploying Sakai] database load
>
> Hi Everyone,
>
> While I can't put quantifiy it, we are also seeing an increased load on
> the database server between 2.6 and 2.7.  We are actually seeing slight
> pauses in the system when load peaks.  We are using MySQL 5.0.51 (64
> bit) and did not change it as part of the 2.6 to 2.7 upgrade (in fact
> this is the same version we used for 2.5 as well).
>
> We are doing a 'show full processlist' every 30 seconds or so in an
> attempt to find out what is going on.  When peak loads occur the process
> list always contains several queries like:
>
> select count(1) from SAKAI_REALM_RL_FN,SAKAI_REALM force index
> (AK_SAKAI_REALM_ID) where SAKAI_REALM_RL_FN.REALM_KEY =
> SAKAI_REALM.REALM_KEY and  SAKAI_REALM.REALM_ID IN
> (x'2F736974652F534F4349325032324430324657323031304D41494E',x'2F757365722F6338316236323361306161643262383930393137363462653662346666313039',x'21757365722E74656D706C6174652E73747564656E74',x'21757365722E74656D706C617465',x'21736974652E68656C706572')
> and FUNCTION_KEY in (select FUNCTION_KEY from SAKAI_REALM_FUNCTION where
> FUNCTION_NAME = x'736974652E7669736974')  and (ROLE_KEY in (select
> ROLE_KEY from SAKAI_REALM_RL_GR where ACTIVE = '1' and USER_ID =
> x'6338316236323361306161643262383930393137363462653662346666313039'  and
> REALM_KEY in (select REALM_KEY from SAKAI_REALM where
> SAKAI_REALM.REALM_ID IN
> (x'2F736974652F534F4349325032324430324657323031304D41494E',x'2F757365722F6338316236323361306161643262383930393137363462653662346666313039',x'21757365722E74656D706C6174652E73747564656E74',x'21757365722E74656D706C617465',x'21736974652E68656C706572')))
> or ROLE_KEY in (select ROLE_KEY from SAKAI_REALM_ROLE where ROLE_NAME =
> '.anon') or ROLE_KEY in (select ROLE_KEY from SAKAI_REALM_ROLE where
> ROLE_NAME = '.auth')
>
> and often various queries involving messageforums (MFR_*) tables.
>
> Tom
>
> On 12/2/2010 7:21 AM, Stephen Marquard wrote:
>> BTW adding valves in Tomcat breaks UTF8 support, so it's good for
>> testing (in a non-i18n context) but not for production.
>>
>> There is support in the RequestFilter to get times per request. But
>> Chuck's observations are probably more to do with query volume going up
>> as a result of code changes somewhere.
>>
>> Cheers
>> Stephen
>>
>> Stephen Marquard, Learning Technologies Co-ordinator
>> Centre for Educational Technology, University of Cape Town
>> http://www.cet.uct.ac.za
>> Email/IM/XMPP: stephen.marquard at uct.ac.za
>> Phone: +27-21-650-5037 Cell: +27-83-500-5290
>>
>>
>>
>>>>> "Berg, Alan"<A.M.Berg at uva.nl>   12/2/2010 1:39 PM>>>
>> Hi all,
>>
>> Chris Kretler is the QA lead for performance for 2.8.  The Columbia QA
>> server is under constant low level load and the results are going to be
>> regularly analyzed.  If anyone wishes to preemptively involve themselves
>> in the 2.8 QA cycle please make contact.
>>
>> Chuck, have you got any usage stats. For example, if you add a valve in
>> the tomcat server.xml you can get response times.
>>
>>
>>    <Valve className="org.apache.catalina.valves.AccessLogValve"
>>                       directory="logs"
>>                       pattern='%h %l %u %t "%r" %s %b "%{User-Agent}i"
>> %T'
>>                       prefix="localhost_access_log." resolveHosts="false"
>> suffix=".txt"/>
>>
>>
>> I would advise looking at the slower tools to begin with, the MySQL
>> slow  query logs and the cache hit rates. Please Jira any defects
>> found.
>>
>> Alan
>>
>> Alan Berg
>> QA Director - The Sakai Foundation
>>
>> Senior Developer / Quality Assurance
>> Group Education and Research Services
>> Central Computer Services
>> University of Amsterdam
>>
>> http://home.uva.nl/a.m.berg
>>
>> ________________________________________
>> From: production-bounces at collab.sakaiproject.org
>> [production-bounces at collab.sakaiproject.org] on behalf of Charles
>> Hedrick [hedrick at rutgers.edu]
>> Sent: 01 December 2010 21:15
>> To: sakai
>> Subject: [Deploying Sakai] database load
>>
>> I've had a feeling that database load was heavier for 2.7 than 2.6. I
>> finally put together a script to clarify it. If you look at
>> https://sakai.rutgers.edu/stats/max.txt, you'll see for each day
>> values 1, 6 and 11 for that day, when the values are sorted largest
>> first. The numbers are CPU% on our database system. Our usage is going
>> up slighly, but not enough to explain what looks like a factor of 2
>> increase. We can survive that, but we can't survive another increase
>> like this.
>>
>> Note that we changed not only from 2.6 to 2.7 but from Mysql 4.1 to
>> 5.1. It's possible that it's mysql more than Sakai that is the issue.
>> Our mysql is CPU limited. There's very little I/O.
>>
>> _______________________________________________
>> 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"
>> _______________________________________________
>> 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"
>>
>>
>>
>>
>> ###
>> UNIVERSITY OF CAPE TOWN
>>
>> This e-mail is subject to the UCT ICT policies and e-mail disclaimer
>> published on our website at
>> http://www.uct.ac.za/about/policies/emaildisclaimer/ or obtainable from
>> +27 21 650 9111. This e-mail is intended only for the person(s) to whom
>> it is addressed. If the e-mail has reached you in error, please notify
>> the author. If you are not the intended recipient of the e-mail you may
>> not use, disclose, copy, redirect or print the content. If this e-mail
>> is not related to the business of UCT it is sent by the sender in the
>> sender's individual capacity.
>>
>> ###
>>
>> _______________________________________________
>> 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"
> _______________________________________________
> 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"

More information about the production mailing list