[Building Sakai] Poor performance on forums
Earle Nietzel
enietzel at anisakai.com
Fri Jan 16 06:31:06 PST 2015
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/bc220fa6/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/bc220fa6/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/bc220fa6/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/bc220fa6/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/bc220fa6/attachment-0003.jpe
More information about the sakai-dev
mailing list