[Building Sakai] Message Forum Preformance Issue

Thu Aug 18 09:05:13 PDT 2011

This was my fear, I guess I need to start working on the upgrade now

Thanks,

Mike

==============================================================================
Mike Jennings
Teaching and Learning Developer
University of North Carolina at Chapel Hill

Office: (919) 843-5013
Cell: (919) 442-8337
E-mail: mike_jennings at unc.edu

From: Sam Ottenhoff [mailto:ottenhoff at longsight.com]
Sent: Thursday, August 18, 2011 11:32 AM
To: Jennings, Michael
Cc: Bryan Holladay; sakai-dev at collab.sakaiproject.org
Subject: Re: [Building Sakai] Message Forum Preformance Issue

The indexes aren't going to cut it........ you need to upgrade your code too.  My guess is that if you look through your MySQL slow query logs, you will see references to queries that are very slow because of the use of an "OR" that makes the query vastly more difficult for MySQL.  Indexes will help a little bit, but aren't going to cut it in a heavily-used production environment. You need to get off MsgCntr 2.7.0 if you are running in production with MySQL.

--Sam
On Thu, Aug 18, 2011 at 11:23 AM, Jennings, Michael <mike_jennings at unc.edu<mailto:mike_jennings at unc.edu>> wrote:
Bryan,

Thanks for sending those indexes to me.  I am going to give those a try first.  If this does not work then I am going to try a upgrade of the tool.  I hate to upgrade the tool right now in production, as students are coming back to Chapel Hill from summer right now and August/September will have a lot of initial load and other issues...  so we are trying to keep changes at a minimum.

Thanks,

Mike

==============================================================================
Mike Jennings
Teaching and Learning Developer
University of North Carolina at Chapel Hill

Office: (919) 843-5013<tel:%28919%29%20843-5013>
Cell: (919) 442-8337<tel:%28919%29%20442-8337>
E-mail: mike_jennings at unc.edu<mailto:mike_jennings at unc.edu>

-----Original Message-----
From: Bryan Holladay [mailto:holladay at longsight.com<mailto:holladay at longsight.com>]
Sent: Thursday, August 18, 2011 11:18 AM
To: Jennings, Michael
Cc: Mathieu Plourde; sakai-dev at collab.sakaiproject.org<mailto:sakai-dev at collab.sakaiproject.org>
Subject: Re: [Building Sakai] Message Forum Preformance Issue

