[Building Sakai] MSGCNTR Forums mysql picks a slow query plan over a faster query plan
Earle Nietzel
earle.nietzel at gmail.com
Wed Sep 7 13:41:37 PDT 2011
Thank Bryan for checking on that.
The goal there was to come up with the same query plan that MySQL selects on
its own when it is working fine (which it looks like we did).
The problem is that MySQL has been selecting a dodgy query plan which then
kills the database server. The plans we were just discussing are what we
expect (and want) to run. If you look at one of my previous posts you will
see the plan that is bad and undesired. This where the straight_join comes
in and says I will always run it this way no matter what.
So I am working up a patch to do this.
Can you look at my psuedo code from my first post and let me know if that
looks like I am going in the right direction?
Thanks,
Earle
On Wed, Sep 7, 2011 at 4:20 PM, Bryan Holladay <holladay at longsight.com>wrote:
> Yup... it gives the same result as whats in Msgcntr right now. It
> looks like the only difference is you reordered the joins and added
> "straight_join" (which only works for MySQL correct?). Anyways, I
> don't see a need to change the code if the results are identical,
> especially if it only works for MySQL.
>
> Thanks,
> Bryan
>
> On Wed, Sep 7, 2011 at 4:14 PM, Earle Nietzel <earle.nietzel at gmail.com>
> wrote:
> > In one of my previous post I mentioned that one of the straight_join
> queries
> > chooses the same fast plan when all is working well. I will include it
> here:
> >
> > select straight_join count(topicimpl0_.ID) as col_0_0_
> > from MFR_AREA_T areaimpl3_
> > inner join MFR_OPEN_FORUM_T openforumi2_ on
> > openforumi2_.surrogateKey=areaimpl3_.ID
> > inner join MFR_TOPIC_T topicimpl0_ on
> > topicimpl0_.of_surrogateKey=openforumi2_.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
> > This query should give the same exact performance as the one without a
> > straight_join that works well.
> >
> > Can you give it a try?
> >
> > On Wed, Sep 7, 2011 at 3:28 PM, Bryan Holladay <holladay at longsight.com>
> > wrote:
> >>
> >> You were right, I grabbed the wrong query... sorry about that... I
> >> just copied your query in the email and ran explain and this is what I
> >> got:
> >>
> >> with straight_join:
> >>
> >> id select_type table type possible_keys key key_len
> >> ref rows Extra
> >> 1 SIMPLE topicimpl0_ ALL PRIMARY,FK863DC0BE74C7E92B
> >> (null) (null) (null) 589086 Using
> >> where
> >> 1 SIMPLE openforumi2_ eq_ref PRIMARY,FKC17608478B5E2A2F
> >> PRIMARY 8 sakai28.topicimpl0_.of_surrogateKey 1
> >> 1 SIMPLE areaimpl3_ eq_ref
> >> PRIMARY,MFR_AREA_CONTEXT_UUID_UNIQUE PRIMARY 8
> >> sakai28.openforumi2_.surrogateKey 1 Using
> >> where
> >> 1 SIMPLE membership4_ ref FKE03761CB7DEF8466
> >> FKE03761CB7DEF8466 9 sakai28.topicimpl0_.ID 6 Using
> >> where
> >> 1 SIMPLE permission1_ ref MFR_MODERATE_POSTINGS_IDX
> >> MFR_MODERATE_POSTINGS_IDX 1 const 15486 Using
> >> where
> >>
> >>
> >> without straight_join:
> >>
> >> id select_type table type possible_keys key key_len
> >> ref rows Extra
> >> 1 SIMPLE areaimpl3_ ref
> >> PRIMARY,MFR_AREA_CONTEXT_UUID_UNIQUE MFR_AREA_CONTEXT_UUID_UNIQUE
> 767
> >> const 1 Using
> >> where; Using index
> >> 1 SIMPLE openforumi2_ ref PRIMARY,FKC17608478B5E2A2F
> >> FKC17608478B5E2A2F 9 sakai28.areaimpl3_.ID 1 Using
> >> where; Using index
> >> 1 SIMPLE topicimpl0_ ref PRIMARY,FK863DC0BE74C7E92B
> >> FK863DC0BE74C7E92B 9 sakai28.openforumi2_.ID 37 Using
> >> where
> >> 1 SIMPLE membership4_ ref FKE03761CB7DEF8466
> >> FKE03761CB7DEF8466 9 sakai28.topicimpl0_.ID 6 Using
> >> where
> >> 1 SIMPLE permission1_ ref MFR_MODERATE_POSTINGS_IDX
> >> MFR_MODERATE_POSTINGS_IDX 1 const 15486 Using
> >> where
> >>
> >>
> >>
> >> So it still looks like it's faster without straight_join. I've
> >> attached the csv file for easier reading.
> >>
> >>
> >> On Wed, Sep 7, 2011 at 3:12 PM, Earle Nietzel <earle.nietzel at gmail.com>
> >> wrote:
> >> > Thanks for the reply Brian.
> >> >
> >> > Everything in your query plan is the same except for the last line:
> >> >
> >> > | id | select_type | table | type | possible_keys |
> >> > key | key_len |
> >> > ref | rows | Extra
> >> > | 1 | SIMPLE | permission1_ | ref | MFRPERMLVL_COMPOSITE_IDX1 |
> >> > MFRPERMLVL_COMPOSITE_IDX1 | 152 |
> >> > ilearn.membership4_.PERMISSION_LEVEL_NAME | 10410 | Using where
> >> >
> >> > Are you looking the right query?
> >> > Your query looks like
> >> > "findNumModeratedTopicsForSiteByUserByMembershipWithPermissionLevelId"
> >> > which
> >> > would make sense since it is using the the PRIMARY key for that last
> >> > line.
> >> > Look at query
> >> >
> "findNumModeratedTopicsForSiteByUserByMembershipWithPermissionLevelName"
> >> > which uses the permissionLevelName so it can't be the PRIMARY key.
> >> >
> >> > Can you check this?
> >> >
> >> > Thanks
> >> > Earle
> >> >
> >> > On Wed, Sep 7, 2011 at 2:41 PM, Bryan Holladay <
> holladay at longsight.com>
> >> > wrote:
> >> >>
> >> >> It still looks like your index's are off... I ran an explain on a
> huge
> >> >> db and got better results than the straight_join query. I noticed
> >> >> your explain is using the PRIMARY index for TOPIC, AREA and OPENFORUM
> >> >> while my explain only shows PRIMARY for MEMBERSHIP. The results are
> >> >> pasted below and I've also attached the csv file to make it easier to
> >> >> read.
> >> >>
> >> >> Thanks,
> >> >> Bryan
> >> >>
> >> >> id select_type table type possible_keys key
> key_len
> >> >> ref rows Extra
> >> >> 1 SIMPLE areaimpl3_ ref
> >> >> PRIMARY,MFR_AREA_CONTEXT_UUID_UNIQUE MFR_AREA_CONTEXT_UUID_UNIQUE
> >> >> 767
> >> >> const 1 Using
> >> >> where; Using index
> >> >> 1 SIMPLE openforumi2_ ref PRIMARY,FKC17608478B5E2A2F
> >> >> FKC17608478B5E2A2F 9 sakai28.areaimpl3_.ID 1
> Using
> >> >> where; Using index
> >> >> 1 SIMPLE topicimpl0_ ref PRIMARY,FK863DC0BE74C7E92B
> >> >> FK863DC0BE74C7E92B 9 sakai28.openforumi2_.ID 37
> Using
> >> >> where
> >> >> 1 SIMPLE membership4_ ref
> >> >> PERMISSION_LEVEL,FKE03761CB7DEF8466,FKE03761CB88085F8E
> >> >> FKE03761CB7DEF8466
> >> >> 9 sakai28.topicimpl0_.ID 6 Using
> >> >> where
> >> >> 1 SIMPLE permission1_ eq_ref
> >> >> PRIMARY,MFR_MODERATE_POSTINGS_IDX
> >> >> PRIMARY 8 sakai28.membership4_.PERMISSION_LEVEL 1
> >> >> Using
> >> >> where
> >> >>
> >> >> On Wed, Sep 7, 2011 at 10:33 AM, Earle Nietzel
> >> >> <earle.nietzel at gmail.com>
> >> >> wrote:
> >> >> > Hi Bryan,
> >> >> >
> >> >> > (I had also included it the original post)
> >> >> >
> >> >> > The only thing I added was the straight_join (MySQL specific). Take
> a
> >> >> > look
> >> >> > last query seems to be the better performer.
> >> >> >
> >> >> > Original query:
> >> >> >
> >> >> > 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
> >> >> >
> >> >> >
> >> >> >
> >> >> > Query with straight_join added:
> >> >> >
> >> >> > select straight_join 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
> >> >> >
> >> >> >
> >> >> > This query with a straight_join seems to out perform the query
> above
> >> >> > and
> >> >> > its
> >> >> > query plan matches that of the original query when the original
> runs
> >> >> > well:
> >> >> > (I would recommend this query)
> >> >> >
> >> >> > select straight_join count(topicimpl0_.ID) as col_0_0_
> >> >> > from MFR_AREA_T areaimpl3_
> >> >> > inner join MFR_OPEN_FORUM_T openforumi2_ on
> >> >> > openforumi2_.surrogateKey=areaimpl3_.ID
> >> >> > inner join MFR_TOPIC_T topicimpl0_ on
> >> >> > topicimpl0_.of_surrogateKey=openforumi2_.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
> >> >> >
> >> >> > Earle
> >> >> >
> >> >> > On Wed, Sep 7, 2011 at 8:12 AM, Bryan Holladay
> >> >> > <holladay at longsight.com>
> >> >> > wrote:
> >> >> >>
> >> >> >> 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"
> >> >> >> >> >
> >> >> >> >
> >> >> >
> >> >> >
> >> >
> >> >
> >
> >
> > _______________________________________________
> > 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/20110907/76f2779c/attachment.html
More information about the sakai-dev
mailing list