[Building Sakai] MSGCNTR Forums mysql picks a slow query plan over a faster query plan
Bryan Holladay
holladay at longsight.com
Tue Sep 6 05:24:48 PDT 2011
What index's do you have set? This has a huge effect on query plans
and most people have too many index's set. Make sure you only have
these index's and then look at the query plan:
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);
On Fri, Sep 2, 2011 at 4:15 PM, Earle Nietzel <earle.nietzel at gmail.com> wrote:
> 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"
>>
>
>
> _______________________________________________
> 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"
>
More information about the sakai-dev
mailing list