Correct, a lot of the issues was with missing indexes (some from hibernate's auto ddl).  But there was also a lot of code changes to improve performance.  The missing indexes were added to the conversion scripts.  Here's an unofficial list:

You should not have any more than this, otherwise, the indexes can be ignored for the extra one's you've added (which happens a lot of times and destroy's performance)

ALTER TABLE MFR_UNREAD_STATUS_T
   DROP INDEX TOPIC_C;
ALTER TABLE MFR_UNREAD_STATUS_T
   ADD CONSTRAINT TOPIC_C
   UNIQUE (TOPIC_C, MESSAGE_C, USER_C);

ALTER TABLE MFR_MESSAGE_T
   DROP FOREIGN KEY FK80C1A316A2D0BE7B; ALTER TABLE MFR_MESSAGE_T
   ADD CONSTRAINT FK80C1A316A2D0BE7B
   FOREIGN KEY(surrogateKey)
   REFERENCES MFR_TOPIC_T(ID);

ALTER TABLE MFR_TOPIC_T
   DROP FOREIGN KEY FK863DC0BE74C7E92B; ALTER TABLE MFR_TOPIC_T
   ADD CONSTRAINT FK863DC0BE74C7E92B
   FOREIGN KEY(of_surrogateKey)
   REFERENCES MFR_OPEN_FORUM_T(ID);

ALTER TABLE MFR_OPEN_FORUM_T
   DROP FOREIGN KEY FKC17608478B5E2A2F; ALTER TABLE MFR_OPEN_FORUM_T
   ADD CONSTRAINT FKC17608478B5E2A2F
   FOREIGN KEY(surrogateKey)
   REFERENCES MFR_AREA_T(ID);

ALTER TABLE MFR_AREA_T
   DROP INDEX MFR_AREA_CONTEXT_UUID_UNIQUE; ALTER TABLE MFR_AREA_T
   ADD CONSTRAINT MFR_AREA_CONTEXT_UUID_UNIQUE
   UNIQUE (CONTEXT_ID, TYPE_UUID);

On Thu, Aug 18, 2011 at 11:14 AM, Jennings, Michael <mike_jennings at unc.edu<mailto:mike_jennings at unc.edu>> wrote:
> Mathieu,
>
>
>
> Thanks so much for passing this question on.  This has been driving
> our users crazy..
>
>
>
> Mike
>
>
>
> ======================================================================
> ========
>
> Mike Jennings
>
> Teaching and Learning Developer
>
> University of North Carolina at Chapel Hill
>
>
>
> Office: (919) 843-5013<tel:%28919%29%20843-5013>
>
> Cell: (919) 442-8337<tel:%28919%29%20442-8337>
>
> E-mail: mike_jennings at unc.edu<mailto:mike_jennings at unc.edu>
>
>
>
> From: Mathieu Plourde [mailto:mathieu at udel.edu<mailto:mathieu at udel.edu>]
> Sent: Thursday, August 18, 2011 11:13 AM
> To: Jennings, Michael
> Cc: sakai-dev at collab.sakaiproject.org<mailto:sakai-dev at collab.sakaiproject.org>; Hall, John
> Subject: Re: [Building Sakai] Message Forum Preformance Issue
>
>
>
> Hi Mike,
>
> We have experienced slowness in forums in the past. We have been able
> to pinpoint some of these by indexing some processes. I have CCed John
> Hall, our Sakai administrator, to explain some of the things we have
> done to speed up the queries.
>
> Mathieu
> ==================================
> Mathieu Plourde, MBA
> Project Leader, LMS/Educational Technologist IT Client Support &
> Services mathieu at udel.edu<mailto:mathieu at udel.edu>
> Office: 302-831-4060<tel:302-831-4060>
> ==================================
> IT Support Center: http://www.udel.edu/help Sakai at UD Support and
> Training: http://www.udel.edu/sakai/training
>
>
> On Thu, Aug 18, 2011 at 11:10 AM, Jennings, Michael
> <mike_jennings at unc.edu<mailto:mike_jennings at unc.edu>>
> wrote:
>
> Hi all,
>
>
>
> At UNC we are running Sakai 2.7.0 with MsgCntr 2.7.0, java 1.6, MySQL
> 5.5 enterprise edition...
>
>
>
> We are seeing really slow load times on a forum loads with large class
> enrollments.
>
>
>
> When I go to this site, as a administrator, then the forum loads up
> really quick, like in seconds, but the same forum will take 40 seconds
> +  to load up if I become user to be the instructor.
>
>
>
> Has anyone else experienced any issues like this.
>
>
>
> Thanks,
>
>
>
> Mike Jennings
>
>
>
> ======================================================================
> ========
>
> Mike Jennings
>
> Teaching and Learning Developer
>
> University of North Carolina at Chapel Hill
>
>
>
> Office: (919) 843-5013<tel:%28919%29%20843-5013>
>
> Cell: (919) 442-8337<tel:%28919%29%20442-8337>
>
> E-mail: mike_jennings at unc.edu<mailto:mike_jennings at unc.edu>
>
>
>
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org<mailto: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<mailto:sakai-dev-unsubscribe at collab.sakaiproject.org>
> with a subject of "unsubscribe"
>
>
>
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org<mailto: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<mailto:sakai-dev-unsubscribe at collab.sakaiproject.org>
> with a subject of "unsubscribe"
>
_______________________________________________
sakai-dev mailing list
sakai-dev at collab.sakaiproject.org<mailto: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<mailto:sakai-dev-unsubscribe at collab.sakaiproject.org> with a subject of "unsubscribe"

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20110818/9574b47d/attachment.html 