[Building Sakai] Poor performance on forums
Earle Nietzel
enietzel at anisakai.com
Fri Jan 16 06:16:13 PST 2015
See https://jira.sakaiproject.org/browse/SAK-24944
(might be the same issue you are seeing)
There is a patch in that JIRA that adds a mysql specific query
straight_join bypassing the optimizer (which generally is not a good idea)
but it fixes the issue where mysql's query optimizer makes a bad decision.
The only other way I know of is as Sam suggested to run optimize on the
tables regularly.
But in reality this is probably a bug with MySQL, would be nice to know if
any mariaDB users experience this?
Here is another reference:
http://stackoverflow.com/questions/512294/when-to-use-straight-join-with-mysql
-earle
On Fri, Jan 16, 2015 at 5:21 AM, Daniel Merino <daniel.merino at unavarra.es>
wrote:
> 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
>
> --
> [image: 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
>
> 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 listsakai-dev at collab.sakaiproject.orghttp://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)
>
>
> _______________________________________________
> sakai-dev mailing listsakai-dev at collab.sakaiproject.orghttp://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>
> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"
>
>
> --
> [image: 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
>
> 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)
>
> _______________________________________________
> 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"
>
--
earle,
asahi net int.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20150116/a5fac236/attachment.html
-------------- 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/a5fac236/attachment.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/a5fac236/attachment-0001.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/a5fac236/attachment-0002.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/a5fac236/attachment-0003.jpe
More information about the sakai-dev
mailing list