[Building Sakai] MSGCNTR Forums mysql picks a slow query plan over a faster query plan

[Sam Ottenhoff]

What version of MsgCntr?  Do you see the same issue on trunk MsgCntr?

Note that MsgCntr 2.7.0 has major performance issues and should not be run
in any type of serious production environment.

--Sam

On Fri, Sep 2, 2011 at 3:51 PM, Earle Nietzel <earle.nietzel at gmail.com>wrote:

> Hello everyone,
>
> Got a bit of an issue here with forums and a particular query that mysql is
> coming up with different query plans. Depending on which one it selects it
> can take a significant amount of time to complete.
>
> The hql in question (added from MSGCNTR-364):
>
>   <query
> name="findNumModeratedTopicsForSiteByUserByMembershipWithPermissionLevelName">
>     <![CDATA[select count(*) from
> org.sakaiproject.component.app.messageforums.dao.hibernate.TopicImpl as
> topic,
>
> org.sakaiproject.component.app.messageforums.dao.hibernate.PermissionLevelImpl
> as pl
>             join topic.openForum as forum
>             join forum.area as area
>             join topic.membershipItemSet as membershipItem
>             where area.contextId = :contextId and
>             topic.moderated = true and
>             membershipItem.name in ( :membershipList ) and
>             pl.moderatePostings = true and
>             (pl.typeUuid != :customTypeUuid and
>                pl.name = membershipItem.permissionLevelName)
>         ]]>
>   </query>
>
> Which then is compiled into this SQL:
>
> select count(*) as col_0_0_
> from MFR_TOPIC_T topicimpl0_
> inner join MFR_OPEN_FORUM_T openforumi2_ on
> topicimpl0_.of_surrogateKey=openforumi2_.ID
> inner join MFR_AREA_T areaimpl3_ on openforumi2_.surrogateKey=areaimpl3_.ID
>
> inner join MFR_MEMBERSHIP_ITEM_T membership4_ on
> topicimpl0_.ID=membership4_.t_surrogateKey,
> MFR_PERMISSION_LEVEL_T permission1_
> where areaimpl3_.CONTEXT_ID='517f8f28-f68e-42ee-b168-919a06f82229'
> and topicimpl0_.MODERATED=1
> and (membership4_.NAME in ('Student' , 'MATH_111L_114_11F'))
> and permission1_.MODERATE_POSTINGS=1
> and permission1_.TYPE_UUID!='00000000-0000-0000-7777-000000000000'
> and permission1_.NAME=membership4_.PERMISSION_LEVEL_NAME
>
> The efficient query plan looks like:
>
> +----+-------------+--------------+------+-------------------------------------------------+------------------------------+---------+-------------------------------------------+-------+--------------------------+
> | id | select_type | table        | type |
> possible_keys                                   |
> key                          | key_len |
> ref                                       | rows  | Extra
> |
>
> +----+-------------+--------------+------+-------------------------------------------------+------------------------------+---------+-------------------------------------------+-------+--------------------------+
> |  1 | SIMPLE      | areaimpl3_   | ref  |
> PRIMARY,MFR_AREA_CONTEXT_UUID_UNIQUE,CONTEXT_ID |
> MFR_AREA_CONTEXT_UUID_UNIQUE | 767     |
> const                                     |     2 | Using where; Using index
> |
> |  1 | SIMPLE      | openforumi2_ | ref  |
> PRIMARY,FKC17608478B5E2A2F                      |
> FKC17608478B5E2A2F           | 9       |
> ilearn.areaimpl3_.ID                      |     3 | Using where; Using index
> |
> |  1 | SIMPLE      | topicimpl0_  | ref  |
> PRIMARY,FK863DC0BE74C7E92B                      |
> FK863DC0BE74C7E92B           | 9       |
> ilearn.openforumi2_.ID                    |    35 | Using where
> |
> |  1 | SIMPLE      | membership4_ | ref  |
> FKE03761CB7DEF8466,MFRMEMITEM_COMPOSITE_IDX1    |
> FKE03761CB7DEF8466           | 9       |
> ilearn.topicimpl0_.ID                     |     3 | Using where
> |
> |  1 | SIMPLE      | permission1_ | ref  |
> MFRPERMLVL_COMPOSITE_IDX1                       |
> MFRPERMLVL_COMPOSITE_IDX1    | 152     |
> ilearn.membership4_.PERMISSION_LEVEL_NAME | 10167 | Using where
> |
>
> +----+-------------+--------------+------+-------------------------------------------------+------------------------------+---------+-------------------------------------------+-------+--------------------------+
>
> The problem occurs when mysql selects a query plan where tables
> membership4_ and permission1_ are not at the end but towards the top of the
> query plan (this query takes several minutes to run):
>
> +----+-------------+--------------+--------+-------------------------------------------------+---------------------------+---------+-------------------------------------------+-------+-------------+
> | id | select_type | table        | type   |
> possible_keys                                   | key
> | key_len | ref                                       | rows  | Extra
> |
>
> +----+-------------+--------------+--------+-------------------------------------------------+---------------------------+---------+-------------------------------------------+-------+-------------+
> |  1 | SIMPLE      | membership4_ | range  |
> FKE03761CB7DEF8466,MFRMEMITEM_COMPOSITE_IDX1    | MFRMEMITEM_COMPOSITE_IDX1
> | 767     |NULL                                       | 67305 | Using where
> |
> |  1 | SIMPLE      | permission1_ | ref    |
> MFRPERMLVL_NAME_IDX                             | MFRPERMLVL_NAME_IDX
> | 152     | ilearn.membership4_.PERMISSION_LEVEL_NAME | 22004 | Using where
> |
> |  1 | SIMPLE      | topicimpl0_  | eq_ref |
> PRIMARY,FK863DC0BE74C7E92B                      | PRIMARY
> | 8       | ilearn.membership4_.t_surrogateKey        |     1 | Using where
> |
> |  1 | SIMPLE      | openforumi2_ | eq_ref |
> PRIMARY,FKC17608478B5E2A2F                      | PRIMARY
> | 8       | ilearn.topicimpl0_.of_surrogateKey        |     1 | Using where
> |
> |  1 | SIMPLE      | areaimpl3_   | eq_ref |
> PRIMARY,MFR_AREA_CONTEXT_UUID_UNIQUE,CONTEXT_ID | PRIMARY
> | 8       | ilearn.openforumi2_.surrogateKey          |     1 | Using where
> |
>
> +----+-------------+--------------+--------+-------------------------------------------------+---------------------------+---------+-------------------------------------------+-------+-------------+
>
>
> At the moment the only way I've been able to make sure mysql doesn't select
> the slow query plan is to use straight_join but it appears this is specific
> to mysql and therefore not supported by hibernate.
>
> Do we need a to implement a native query specific for MySQL in this case?
>
> Here is some psuedo code to represent what I am thinking:
>
> HibernateCallback hcb = new HibernateCallback() {
>   public Object doInHibernate(Session session) throws HibernateException,
> SQLException {
>     if
> ("mysql".equals(serverConfigurationService.getString(vendor at org.sakaiproject.db.api.SqlService
> ))
>       Query q =
> session.createSQLQuery(MYSQL_QUERY_GET_NUM_MOD_TOPICS_WITH_MOD_PERM_BY_PERM_LEVEL_NAME);
>     else
>           Query q =
> session.getNamedQuery(QUERY_GET_NUM_MOD_TOPICS_WITH_MOD_PERM_BY_PERM_LEVEL_NAME);
>     q.setParameterList("membershipList", membershipList);
>     q.setParameter("contextId", getContextId(), Hibernate.STRING);
>     q.setParameter("customTypeUuid", typeManager.getCustomLevelType(),
> Hibernate.STRING);
>     return q.uniqueResult();
>   }
> };
>
> Ideas and thoughts most welcome,
> Earle
>
> _______________________________________________
> 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"
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20110902/66c5933e/attachment.html