[Using Sakai] Problem with messageforum

[Bryan Holladay]

Msgcntr 2.7.x is missing a lot of performance improvements that have
been added to 2.8.x+ and is known to have some issues.  I would first
check your tables and indexes:

Here are some things to try:


If using mysql, run this:

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


next make sure you have the correct indexes (and no additional indexes
that could result in the wrong indexes being used):


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

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

CREATE UNIQUE INDEX PRIMARY USING BTREE
    ON MFR_UNREAD_STATUS_T(ID);

ALTER TABLE MFR_UNREAD_STATUS_T
    ADD CONSTRAINT TOPIC_C
    UNIQUE (TOPIC_C, MESSAGE_C, USER_C);

ALTER TABLE MFR_MESSAGE_T
    ADD CONSTRAINT FK80C1A316A2D0BE7B
    FOREIGN KEY(surrogateKey)
    REFERENCES MFR_TOPIC_T(ID);

ALTER TABLE MFR_TOPIC_T
    ADD CONSTRAINT FK863DC0BE74C7E92B
    FOREIGN KEY(of_surrogateKey)
    REFERENCES MFR_OPEN_FORUM_T(ID);

ALTER TABLE MFR_OPEN_FORUM_T
    ADD CONSTRAINT FKC17608478B5E2A2F
    FOREIGN KEY(surrogateKey)
    REFERENCES MFR_AREA_T(ID);


ALTER TABLE MFR_AREA_T
    ADD CONSTRAINT MFR_AREA_CONTEXT_UUID_UNIQUE
    UNIQUE (CONTEXT_ID, TYPE_UUID);


Next you want to see how your queries are using the indexes:

Use this query to see what indexes are being used:

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): (notice that there are not indexes returning
a ton of results)

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



The other issues are in the code and would be harder to merge and fix.
 Start with this and if the issue is still bad, then you'll need to
backport the performance jira's.

-Bryan


On Thu, Sep 6, 2012 at 7:54 AM, Neal Caidin
<nealcaidin at sakaifoundation.org> wrote:
> Hi Anders,
>
> I wonder if it could be related to this one -
> https://jira.sakaiproject.org/browse/MSGCNTR-683 ?
>
> Cheers,
>
> Neal Caidin
>
> Sakai CLE Community Coordinator
> nealcaidin at sakaifoundation.org
> Skype: nealkdin
> AIM: ncaidin at aol.com
>
>
>
>
> On Sep 6, 2012, at 5:31 AM, Anders Nordkvist <anders.nordqvist at his.se>
> wrote:
>
> Hello,
>
> We at Skövde University in Sweden have a site that’s distributed to all our
> new students (The University Library site). The site is automatically added
> to all students when a teacher creates a site in our course creator program
> for Sakai. The problem is that this site becomes very slow and if an
> instructor, or a student deletes a post in the forum he gets an error
> message (pic1). I get the same error as an admin and I don’t have more than
> one tab of Sakai open. This error is somewhat mentioned in  the somewhat old
> post https://jira.sakaiproject.org/browse/MSGCNTR-48 (its unresolved there)
> and also I suspect it somehow can have to do with the amount of students on
> the site but I don’t know how to solve it. I have looked through all the
> logs but cant find anything!
>
> Tested on both IE v.9 and Firefox 15.0 with the same error.
>
> We are using Sakai 2.7.x and msgcntr version 2.7.7.
>
>
> Regards
> Anders Nordkvist
> System administrator
> University Of Skövde
> Sweden
>
>
> <pic1.PNG>_______________________________________________
> sakai-user mailing list
> sakai-user at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-user
>
> TO UNSUBSCRIBE: send email to sakai-user-unsubscribe at collab.sakaiproject.org
> with a subject of "unsubscribe"
>
>
>
> _______________________________________________
> sakai-user mailing list
> sakai-user at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-user
>
> TO UNSUBSCRIBE: send email to sakai-user-unsubscribe at collab.sakaiproject.org
> with a subject of "unsubscribe"