[Building Sakai] [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/sakai-dev/attachments/20121205/e52caa4b/attachment.html
More information about the sakai-dev
mailing list