[Building Sakai] Poor performance on forums
Daniel Merino
daniel.merino at unavarra.es
Fri Jan 16 02:21:23 PST 2015
The recommended solution for us in that topic to solve a bad query plan
was to run the OPTIMIZE TABLE command on the related tables, but it did
not improve the performance. Perhaps it could work for you.
I'm afraid I don't know too much about databases to give you a better
answer. Maybe some DBA in this list can help you more.
Best regards.
El 16/01/15 a las 11:11, Miguel Carro Pellicer escribió:
> Thanks for your response Dani,
>
> The result of the second query is:
>
> +----+-------------+--------------+--------+-----------------------------------------------------------------------------+-----------------------+---------+-----------------------------------+------+----------------------------------------------+
> | id | select_type | table | type | possible_keys |
> key | key_len | ref |
> rows | Extra |
> +----+-------------+--------------+--------+-----------------------------------------------------------------------------+-----------------------+---------+-----------------------------------+------+----------------------------------------------+
> | 1 | SIMPLE | messageimp0_ | ref |
> PRIMARY,FK80C1A316A2D0BE7B,MFR_MESSAGE_PARENT_TOPIC_I,MFR_MESSAGE_DELETED_I
> | MFR_MESSAGE_DELETED_I | 1 | const
> | 6447 | Using where; Using temporary; Using filesort |
> | 1 | SIMPLE | topicimpl2_ | eq_ref |
> PRIMARY,FK863DC0BE74C7E92B,MFR_TOPIC_PARENT_I1 | PRIMARY
> | 8 | sakai.messageimp0_.surrogateKey | 1
> | |
> | 1 | SIMPLE | openforumi3_ | eq_ref |
> PRIMARY,FKC17608478B5E2A2F,MFR_OF_PARENT_BASEFORUM_I |
> PRIMARY | 8 | sakai.topicimpl2_.of_surrogateKey
> | 1 | |
> | 1 | SIMPLE | areaimpl4_ | eq_ref |
> PRIMARY,CONTEXT_ID,MFR_AREA_CONTEXT_I | PRIMARY |
> 8 | sakai.openforumi3_.surrogateKey | 1 | Using
> where |
> | 1 | SIMPLE | unreadstat1_ | ref | MFR_UNREAD_STATUS_I2 |
> MFR_UNREAD_STATUS_I2 | 8 | sakai.messageimp0_.ID
> | 6 | Using where; Using index |
> +----+-------------+--------------+--------+-----------------------------------------------------------------------------+-----------------------+---------+-----------------------------------+------+----------------------------------------------+
>
> I'm sure our query plan is not the optimal in terms of performance,
> any recommendation? We do not have too much data in that instance,
> that's why i'm surprised!
>
> If anybody needs more data let me know.
>
> Regards and thank you so much in advance.
>
> El 16/01/2015 a las 9:20, Daniel Merino escribió:
>> 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 tosakai-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)
>>
>>
>> _______________________________________________
>> sakai-dev mailing list
>> sakai-dev at collab.sakaiproject.org
>> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>>
>> TO UNSUBSCRIBE: send email tosakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"
>
> --
> 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.
>
--
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.
--
El dinero no hace la felicidad, la compra hecha. (Anónimo)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20150116/6210725e/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/6210725e/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/6210725e/attachment-0001.jpe
-------------- 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/6210725e/attachment-0002.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/6210725e/attachment-0003.jpe
More information about the sakai-dev
mailing list