[Deploying Sakai] MySQL performance issues with 2.8

Sam Ottenhoff ottenhoff at longsight.com
Wed Dec 5 07:00:37 PST 2012


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> wrote:

> 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<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 <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@**upmc.fr<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<production at collab.sakaiproject.org>
>>         <mailto:production at collab.**sakaiproject.org<production at collab.sakaiproject.org>
>> >
>>
>>         http://collab.sakaiproject.**org/mailman/listinfo/**production<http://collab.sakaiproject.org/mailman/listinfo/production>
>>
>>         TO UNSUBSCRIBE: send email to
>>         production-unsubscribe at collab.**sakaiproject.org<production-unsubscribe at collab.sakaiproject.org>
>>         <mailto:production-**unsubscribe at collab.**sakaiproject.org<production-unsubscribe at collab.sakaiproject.org>>
>> with a
>>
>>         subject of "unsubscribe"
>>
>>
>>
>>     ______________________________**_________________
>>     production mailing list
>>     production at collab.**sakaiproject.org<production at collab.sakaiproject.org>
>>     <mailto:production at collab.**sakaiproject.org<production at collab.sakaiproject.org>
>> >
>>
>>     http://collab.sakaiproject.**org/mailman/listinfo/**production<http://collab.sakaiproject.org/mailman/listinfo/production>
>>
>>     TO UNSUBSCRIBE: send email to
>>     production-unsubscribe at collab.**sakaiproject.org<production-unsubscribe at collab.sakaiproject.org>
>>     <mailto:production-**unsubscribe at collab.**sakaiproject.org<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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/production/attachments/20121205/e52caa4b/attachment-0001.html 


More information about the production mailing list