[Deploying Sakai] [Building Sakai] MySQL performance issues with 2.8 (Msgcntr)
Jean-Francois Leveque
jean-francois.leveque at upmc.fr
Wed Dec 5 07:42:41 PST 2012
On 05/12/2012 16:12, Bryan Holladay wrote:
> The Msgcntr tables probably just needs to be tuned:
>
>
> Test query:
>
>
> explain select SQL_NO_CACHE unreadstat1_.USER_C as col_0_0_, count(*) as
> col_1_0_
> from MFR_MESSAGE_T messageimp0_
> inner join MFR_TOPIC_T topicimpl2_ on
> messageimp0_.surrogateKey=topicimpl2_.ID
> inner join MFR_OPEN_FORUM_T openforumi3_ on
> topicimpl2_.of_surrogateKey=openforumi3_.ID
> inner join MFR_AREA_T areaimpl4_ on
> openforumi3_.surrogateKey=areaimpl4_.ID, MFR_UNREAD_STATUS_T unreadstat1_
> where messageimp0_.ID=unreadstat1_.MESSAGE_C and
> areaimpl4_.CONTEXT_ID='554a0549-03b1-4c1b-82a9-75d7fc5ac2d5'
> and unreadstat1_.READ_C=1 and messageimp0_.DRAFT=0 and
> messageimp0_.DELETED=0
> group by unreadstat1_.USER_C;
>
>
> -a good query plan should look like (taken from a DB with 5+ million
> mfr_unread_status rows):
mysql> select count(ID) from mfr_unread_status_t;
+-----------+
| count(ID) |
+-----------+
| 260613 |
+-----------+
1 row in set (1.26 sec)
> table id select_type type possible_keys key key_len ref rows Extra
>
> areaimpl4_ 1 SIMPLE ref PRIMARY,MFR_AREA_CONTEXT_UUID_UNIQUE
> MFR_AREA_CONTEXT_UUID_UNIQUE 767 const 1 Using where; Using index; Using
> temporary; Using filesort
areaimpl4_ 1 SIMPLE ref
PRIMARY,MFR_AREA_CONTEXT_UUID_UNIQUE,MFR_AREA_CONTEXT_I
MFR_AREA_CONTEXT_UUID_UNIQUE 767 const 1 Using where; Using index; Using
temporary; Using filesort
Different possible
> openforumi3_ 1 SIMPLE ref PRIMARY,FKC17608478B5E2A2F FKC17608478B5E2A2F
> 9 sakai28.areaimpl4_.ID 1 Using where; Using index
openforumi3_1 SIMPLE ref PRIMARY,FKC17608478B5E2A2F FKC17608478B5E2A2F 9
prepsakai.areaimpl4_.ID 1 Using where; Using index
Same
> topicimpl2_ 1 SIMPLE ref PRIMARY,FK863DC0BE74C7E92B FK863DC0BE74C7E92B 9
> sakai28.openforumi3_.ID 31 Using where; Using index
topicimpl2_ 1 SIMPLE ref PRIMARY,FK863DC0BE74C7E92B FK863DC0BE74C7E92B 9
prepsakai.openforumi3_.ID 13 Using where; Using index
Less rows, otherwise same
> messageimp0_ 1 SIMPLE ref PRIMARY,FK80C1A316A2D0BE7B FK80C1A316A2D0BE7B
> 9 sakai28.topicimpl2_.ID 35 Using where
messageimp0_ 1 SIMPLE ref
PRIMARY,FK80C1A316A2D0BE7B,MFR_MESSAGE_DELETED_I FK80C1A316A2D0BE7B 9
prepsakai.topicimpl2_.ID 17 Using where
Different possible and less rows.
> unreadstat1_ 1 SIMPLE ref MFR_UNREAD_STATUS_I2 MFR_UNREAD_STATUS_I2 8
> sakai28.messageimp0_.ID 5 Using where; Using index
unreadstat1_ 1 SIMPLE ref
MFR_UNREAD_STATUS_I2,MFR_UNREAD_MESSAGE_C_ID,MFR_UNREAD_READ_C_ID
MFR_UNREAD_STATUS_I2 8 prepsakai.messageimp0_.ID 5 Using where; Using index
Different possible, otherwise same.
Will do further checks later.
> Full set of needed indexes/contraints
>
> Now, go through tables: MFR_AREA_T, MFR_UNREAD_STATUS_T, MFR_MESSAGE_T,
> MFR_TOPIC_T, and MFR_OPEN_FORUM_T and check the contraints/indexes and
> make sure they only have (remove any extras and run any missing):
>
>
>
> DROP INDEX MFR_UNREAD_STATUS_I2 ON MFR_UNREAD_STATUS_T;
> CREATE INDEX MFR_UNREAD_STATUS_I2 USING BTREE
> ON MFR_UNREAD_STATUS_T(MESSAGE_C, USER_C, READ_C);
>
>
> DROP INDEX MFR_UNREAD_STATUS_I1 ON MFR_UNREAD_STATUS_T;
> CREATE INDEX MFR_UNREAD_STATUS_I1 USING BTREE
> ON MFR_UNREAD_STATUS_T(TOPIC_C, MESSAGE_C, USER_C, READ_C);
>
> DROP INDEX PRIMARY ON MFR_UNREAD_STATUS_T;
> CREATE UNIQUE INDEX PRIMARY USING BTREE
> ON MFR_UNREAD_STATUS_T(ID);
>
>
> ALTER TABLE MFR_UNREAD_STATUS_T
> DROP INDEX TOPIC_C;
> ALTER TABLE MFR_UNREAD_STATUS_T
> ADD CONSTRAINT TOPIC_C
> UNIQUE (TOPIC_C, MESSAGE_C, USER_C);
>
>
> ALTER TABLE MFR_MESSAGE_T
> DROP FOREIGN KEY FK80C1A316A2D0BE7B;
> ALTER TABLE MFR_MESSAGE_T
> ADD CONSTRAINT FK80C1A316A2D0BE7B
> FOREIGN KEY(surrogateKey)
> REFERENCES MFR_TOPIC_T(ID);
>
> ALTER TABLE MFR_TOPIC_T
> DROP FOREIGN KEY FK863DC0BE74C7E92B;
> ALTER TABLE MFR_TOPIC_T
> ADD CONSTRAINT FK863DC0BE74C7E92B
> FOREIGN KEY(of_surrogateKey)
> REFERENCES MFR_OPEN_FORUM_T(ID);
>
> ALTER TABLE MFR_OPEN_FORUM_T
> DROP FOREIGN KEY FKC17608478B5E2A2F;
> ALTER TABLE MFR_OPEN_FORUM_T
> ADD CONSTRAINT FKC17608478B5E2A2F
> FOREIGN KEY(surrogateKey)
> REFERENCES MFR_AREA_T(ID);
>
>
>
> ALTER TABLE MFR_AREA_T
> DROP INDEX MFR_AREA_CONTEXT_UUID_UNIQUE;
> ALTER TABLE MFR_AREA_T
> ADD CONSTRAINT MFR_AREA_CONTEXT_UUID_UNIQUE
> UNIQUE (CONTEXT_ID, TYPE_UUID);
>
>
> *******The majority of times, it seems you just need to remove any
> existing indexes on MFR_UNREAD_STATUS_T and run the following script:
>
>
> 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);
>
> ALTER TABLE MFR_AREA_T
>
> ADD CONSTRAINT CONTEXT_ID
>
> UNIQUE (CONTEXT_ID, TYPE_UUID);
>
>
> If all indexs are correct, try running optimize:
>
>
> optimize table MFR_MESSAGE_T
>
> optimize table MFR_AREA_T
>
> optimize table MFR_OPEN_FORUM_T
>
> optimize table MFR_TOPIC_T
>
> optimize table MFR_UNREAD_STATUS_T
> |
> |
>
>
> On Wed, Dec 5, 2012 at 10:07 AM, Jean-Francois Leveque
> <jean-francois.leveque at upmc.fr <mailto:jean-francois.leveque at upmc.fr>>
> wrote:
>
> Here are the details preceding each query.
>
> On 05/12/2012 16:00, Sam Ottenhoff wrote:
> > And how slow is each query? The MySQL slow log should be providing
> > additional info like speed and rows scanned......
> >
> >
> > On Wed, Dec 5, 2012 at 9:58 AM, Jean-Francois Leveque
> > <jean-francois.leveque at upmc.fr
> <mailto:jean-francois.leveque at upmc.fr>
> <mailto:jean-francois.leveque at upmc.fr
> <mailto:jean-francois.leveque at upmc.fr>>>
> > wrote:
> >
> > Here are the first 9.
>
> Count: 193 Time=6.40s (1234s) Lock=0.00s (0s) Rows=30.0 (5790)
>
> > select topicimpl0_.ID as col_0_0_, count(messagesse1_.ID) as
> > col_1_0_ from MFR_TOPIC_T topicimpl0_ left outer join
> MFR_MESSAGE_T
> > messagesse1_ on topicimpl0_.ID=messagesse1_. surrogateKey and
> > (messagesse1_.DRAFT=N and messagesse1_.DELETED=N) where
> > topicimpl0_.ID in (N , N , N , N , N , N , N , N , N , N , N
> , N , N
> > , N , N , N , N , N , N , N , N , N , N , N , N , N , N , N ,
> N , N)
> > group by topicimpl0_.ID
>
> Count: 69 Time=3.24s (223s) Lock=0.00s (0s) Rows=87.7 (6053)
>
> > select resourcest0_.SITE_ID as col_0_0_,
> resourcest0_.RESOURCE_REF
> > as col_1_0_, sum(resourcest0_.RESOURCE_ COUNT) as col_2_0_ from
> > SST_RESOURCES resourcest0_ where resourcest0_.SITE_ID='S' and
> > resourcest0_.RESOURCE_ACTION=' S' and (resourcest0_.RESOURCE_REF
> > like 'S') and resourcest0_.RESOURCE_DATE>=' S' and
> > resourcest0_.RESOURCE_DATE<'S' group by resourcest0_.SITE_ID ,
> > resourcest0_.RESOURCE_REF order by col_2_0_ DESC
>
> Count: 54 Time=2.51s (135s) Lock=0.00s (0s) Rows=3.6 (197)
>
> > select READ_STATUS, TYPE_UUID, count(READ_STATUS) as
> readCount from
> > MFR_PVT_MSG_USR_T where USER_ID = 'S' and CONTEXT_ID = 'S'
> group
> > by READ_STATUS, TYPE_UUID
>
> Count: 40 Time=2.50s (99s) Lock=0.00s (0s) Rows=4.0 (160)
>
> > select distinct TYPE from SAKAI_SITE order by TYPE
>
> Count: 37 Time=2.24s (82s) Lock=0.00s (0s) Rows=1986.7 (73507)
>
> > select XML, BINARY_ENTITY from CONTENT_RESOURCE_DELETE where
> > (RESOURCE_ID = x'S')
>
> Count: 27 Time=2.63s (71s) Lock=0.00s (0s) Rows=1.0 (27)
>
> > 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='S' and topicimpl0_.MODERATED=N and
> > (membership4_.NAME in ('S' , 'S')) and permission1_.MODERATE_
> > POSTINGS=N and membership4_.PERMISSION_LEVEL= permission1_.ID
>
> Count: 27 Time=2.38s (64s) Lock=0.00s (0s) Rows=20.0 (540)
>
> > select SAKAI_SITE.SITE_ID,SAKAI_SITE.
> TITLE,SAKAI_SITE.TYPE,SAKAI_
> > SITE.SHORT_DESC,SAKAI_SITE. DESCRIPTION,SAKAI_SITE.ICON_
> > URL,SAKAI_SITE.INFO_URL,SAKAI_ SITE.SKIN,SAKAI_SITE.
> > PUBLISHED,SAKAI_SITE.JOINABLE, SAKAI_SITE.PUBVIEW,SAKAI_SITE.
> > JOIN_ROLE,SAKAI_SITE.IS_ SPECIAL,SAKAI_SITE.IS_USER,
> > SAKAI_SITE.CREATEDBY,SAKAI_ SITE.MODIFIEDBY,SAKAI_SITE.
> > CREATEDON,SAKAI_SITE. MODIFIEDON,SAKAI_SITE.CUSTOM_ PAGE_ORDERED
> > from SAKAI_SITE order by SAKAI_SITE.TITLE
> ASC,SAKAI_SITE.TITLE limit
> > N offset N
>
> Count: 24 Time=2.33s (55s) Lock=0.00s (0s) Rows=5.8 (140)
>
> > select SAKAI_SITE.SITE_ID,SAKAI_SITE.
> TITLE,SAKAI_SITE.TYPE,SAKAI_
> > SITE.SHORT_DESC,SAKAI_SITE. DESCRIPTION,SAKAI_SITE.ICON_
> > URL,SAKAI_SITE.INFO_URL,SAKAI_ SITE.SKIN,SAKAI_SITE.
> > PUBLISHED,SAKAI_SITE.JOINABLE, SAKAI_SITE.PUBVIEW,SAKAI_SITE.
> > JOIN_ROLE,SAKAI_SITE.IS_ SPECIAL,SAKAI_SITE.IS_USER,
> > SAKAI_SITE.CREATEDBY,SAKAI_ SITE.MODIFIEDBY,SAKAI_SITE.
> > CREATEDON,SAKAI_SITE. MODIFIEDON,SAKAI_SITE.CUSTOM_ PAGE_ORDERED
> > from SAKAI_SITE where SAKAI_SITE.IS_USER = 'S' and
> > SAKAI_SITE.IS_SPECIAL = 'S' and SAKAI_SITE.PUBLISHED = N and
> > SAKAI_SITE.JOINABLE = 'S' and SITE_ID not in (select SITE_ID from
> > SAKAI_SITE_USER where USER_ID = x'S' and PERMISSION <= PUBLISHED)
> > order by SAKAI_SITE.TITLE ASC
>
> Count: 20 Time=2.88s (57s) Lock=0.00s (0s) Rows=1.0 (20)
>
> > select count(distinct eventstati0_.USER_ID) as col_0_0_ from
> > SST_EVENTS eventstati0_ where eventstati0_.SITE_ID='S' and
> > eventstati0_.EVENT_ID='S'
> >
> >
> > On 05/12/2012 15:43, Matthew Jones wrote:
> >
> > Nothing was added back to 2.8 and many of those kernel tables
> > will be
> > the slowest without caching. In 2.9 caching was improved
> around the
> > kernel tables and it's possible that you might be able to
> change
> > some of
> > your cache parameters specific to your institution to
> lower the
> > hits on
> > the other kernel tables.
> >
> > https://jira.sakaiproject.org/ browse/KNL-826
> > <https://jira.sakaiproject.org/browse/KNL-826> - Improve
> > performance by
> > precaching Sakai Users
> > https://jira.sakaiproject.org/ browse/KNL-934
> > <https://jira.sakaiproject.org/browse/KNL-934> - Authz Role Key
> > Cache
> >
> > I don't know of any significant performance improvements to
> > sitestats
> > other than in the admin (global sitestats) interface. Steve
> > Swinsburg
> > would probably know as he's done the most work on that tool
> > recently.
> >
> > With msgcntr, there were reports about it being slow
> because certain
> > versions of MySQL were selecting a slow query plan.
> > (https://jira.sakaiproject. org/browse/MSGCNTR-575
> > <https://jira.sakaiproject.org/browse/MSGCNTR-575>) Earle said
> > he had the
> > correct indexes, just mysql was still running slower queries.
> > But yea,
> > you'd have to post more specific information.
> >
> >
> > On Wed, Dec 5, 2012 at 9:35 AM, Sam Ottenhoff
> > <ottenhoff at longsight.com <mailto:ottenhoff at longsight.com>
> <mailto:ottenhoff at longsight.com <mailto:ottenhoff at longsight.com>>
> > <mailto:ottenhoff at longsight <mailto:ottenhoff at longsight>. com
> > <mailto:ottenhoff at longsight.com
> <mailto:ottenhoff at longsight.com>>>> wrote:
> >
> > Hi JFL,
> >
> > Sharing the actual queries that are slow will be
> much more
> > helpful
> > than the aggregation you have provided. For
> example, based
> > on your
> > MsgCntr queries, it's possible we could tell if you are
> > missing some
> > key performance improvements committed after 2.8.0 was
> > released.
> >
> > --Sam
> >
> >
> > On Wed, Dec 5, 2012 at 9:29 AM, Jean-Francois Leveque
> > <jean-francois.leveque at upmc.fr <mailto:jean-francois.leveque at upmc.fr>
> > <mailto:jean-francois.leveque at upmc.fr
> <mailto:jean-francois.leveque at upmc.fr>>
> > <mailto:jean-francois.leveque@ <mailto:jean-francois.leveque@>
> upmc.fr <http://upmc.fr>
> > <mailto:jean-francois.leveque at upmc.fr
> <mailto:jean-francois.leveque at upmc.fr>>>> wrote:
> >
> > Hi all,
> >
> > I've just checked my most frequent MySQL slow
> log requests.
> >
> > Apart from the commits that cannot be traced by slow
> > log only, I've
> > found the tables that appear more frequently are
> from 3
> > different parts
> > of the code.
> >
> > msgcntr (MFR_*)
> > sitestats (SST_*)
> > kernel (SAKAI_SITE, SAKAI_SITE_USER,
> SAKAI_SITE_PAGE,
> > CONTENT_RESOURCE_DELETE)
> >
> > Have some of you done some extra indexing or other
> > changes that
> > improved
> > performance with these?
> >
> > Cheers,
> >
> > J-F
More information about the production
mailing list