[Building Sakai] Sakai 2.7.0 and MSGCNTR 3.0

[Mike Jennings]

Brian,

Thanks so much for this query.  I ran this explain plan and got these 
results that I have exported into a html file.

It seems that I am missing or using the wrong indexes like you said.....

Thanks so much for your help... I will start looking at the indexes 
tomorrow and see if I can get them more in line with what you have....

Thanks,

Mike Jennings





On 1/25/2012 4:48 PM, Bryan Holladay wrote:
> It could be missing (or wrong) indexes... run an explain on this query
> (put the correct Context Id):
>
>
> explain select SQL_NO_CACHE unreadstat1_.USER_C as col_0_0_, count(*)
> as col_1_0_
> from MFR_MESSAGE_T messageimp0_
> inner join MFR_TOPIC_T topicimpl2_ on messageimp0_.surrogateKey=topicimpl2_.ID
> inner join MFR_OPEN_FORUM_T openforumi3_ on
> topicimpl2_.of_surrogateKey=openforumi3_.ID
> inner join MFR_AREA_T areaimpl4_ on
> openforumi3_.surrogateKey=areaimpl4_.ID, MFR_UNREAD_STATUS_T
> unreadstat1_
> where messageimp0_.ID=unreadstat1_.MESSAGE_C and
> areaimpl4_.CONTEXT_ID='554a0549-03b1-4c1b-82a9-75d7fc5ac2d5'
> and unreadstat1_.READ_C=1 and messageimp0_.DRAFT=0 and messageimp0_.DELETED=0
> group by unreadstat1_.USER_C;
>
>
>
>
> a good query plan should look like (taken from a DB with 5+ million
> mfr_unread_status rows):
>
>
>
>
> table id select_type type possible_keys key key_len ref rows Extra
>
> areaimpl4_ 1 SIMPLE ref PRIMARY,MFR_AREA_CONTEXT_UUID_UNIQUE
> MFR_AREA_CONTEXT_UUID_UNIQUE 767 const 1 Using where; Using index;
> Using temporary; Using filesort
>
> openforumi3_ 1 SIMPLE ref PRIMARY,FKC17608478B5E2A2F
> FKC17608478B5E2A2F 9 sakai28.areaimpl4_.ID 1 Using where; Using index
>
> topicimpl2_ 1 SIMPLE ref PRIMARY,FK863DC0BE74C7E92B FK863DC0BE74C7E92B
> 9 sakai28.openforumi3_.ID 31 Using where; Using index
>
> messageimp0_ 1 SIMPLE ref PRIMARY,FK80C1A316A2D0BE7B
> FK80C1A316A2D0BE7B 9 sakai28.topicimpl2_.ID 35 Using where
>
> unreadstat1_ 1 SIMPLE ref MFR_UNREAD_STATUS_I2 MFR_UNREAD_STATUS_I2 8
> sakai28.messageimp0_.ID 5 Using where; Using index
>
>
>
> Also, it is good to run optimize on large tables:
>
>
> optimize table MFR_MESSAGE_T
>
> optimize table MFR_AREA_T
>
> optimize table MFR_OPEN_FORUM_T
>
> optimize table MFR_TOPIC_T
>
> optimize table MFR_UNREAD_STATUS_T
>
>
> On Wed, Jan 25, 2012 at 4:30 PM, Mike Jennings<mike_jennings at unc.edu>  wrote:
>> Thanks for the help.  We are currently using msgcntr 2.7.5 and users are
>> reporting that the forums are very slow for them.  I was going to try and
>> upgrade to the latest release, and see if that would make things faster :D
>>
>> Thanks again for this information.
>>
>> Mike Jennings
>>
>>
>> On 1/25/2012 4:20 PM, Bryan Holladay wrote:
>>>
>>> You also need to add the jquery-latest in /webapps/library/...
>>>
>>> On Wed, Jan 25, 2012 at 3:58 PM, Matthew Jones<matthew at longsight.com>
>>>   wrote:
>>>>
>>>> Yea, you'd have to branch and revert the changes made to msgcntr
>>>> in SAK-16568 (r95924) [1] as that method only exists in the kernel for
>>>> 2.8.
>>>> And probably also update the properties in the root pom.xml for msgcntr
>>>> to
>>>> match the versions of the tools you're running.
>>>>
>>>> And of course run the appropriate conversion script(s) [2], but after
>>>> that
>>>> it should work. It is running at Michigan on 2.7.1, so it does eventually
>>>> work. ;)
>>>>
>>>> I think that was all that was changed.
>>>>
>>>> [1] https://source.sakaiproject.org/viewsvn?view=revision&revision=95924
>>>> [2]
>>>> https://source.sakaiproject.org/svn/msgcntr/branches/msgcntr-3.0.x/messageforums-hbm/src/sql/
>>>>
>>>>
>>>> On Wed, Jan 25, 2012 at 3:41 PM, Mike Jennings<mike_jennings at unc.edu>
>>>> wrote:
>>>>>
>>>>>
>>>>> I was wondering is it possible to upgrade msgcntr to version 3.0 if your
>>>>> are running Sakai 2.7.0?
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Mike
>>>>> --
>>>>>
>>>>>
>>>>> ==============================================================================
>>>>> Mike Jennings
>>>>> Teaching and Learning Developer
>>>>> University of North Carolina at Chapel Hill
>>>>>
>>>>> Office: (919) 843-5013
>>>>> Cell: (919) 698-3746
>>>>> 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"
>>
>>
>> --
>> ==============================================================================
>> Mike Jennings
>> Teaching and Learning Developer
>> University of North Carolina at Chapel Hill
>>
>> Office: (919) 843-5013
>> Cell: (919) 698-3746
>> E-mail: mike_jennings at unc.edu

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

Office: (919) 843-5013
Cell: (919) 698-3746
E-mail: mike_jennings at unc.edu
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20120125/f73c5448/attachment.html