[Building Sakai] Sakai 2.7.1 performance issues

[Sam Ottenhoff]

  Hi Tom,

Yes, there are critical performance issues in MSGNCTR 2.7.0 and 2.7.1 
directly related to the query you posted to the list.  Permissions in 
Forums are complex as they allow custom permissions and default 
permissions.  The "OR" statement in the query you posted is the 
performance killer.  Enough of those queries queuing on your database 
server will cause Sakai to stop responding.

We have submitted a number of patches that have been merged into trunk 
and 2.8.x; the parent issue is here:

http://jira.sakaiproject.org/browse/MSGCNTR-361

The immediate performance issues will be solved by merging in the 
commits in sub-tasks 3,4, and 5.  You should also confirm that you have 
the index discussed in sub-task 1.  The fixes in sub-tasks 3,4, and 5 
break up the complex query into two individual, high-performing queries.

--Sam



On 9/25/2010 2:39 PM, Tom Hall wrote:
>    Good day everyone
>
> We are having some severe performance issues on our Sakai 2.7.1 system.
> We are using MySQL and Java 1.6
>
> The load averages on the database server are in the area of 10 (usually
> under 3) and all users no matter what site or tool are experiencing
> problems.  We had the same problem yesterday and a restart appeared to
> clear it, Today, a restart has helped but we are still  having problems.
>
> About the only thing we are seeing in a process list are queries like
>
> select count(*) as col_0_0_ from MFR_TOPIC_T topicimpl0_ inner join
> MFR_OPEN_FORUM_T openforumi2_ on
> topicimpl0_.of_surrogateKey=openforumi2_.ID inner join MFR_AREA_T
> areaimpl3_ on openforumi2_.surrogateKey=areaimpl3_.ID inner join
> MFR_MEMBERSHIP_ITEM_T membership4_ on
> topicimpl0_.ID=membership4_.t_surrogateKey, MFR_PERMISSION_LEVEL_T
> permission1_ where areaimpl3_.CONTEXT_ID='ADED4F32D02FW2010WWW001' and
> topicimpl0_.MODERATED=1 and (membership4_.NAME in ('Student' ,
> 'ADED4F32D02FW2010WWW001')) and permission1_.MODERATE_POSTINGS=1 and
> (permission1_.TYPE_UUID<>'6db07323-5783-4d8d-b744-2a988d91749d' and
> permission1_.NAME=membership4_.PERMISSION_LEVEL_NAME or
> membership4_.PERMISSION_LEVEL=permission1_.ID)
>
> These queries are taking long periods of time (>2 minutes).
>
> Has anyone seen anything like this in 2.7.1?
>
> Tom
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>
> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"