[Building Sakai] Poor performance on forums
Bryan Holladay
holladay at longsight.com
Fri Jan 16 06:34:11 PST 2015
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>
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>
> 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>
>> 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> 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.
>>>
>>> _______________________________________________
>>> 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/8cc9ab13/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/8cc9ab13/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/8cc9ab13/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/8cc9ab13/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/8cc9ab13/attachment-0003.jpe
More information about the sakai-dev
mailing list