[Building Sakai] Sakai 2.7.0 and MSGCNTR 3.0

[Bryan Holladay]

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