[Building Sakai] MSGCNTR Forums mysql picks a slow query plan over a faster query plan
Bryan Holladay
holladay at longsight.com
Wed Sep 7 05:12:14 PDT 2011
Earle,
Could you send me both queries:
1) the slow query that Sakai uses (the actual SQL query, not the
hibernate query. You can get this by setting hibernate.show_sql=true
in the sakai.properties (DO NOT SET THIS IN PRD!!!) and watch the
logs).
2) your new join query to resolve this (actual SQL)
Then I can look at the query plans on my local machine to see how the
index's pan out.
Thanks,
Bryan
On Tue, Sep 6, 2011 at 5:48 PM, Earle Nietzel <earle.nietzel at gmail.com> wrote:
> Hi Bryan.
>
> I have included the create statements from our DB (see below inline).
>
> Everything matches up except we have a few additional Foreign Keys
> while you didn't mention the other tables I am assuming that they are
> fine.
>
> On the side I did a hbm2ddl and it seems that more indexes are
> definitely created according to hibernate.
>
> I realize this issue is a bit strange but I don't believe there to be
> a problem with sakai but rather mysql.
>
> So I suggested a workaround for mysql using a straight_join.
>
> I am not sure if that is the best idea or maybe rework the query so
> that mysql doesn't get into a situation of picking a bad query plan.
>
> Earle
>
> BTW We are using mysql-5.1.52-1.el6_0.1.x86_64
>
> On Tue, Sep 6, 2011 at 8:24 AM, Bryan Holladay <holladay at longsight.com> wrote:
>>
>> 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);
>
> | MFR_UNREAD_STATUS_T | CREATE TABLE `mfr_unread_status_t` (
> `ID` bigint(20) NOT NULL AUTO_INCREMENT,
> `VERSION` int(11) NOT NULL,
> `TOPIC_C` bigint(20) NOT NULL,
> `MESSAGE_C` bigint(20) NOT NULL,
> `USER_C` varchar(255) NOT NULL,
> `READ_C` bit(1) NOT NULL,
> PRIMARY KEY (`ID`),
> UNIQUE KEY `TOPIC_C` (`TOPIC_C`,`MESSAGE_C`,`USER_C`),
> KEY `MFR_UNREAD_STATUS_I2` (`MESSAGE_C`,`USER_C`,`READ_C`),
> KEY `MFR_UNREAD_STATUS_I1` (`TOPIC_C`,`MESSAGE_C`,`USER_C`,`READ_C`)
> USING BTREE
> ) ENGINE=InnoDB AUTO_INCREMENT=5750248 DEFAULT CHARSET=utf8 |
>
>
>>
>> ALTER TABLE MFR_MESSAGE_T
>> ADD CONSTRAINT FK80C1A316A2D0BE7B
>> FOREIGN KEY(surrogateKey)
>> REFERENCES MFR_TOPIC_T(ID);
>>
>
> | MFR_MESSAGE_T | CREATE TABLE `mfr_message_t` (
> `ID` bigint(20) NOT NULL AUTO_INCREMENT,
> `MESSAGE_DTYPE` varchar(2) NOT NULL,
> `VERSION` int(11) NOT NULL,
> `UUID` varchar(36) NOT NULL,
> `CREATED` datetime NOT NULL,
> `CREATED_BY` varchar(36) NOT NULL,
> `MODIFIED` datetime NOT NULL,
> `MODIFIED_BY` varchar(36) NOT NULL,
> `TITLE` varchar(255) NOT NULL,
> `BODY` text,
> `AUTHOR` varchar(255) NOT NULL,
> `HAS_ATTACHMENTS` bit(1) NOT NULL,
> `GRADEASSIGNMENTNAME` varchar(255) DEFAULT NULL,
> `LABEL` varchar(255) DEFAULT NULL,
> `IN_REPLY_TO` bigint(20) DEFAULT NULL,
> `TYPE_UUID` varchar(36) NOT NULL,
> `APPROVED` bit(1) DEFAULT NULL,
> `DRAFT` bit(1) NOT NULL,
> `surrogateKey` bigint(20) DEFAULT NULL,
> `EXTERNAL_EMAIL` bit(1) DEFAULT NULL,
> `EXTERNAL_EMAIL_ADDRESS` varchar(255) DEFAULT NULL,
> `RECIPIENTS_AS_TEXT` text,
> `DELETED` bit(1) NOT NULL DEFAULT b'0',
> `NUM_READERS` int(11) DEFAULT NULL,
> `THREADID` bigint(20) DEFAULT NULL,
> `LASTTHREADATE` datetime DEFAULT NULL,
> `LASTTHREAPOST` bigint(20) DEFAULT NULL,
> PRIMARY KEY (`ID`),
> KEY `FK80C1A31650339D56` (`IN_REPLY_TO`),
> KEY `FK80C1A316A2D0BE7B` (`surrogateKey`),
> KEY `MFR_MESSAGE_DELETED_I` (`DELETED`),
> KEY `MFR_MESSAGE_T_IN_REPLY_TO_I` (`IN_REPLY_TO`),
> KEY `mfrmessageindex2` (`CREATED_BY`),
> CONSTRAINT `FK80C1A31650339D56` FOREIGN KEY (`IN_REPLY_TO`)
> REFERENCES `mfr_message_t` (`ID`),
> CONSTRAINT `FK80C1A316A2D0BE7B` FOREIGN KEY (`surrogateKey`)
> REFERENCES `mfr_topic_t` (`ID`)
> ) ENGINE=InnoDB AUTO_INCREMENT=1196102 DEFAULT CHARSET=utf8 |
>
>
>>
>> ALTER TABLE MFR_TOPIC_T
>> ADD CONSTRAINT FK863DC0BE74C7E92B
>> FOREIGN KEY(of_surrogateKey)
>> REFERENCES MFR_OPEN_FORUM_T(ID);
>>
>
> | MFR_TOPIC_T | CREATE TABLE `mfr_topic_t` (
> `ID` bigint(20) NOT NULL AUTO_INCREMENT,
> `TOPIC_DTYPE` varchar(2) NOT NULL,
> `VERSION` int(11) NOT NULL,
> `UUID` varchar(36) NOT NULL,
> `CREATED` datetime NOT NULL,
> `CREATED_BY` varchar(36) NOT NULL,
> `MODIFIED` datetime NOT NULL,
> `MODIFIED_BY` varchar(36) NOT NULL,
> `DEFAULTASSIGNNAME` varchar(255) DEFAULT NULL,
> `TITLE` varchar(255) NOT NULL,
> `SHORT_DESCRIPTION` varchar(255) DEFAULT NULL,
> `EXTENDED_DESCRIPTION` text,
> `MODERATED` bit(1) NOT NULL,
> `MUTABLE` bit(1) NOT NULL,
> `SORT_INDEX` int(11) NOT NULL,
> `TYPE_UUID` varchar(36) NOT NULL,
> `of_surrogateKey` bigint(20) DEFAULT NULL,
> `pf_surrogateKey` bigint(20) DEFAULT NULL,
> `USER_ID` varchar(255) DEFAULT NULL,
> `CONTEXT_ID` varchar(36) DEFAULT NULL,
> `pt_surrogateKey` bigint(20) DEFAULT NULL,
> `LOCKED` bit(1) DEFAULT NULL,
> `DRAFT` bit(1) DEFAULT NULL,
> `CONFIDENTIAL_RESPONSES` bit(1) DEFAULT NULL,
> `MUST_RESPOND_BEFORE_READING` bit(1) DEFAULT NULL,
> `HOUR_BEFORE_RESPONSES_VISIBLE` int(11) DEFAULT NULL,
> `AUTO_MARK_THREADS_READ` bit(1) NOT NULL,
> `AVAILABILITY_RESTRICTED` bit(1) NOT NULL DEFAULT b'0',
> `AVAILABILITY` bit(1) NOT NULL DEFAULT b'1',
> `OPEN_DATE` datetime DEFAULT NULL,
> `CLOSE_DATE` datetime DEFAULT NULL,
> PRIMARY KEY (`ID`),
> KEY `FK863DC0BE82FAB29` (`pf_surrogateKey`),
> KEY `FK863DC0BEFF3B3AE9` (`pt_surrogateKey`),
> KEY `FK863DC0BE74C7E92B` (`of_surrogateKey`),
> KEY `MFR_TOPIC_T_UUID_I` (`UUID`),
> CONSTRAINT `FK863DC0BE74C7E92B` FOREIGN KEY (`of_surrogateKey`)
> REFERENCES `mfr_open_forum_t` (`ID`),
> CONSTRAINT `FK863DC0BE82FAB29` FOREIGN KEY (`pf_surrogateKey`)
> REFERENCES `mfr_private_forum_t` (`ID`),
> CONSTRAINT `FK863DC0BEFF3B3AE9` FOREIGN KEY (`pt_surrogateKey`)
> REFERENCES `mfr_topic_t` (`ID`)
> ) ENGINE=InnoDB AUTO_INCREMENT=574313 DEFAULT CHARSET=utf8 |
>
>
>>
>> ALTER TABLE MFR_OPEN_FORUM_T
>> ADD CONSTRAINT FKC17608478B5E2A2F
>> FOREIGN KEY(surrogateKey)
>> REFERENCES MFR_AREA_T(ID);
>>
>
> | MFR_OPEN_FORUM_T | CREATE TABLE `mfr_open_forum_t` (
> `ID` bigint(20) NOT NULL AUTO_INCREMENT,
> `FORUM_DTYPE` varchar(2) NOT NULL,
> `VERSION` int(11) NOT NULL,
> `UUID` varchar(36) NOT NULL,
> `CREATED` datetime NOT NULL,
> `CREATED_BY` varchar(36) NOT NULL,
> `MODIFIED` datetime NOT NULL,
> `MODIFIED_BY` varchar(36) NOT NULL,
> `DEFAULTASSIGNNAME` varchar(255) DEFAULT NULL,
> `TITLE` varchar(255) NOT NULL,
> `SHORT_DESCRIPTION` varchar(255) DEFAULT NULL,
> `EXTENDED_DESCRIPTION` text,
> `TYPE_UUID` varchar(36) NOT NULL,
> `SORT_INDEX` int(11) NOT NULL,
> `LOCKED` bit(1) NOT NULL,
> `DRAFT` bit(1) DEFAULT NULL,
> `surrogateKey` bigint(20) DEFAULT NULL,
> `MODERATED` bit(1) NOT NULL,
> `AUTO_MARK_THREADS_READ` bit(1) NOT NULL,
> `AVAILABILITY_RESTRICTED` bit(1) NOT NULL DEFAULT b'0',
> `AVAILABILITY` bit(1) NOT NULL DEFAULT b'1',
> `OPEN_DATE` datetime DEFAULT NULL,
> `CLOSE_DATE` datetime DEFAULT NULL,
> PRIMARY KEY (`ID`),
> KEY `FKC17608478B5E2A2F` (`surrogateKey`),
> CONSTRAINT `FKC17608478B5E2A2F` FOREIGN KEY (`surrogateKey`)
> REFERENCES `mfr_area_t` (`ID`)
> ) ENGINE=InnoDB AUTO_INCREMENT=17835 DEFAULT CHARSET=utf8 |
>
>
>>
>> ALTER TABLE MFR_AREA_T
>> ADD CONSTRAINT MFR_AREA_CONTEXT_UUID_UNIQUE
>> UNIQUE (CONTEXT_ID, TYPE_UUID);
>>
>
> | MFR_AREA_T | CREATE TABLE `mfr_area_t` (
> `ID` bigint(20) NOT NULL AUTO_INCREMENT,
> `VERSION` int(11) NOT NULL,
> `UUID` varchar(36) NOT NULL,
> `CREATED` datetime NOT NULL,
> `CREATED_BY` varchar(36) NOT NULL,
> `MODIFIED` datetime NOT NULL,
> `MODIFIED_BY` varchar(36) NOT NULL,
> `CONTEXT_ID` varchar(255) NOT NULL,
> `NAME` varchar(255) NOT NULL,
> `HIDDEN` bit(1) NOT NULL,
> `TYPE_UUID` varchar(36) NOT NULL,
> `ENABLED` bit(1) NOT NULL,
> `LOCKED` bit(1) NOT NULL,
> `MODERATED` bit(1) NOT NULL,
> `SENDEMAILOUT` bit(1) NOT NULL,
> `AUTO_MARK_THREADS_READ` bit(1) NOT NULL,
> `AVAILABILITY_RESTRICTED` bit(1) NOT NULL DEFAULT b'0',
> `AVAILABILITY` bit(1) NOT NULL DEFAULT b'1',
> `OPEN_DATE` datetime DEFAULT NULL,
> `CLOSE_DATE` datetime DEFAULT NULL,
> PRIMARY KEY (`ID`),
> UNIQUE KEY `MFR_AREA_CONTEXT_UUID_UNIQUE` (`CONTEXT_ID`,`TYPE_UUID`),
> KEY `CONTEXT_ID` (`CONTEXT_ID`(20))
> ) ENGINE=InnoDB AUTO_INCREMENT=12227 DEFAULT CHARSET=utf8 |
>
>
>>
>> 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