[Building Sakai] Forums slow in 10.2

[Bryan Holladay]

More likely an index issue... Run the following:


1) First, get the largest forum site id:

select max(a), b from
(select count(*) a, mat.CONTEXT_ID b from MFR_MESSAGE_T mmt
left join MFR_TOPIC_T mtt on mtt.ID = mmt.surrogateKey
left join MFR_OPEN_FORUM_T moft on moft.ID = mtt.of_surrogateKey
left join MFR_AREA_T mat on mat.ID = moft.surrogateKey
where mmt.surrogateKey is not null and moft.surrogateKey is not null and
mtt.of_surrogateKey is not null
Group By mat.CONTEXT_ID
Order by a DESC) c


2) Next, plug in that site id to show the query plan (replace {SITEID}):

describe 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='{SITEID}'
and unreadstat1_.READ_C=1 and messageimp0_.DRAFT=0 and
messageimp0_.DELETED=0
group by unreadstat1_.USER_C;


3) 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



4) If your number of rows are in the 1000's, then you definitely have an
index issue. Make sure you have all the indexes listed in the above query
plan under "possible_keys" and make sure that the selected indexes are the
same as above in "keys" column.


Here's a list of the possible keys you may be missing:


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);



5) Run the following queries even if you don't add any new indices
(WARNING: these will lock your tables, so if you have a large database, the
forums tool will be unresponsive until they complete. For normal sized
databases, these only take a few seconds):


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;



There was work on a jira [1] that may help with bad query plans but that
work wasn't complete and didn't address all the slow queries, so the
indexes above are you best bet for now.


-Bryan Holladay

[1] https://jira.sakaiproject.org/browse/SAK-24944



On Tue, Mar 24, 2015 at 5:24 AM, David Wafula Wanyonyi <
DavidWafula.Wanyonyi at wits.ac.za> wrote:

>  Dear Team,
> Recently we have users experiencing extremely slow response with forums
> (some  posts take over 5 minutes before loading).
>
> We run 10.2 on Solaris 11 over 4 clusters, each with 8GB RAM, on Mysql.
> The forums install is default. Has anyone experienced  this ? Where would
> be the most obvious place to start looking.
> Many thanks,
> DAvid
>
>  This communication is intended for the addressee only. It is confidential. If you have received this communication in error, please notify us immediately and destroy the original message. You may not copy or disseminate this communication without the permission of the University. Only authorised signatories are competent to enter into agreements on behalf of the University and recipients are thus advised that the content of this message may not be legally binding on the University and may contain the personal views and opinions of the author, which are not necessarily the views and opinions of The University of the Witwatersrand, Johannesburg. All agreements between the University and outsiders are subject to South African Law unless the University agrees in writing to the contrary.
>
>
> _______________________________________________
> 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"
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20150324/1991e087/attachment.html