[Deploying Sakai] database load

Tom Hall thall at brocku.ca
Thu Dec 2 06:47:46 PST 2010 

Thanks David

I'll check it out.

Tom

On 12/2/2010 9:19 AM, David Horwitz wrote:
> I did alert the dev group about cachign realm grants:
>
> http://jira.sakaiproject.org/browse/KNL-600
>
> But got no feedback ....
>
> D
>
> On 12/02/2010 04:04 PM, Tom Hall wrote:
>> 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"
>>
> _______________________________________________
> 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