[Building Sakai] Message Forum Preformance Issue

[Sam Ottenhoff]

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>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
> Cell: (919) 442-8337
> E-mail: mike_jennings at unc.edu
>
>
> -----Original Message-----
> From: Bryan Holladay [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
> 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>
> 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
> >
> > Cell: (919) 442-8337
> >
> > E-mail: mike_jennings at unc.edu
> >
> >
> >
> > From: Mathieu Plourde [mailto:mathieu at udel.edu]
> > Sent: Thursday, August 18, 2011 11:13 AM
> > To: Jennings, Michael
> > Cc: 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
> > Office: 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>
> > 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
> >
> > Cell: (919) 442-8337
> >
> > E-mail: mike_jennings at unc.edu
> >
> >
> >
> > _______________________________________________
> > 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"
> >
> >
> >
> > _______________________________________________
> > 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"
> >
> _______________________________________________
> 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"
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20110818/354b82d4/attachment.html