[Building Sakai] Sakai 2.7.0 and MSGCNTR 3.0

[Bryan Holladay]

Yup, it looks like your query is taking the longest path it could
take.  Here's the MRF_UNREAD_STATUS_I2 index:

CREATE INDEX MFR_UNREAD_STATUS_I2 USING BTREE
    ON MFR_UNREAD_STATUS_T(MESSAGE_C, USER_C, READ_C);

Thanks,
Bryan


On Wed, Jan 25, 2012 at 5:24 PM, Mike Jennings <mike_jennings at unc.edu> wrote:
> 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