[Building Sakai] Message Forum Preformance Issue
Jennings, Michael
mike_jennings at unc.edu
Thu Aug 18 09:04:51 PDT 2011
Thanks for those Bryan, I will apply those as well and see what happens. We actually had the other ones already :D
==============================================================================
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:26 AM
To: Jennings, Michael
Cc: Mathieu Plourde; sakai-dev at collab.sakaiproject.org
Subject: Re: [Building Sakai] Message Forum Preformance Issue
Sorry... missed the 3 most important ones:
DROP INDEX MFR_UNREAD_STATUS_I2 ON MFR_UNREAD_STATUS_T; CREATE INDEX MFR_UNREAD_STATUS_I2 USING BTREE
ON MFR_UNREAD_STATUS_T(MESSAGE_C, USER_C, READ_C);
DROP INDEX MFR_UNREAD_STATUS_I1 ON MFR_UNREAD_STATUS_T; CREATE INDEX MFR_UNREAD_STATUS_I1 USING BTREE
ON MFR_UNREAD_STATUS_T(TOPIC_C, MESSAGE_C, USER_C, READ_C);
DROP INDEX PRIMARY ON MFR_UNREAD_STATUS_T; CREATE UNIQUE INDEX PRIMARY USING BTREE
ON MFR_UNREAD_STATUS_T(ID);
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"
>>
>
More information about the sakai-dev
mailing list