[Building Sakai] Poor performance on forums

[Daniel Merino]

Hi Miguel,

from an earlier message of Bryan Holladay, I got this info to check the 
performance of Forums in a MySql database:


See which indices are being selected when you run this query:

Find Largest Forum usage site:

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


Then grab that site id and place it in this query:


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='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


In MySql you can also try to optimize the tables, though it did not help 
us in the past with slowness due to many rows. We just cleaned up the 
tables to make it faster.

I hope it helps. Good luck.

El 16/01/15 a las 08:58, Miguel Carro Pellicer escribió:
> Hi subscribers,
>
> One of my instances (Sakai 10.0) has serious performance problems in 
> the forums tool, i upgraded the MSGCNTR to 10.3 but the performance 
> problems still really serious.
>
> It takes minutes to access a forum or topic, it's really frustrating, 
> our MFR_MESSAGE_T table has 10K rows, IMO not so much :(
>
> Thanks for any guide, Miguel
>
> -- 
> Miguel Carro Pellicer
> Logo 	
>
> Miguel Carro 
> Pellicer<http://es.linkedin.com/pub/miguel-carro-pellicer/38/502/b92>
> /CTO Entornos de Formación S.L./
>
> Phone: +34 - 686266485
> Email: mcarro at entornosdeformacion.com 
> <mailto:mcarro at entornosdeformacion.com>
>
> No me imprimas si no es necesario. Protejamos el medio ambiente
>
>
> AVISO LEGAL: El contenido de este mensaje de correo electrónico, 
> incluidos los ficheros adjuntos, es confidencial y está protegido por 
> el artículo 18.3 de la Constitución Española, que garantiza el secreto 
> de las comunicaciones.
> Si usted recibe este mensaje por error, por favor póngase en contacto 
> con el remitente para informarle de este hecho, y no difunda su 
> contenido ni haga copias.
> *** Este mensaje ha sido verificado con herramientas de eliminación de 
> virus y contenido malicioso ***
> Este aviso legal ha sido incorporado automáticamente al mensaje.
>
>
>
> _______________________________________________
> 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"

-- 
Daniel Merino Echeverría
daniel.merino at unavarra.es
Gestor de E-learning - Centro Superior de Innovación Educativa.
Tfno: 948-168489 - Universidad Pública de Navarra.
--
No te preocupes si al crear un programa no funciona a la primera. Si 
todos lo hicieran, no tendrías trabajo. (Ley de Mosher de la ingeniería 
de software)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20150116/06109eb4/attachment.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/jpeg
Size: 8726 bytes
Desc: not available
Url : http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20150116/06109eb4/attachment.jpe 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/jpeg
Size: 1103 bytes
Desc: not available
Url : http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20150116/06109eb4/attachment-0001.jpe