[Building Sakai] Poor performance on forums
Miguel Carro Pellicer
mcarro at entornosdeformacion.com
Fri Jan 16 06:35:16 PST 2015
Thank you so much Dani, Bryan and Earle, i will implement this
suggestions and give you the feedback
Regards :)
El 16/01/2015 a las 15:34, Bryan Holladay escribió:
> I was responding to Miguel's current situation. I think it's better to
> run those queries and fix the indices over restarting production and
> running experimental code in production. I'd be up for another round
> of discussions on SAK-24944.
>
> -Bryan
>
>
>
>
> On Fri, Jan 16, 2015 at 9:31 AM, Earle Nietzel <enietzel at anisakai.com
> <mailto:enietzel at anisakai.com>> wrote:
>
> If MySQL didn't make bad query plan decisions then agree SAK-24944
> is not needed... But that is not the reality with that query!
>
> Running ANALYZE or OPTIMIZE is a good idea but we see this same
> issue crop up over time and I am now thinking that answer is not
> acceptable any more.
>
> Optimal Solution:
> Change that query as MySQL has a bad history of choosing bad
> plans. (rewrite the query completely)
>
> Sub Optimal Solution:
> Continue using same query and add straight_join again see
> http://stackoverflow.com/questions/512294/when-to-use-straight-join-with-mysql
> for why this not a good idea.
>
> Now Unacceptable Solution:
> Tell people to run OPTIMIZE or ANALYZE on their db tables regularly...
>
>
>
> On Fri, Jan 16, 2015 at 9:20 AM, Bryan Holladay
> <holladay at longsight.com <mailto:holladay at longsight.com>> wrote:
>
> SAK-24944 should not be required and you should be able to fix
> this w/optimizing your tables and making sure you have the
> correct indices. What Daniel has said is correct and your
> query plan is showing that something is off since the first
> row has 6k+ rows scanned. The first thing you'll want to do is
> run these queries:
>
> 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;
>
>
>
> If that didn't fix it (e.g. the total # of rows scanned is <
> 100) then you should make sure you have the correct indices
> and no additional ones. You'll know if you have the right
> combination if your query plan follows the same order and
> selection as the example Daniel showed you. If you are still
> having problems, that means your indices are wrong. Usually
> it's once of the following that are missing or being ignored:
>
> 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);
>
>
> If you've added any indices that were not any of the above,
> then remove it.
>
> -Bryan
>
> On Fri, Jan 16, 2015 at 9:16 AM, Earle Nietzel
> <enietzel at anisakai.com <mailto:enietzel at anisakai.com>> wrote:
>
> 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
> <mailto: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
>>>>
>>>> --
>>>> 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 <mailto: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 <mailto:sakai-dev-unsubscribe at collab.sakaiproject.org> with a subject of "unsubscribe"
>>>
>>> --
>>> Daniel Merino Echeverría
>>> daniel.merino at unavarra.es
>>> <mailto: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 <mailto: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 <mailto:sakai-dev-unsubscribe at collab.sakaiproject.org> with a subject of "unsubscribe"
>>
>> --
>> 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
> <mailto: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
> <mailto: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
> <mailto:sakai-dev-unsubscribe at collab.sakaiproject.org>
> with a subject of "unsubscribe"
>
>
>
>
> --
> earle,
> asahi net int.
>
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> <mailto: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
> <mailto:sakai-dev-unsubscribe at collab.sakaiproject.org>
> with a subject of "unsubscribe"
>
>
>
>
>
> --
> earle,
> asahi net int.
>
>
>
>
> _______________________________________________
> 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"
--
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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20150116/ebf292ab/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/ebf292ab/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/ebf292ab/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/ebf292ab/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/ebf292ab/attachment-0003.jpe
-------------- next part --------------
A non-text attachment was scrubbed...
Name: entornos_logo.jpg
Type: image/jpeg
Size: 8726 bytes
Desc: not available
Url : http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20150116/ebf292ab/attachment.jpg
-------------- next part --------------
A non-text attachment was scrubbed...
Name: linkedin.jpg
Type: image/jpeg
Size: 1103 bytes
Desc: not available
Url : http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20150116/ebf292ab/attachment-0001.jpg
More information about the sakai-dev
mailing list