[Deploying Sakai] database load

Thu Dec 2 09:50:59 PST 2010

Thanks David,

I think this solution would be great for someone running one app server.
There would be an issue of the cluster-wide invalidation that you mentioned
for those running in a cluster. It would be *nice* if were able to spend
more time looking at either distributed caching [1] [2] or a better cluster
wide eventing mechanism. (rather than reading/writing from the events table)
[3] These have been discussed for years now, and looks like some recent
progress on both. Without (one/both) of those, improving caching in some
places (like the realm grants) seems like it would just trade a performance
improvement in one place for new security issues or performance decline in
another.

[1] http://jira.sakaiproject.org/browse/SAK-11913
[2]
http://confluence.sakaiproject.org/confluence/display/SAKDEV/Sakai+Cluster-wide+Caching
[3] http://jira.sakaiproject.org/browse/SAK-11021

On Thu, Dec 2, 2010 at 9:19 AM, David Horwitz <david.horwitz at uct.ac.za>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"
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/production/attachments/20101202/95272dca/attachment-0001.html 