[Building Sakai] [Deploying Sakai] MySQL performance issues with 2.8
Bryan Holladay
holladay at longsight.com
Wed Dec 5 07:12:11 PST 2012
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):
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
openforumi3_ 1 SIMPLE ref PRIMARY,FKC17608478B5E2A2F FKC17608478B5E2A2F 9
sakai28.areaimpl4_.ID 1 Using where; Using index
topicimpl2_ 1 SIMPLE ref PRIMARY,FK863DC0BE74C7E92B FK863DC0BE74C7E92B 9
sakai28.openforumi3_.ID 31 Using where; Using index
messageimp0_ 1 SIMPLE ref PRIMARY,FK80C1A316A2D0BE7B FK80C1A316A2D0BE7B 9
sakai28.topicimpl2_.ID 35 Using where
unreadstat1_ 1 SIMPLE ref MFR_UNREAD_STATUS_I2 MFR_UNREAD_STATUS_I2 8
sakai28.messageimp0_.ID 5 Using where; Using index
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> 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>>
> > 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>>> 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@ 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
> _______________________________________________
> 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/20121205/1adce2b1/attachment.html
More information about the sakai-dev
mailing list