[Deploying Sakai] MySQL performance issues with 2.8
Jean-Francois Leveque
jean-francois.leveque at upmc.fr
Wed Dec 5 07:07:52 PST 2012
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
More information about the production
mailing list