[Deploying Sakai] MySQL performance issues with 2.8
Jean-Francois Leveque
jean-francois.leveque at upmc.fr
Wed Dec 5 06:58:40 PST 2012
Here are the first 9.
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
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
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
select distinct TYPE from SAKAI_SITE order by TYPE
select XML, BINARY_ENTITY from CONTENT_RESOURCE_DELETE where
(RESOURCE_ID = x'S')
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
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
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
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 - Improve performance by
> precaching Sakai Users
> 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) 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>> 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>> 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
> _______________________________________________
> production mailing list
> production at collab.sakaiproject.org
> <mailto:production at collab.sakaiproject.org>
> http://collab.sakaiproject.org/mailman/listinfo/production
>
> TO UNSUBSCRIBE: send email to
> production-unsubscribe at collab.sakaiproject.org
> <mailto:production-unsubscribe at collab.sakaiproject.org> with a
> subject of "unsubscribe"
>
>
>
> _______________________________________________
> production mailing list
> production at collab.sakaiproject.org
> <mailto:production at collab.sakaiproject.org>
> http://collab.sakaiproject.org/mailman/listinfo/production
>
> TO UNSUBSCRIBE: send email to
> production-unsubscribe at collab.sakaiproject.org
> <mailto:production-unsubscribe at collab.sakaiproject.org> with a
> subject of "unsubscribe"
>
>
--
Jean-François Lévêque
Responsable technique Sakai
Université Pierre et Marie Curie
--
Jean-Francois Leveque
University Pierre and Marie Curie
France
More information about the production
mailing list