[Building Sakai] MSGCNTR Forums mysql picks a slow query plan over a faster query plan
Earle Nietzel
earle.nietzel at gmail.com
Fri Sep 2 13:15:24 PDT 2011
Here is the version from the pom:
<name>Sakai Message Center Base POM</name>
<groupId>org.sakaiproject.msgcntr</groupId>
<version>2.8.0-rc04</version>
<artifactId>msgcntr</artifactId>
<packaging>pom</packaging>
Earle
On Fri, Sep 2, 2011 at 4:08 PM, Sam Ottenhoff <ottenhoff at longsight.com>wrote:
> 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/8c00b251/attachment.html
More information about the sakai-dev
mailing list