[Deploying Sakai] database load

Thu Dec 2 06:09:37 PST 2010

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"