[Building Sakai] Slow permissions checks in large sites (mysql)
Stephen Marquard
stephen.marquard at uct.ac.za
Fri May 3 05:22:17 PDT 2013
5.1.68
Regards
Stephen
-----Original Message-----
From: azeckoski at gmail.com [mailto:azeckoski at gmail.com] On Behalf Of Aaron Zeckoski
Sent: 03 May 2013 02:20 PM
To: Stephen Marquard
Cc: sakai-dev
Subject: Re: [Building Sakai] Slow permissions checks in large sites (mysql)
What version of MySQL?
-AZ
On Fri, May 3, 2013 at 7:28 AM, Stephen Marquard
<stephen.marquard at uct.ac.za> wrote:
> This seems to be on of those cases where mysql can pick the wrong index.
> Adding some “force index” statements seems to help:
>
>
>
> mysql> explain select count(1) from SAKAI_REALM_RL_FN MAINTABLE force
> index (PRIMARY) LEFT JOIN SAKAI_REALM_RL_GR GRANTED_ROLES force index
> (PRIMARY) ON (MAINTABLE.REALM_KEY = GRANTED_ROLES.REALM_KEY AND
>
> -> MAINTABLE.ROLE_KEY = GRANTED_ROLES.ROLE_KEY), SAKAI_REALM REALMS ,
> SAKAI_REALM_FUNCTION FUNCTIONS where (MAINTABLE.ROLE_KEY in(2,1) or
> (GRANTED_ROLES.USER_ID = 'dd816dde-f2e5-4944-9663-c433d8b9b8ac' AND
> GRANTED_ROLES.ACTIVE = 1))
>
> -> AND FUNCTIONS.FUNCTION_NAME = 'annc.all.groups' AND REALMS.REALM_ID
> in ('/site/81791de6-6b18-4b7e-8e5a-9c2e404d1314') AND MAINTABLE.REALM_KEY
> = REALMS.REALM_KEY AND MAINTABLE.FUNCTION_KEY = FUNCTIONS.FUNCTION_KEY;
>
>
>
> +----+-------------+---------------+-------+--------------------------------------------------------------+------------------------------+---------+--------------------------+------+--------------------------+
>
> | id | select_type | table | type | possible_keys
> | key | key_len | ref | rows |
> Extra |
>
> +----+-------------+---------------+-------+--------------------------------------------------------------+------------------------------+---------+--------------------------+------+--------------------------+
>
> | 1 | SIMPLE | REALMS | const | PRIMARY,AK_SAKAI_REALM_ID
> | AK_SAKAI_REALM_ID | 767 | const | 1 |
> Using index |
>
> | 1 | SIMPLE | FUNCTIONS | const |
> PRIMARY,IE_SAKAI_REALM_FUNCTION_NAME,SAKAI_REALM_FUNCTION_KN |
> IE_SAKAI_REALM_FUNCTION_NAME | 299 | const | 1 |
> Using index |
>
> | 1 | SIMPLE | MAINTABLE | ref | PRIMARY
> | PRIMARY | 4 | const | 550 |
> Using where; Using index |
>
> | 1 | SIMPLE | GRANTED_ROLES | ref | PRIMARY
> | PRIMARY | 4 | vula.MAINTABLE.REALM_KEY | 1548 |
> Using where |
>
> +----+-------------+---------------+-------+--------------------------------------------------------------+------------------------------+---------+--------------------------+------+--------------------------+
>
> 4 rows in set (0.00 sec)
>
>
>
> Regards
>
> Stephen
>
>
>
> ---
>
> Stephen Marquard, Acting Director
>
> Centre for Educational Technology, University of Cape Town
> http://www.cet.uct.ac.za
> stephen.marquard at uct.ac.za
> Phone: +27-21-650-5037 Cell: +27-83-500-5290
>
>
>
> From: sakai-dev-bounces at collab.sakaiproject.org
> [mailto:sakai-dev-bounces at collab.sakaiproject.org] On Behalf Of Stephen
> Marquard
> Sent: 03 May 2013 12:44 PM
> To: sakai-dev
> Subject: [Building Sakai] Slow permissions checks in large sites (mysql)
>
>
>
> Hi all,
>
>
>
> We have some performance issues on 2.9.x with mysql that look related to
> this query described in:
>
>
>
> https://jira.sakaiproject.org/browse/SAK-10193
>
>
>
> Though a mysql explain suggests that the query is well-behaved, in fact it
> is not and is producing execution times of up to 30s scanning 150K+ rows in
> some cases.
>
>
>
> mysql> explain select count(1) from SAKAI_REALM_RL_FN MAINTABLE
> LEFT JOIN SAKAI_REALM_RL_GR GRANTED_ROLES ON (MAINTABLE.REALM_KEY =
> GRANTED_ROLES.REALM_KEY AND
>
> -> MAINTABLE.ROLE_KEY = GRANTED_ROLES.ROLE_KEY), SAKAI_REALM REALMS,
> SAKAI_REALM_FUNCTION FUNCTIONS where (MAINTABLE.ROLE_KEY in(2,1) or
> (GRANTED_ROLES.USER_ID = 'dd816dde-f2e5-4944-9663-c433d8b9b8ac' AND
> GRANTED_ROLES.ACTIVE = 1))
>
> -> AND FUNCTIONS.FUNCTION_NAME = 'annc.all.groups' AND REALMS.REALM_ID
> in ('/site/81791de6-6b18-4b7e-8e5a-9c2e404d1314') AND MAINTABLE.REALM_KEY
> = REALMS.REALM_KEY AND MAINTABLE.FUNCTION_KEY = FUNCTIONS.FUNCTION_KEY
>
> -> ;
>
>
>
> +----+-------------+---------------+-------+----------------------------------------------------------------------------------------+------------------------------+---------+-------------------------+------+--------------------------+
>
> | id | select_type | table | type | possible_keys
> | key | key_len | ref | rows |
> Extra |
>
> +----+-------------+---------------+-------+----------------------------------------------------------------------------------------+------------------------------+---------+-------------------------+------+--------------------------+
>
> | 1 | SIMPLE | REALMS | const | PRIMARY,AK_SAKAI_REALM_ID
> | AK_SAKAI_REALM_ID | 767 | const | 1 |
> Using index |
>
> | 1 | SIMPLE | FUNCTIONS | const |
> PRIMARY,IE_SAKAI_REALM_FUNCTION_NAME,SAKAI_REALM_FUNCTION_KN
> | IE_SAKAI_REALM_FUNCTION_NAME | 299 | const | 1 |
> Using index |
>
> | 1 | SIMPLE | MAINTABLE | ref |
> PRIMARY,FK_SAKAI_REALM_RL_FN_REALM,FK_SAKAI_REALM_RL_FN_FUNC,FJ_SAKAI_REALM_RL_FN_ROLE
> | FK_SAKAI_REALM_RL_FN_REALM | 4 | const | 442 |
> Using where; Using index |
>
> | 1 | SIMPLE | GRANTED_ROLES | ref |
> PRIMARY,FK_SAKAI_REALM_RL_GR_REALM,FK_SAKAI_REALM_RL_GR_ROLE,SAKAI_REALM_RL_GR_RAU
> | FK_SAKAI_REALM_RL_GR_ROLE | 4 | vula.MAINTABLE.ROLE_KEY | 491 |
> Using where |
>
> +----+-------------+---------------+-------+----------------------------------------------------------------------------------------+------------------------------+---------+-------------------------+------+--------------------------+
>
> 4 rows in set (0.00 sec)
>
>
>
> Has anyone come across this and/or have optimizations?
>
>
>
> Regards
>
> Stepheh
>
>
>
> ---
>
> Stephen Marquard, Acting Director
>
> Centre for Educational Technology, University of Cape Town
> http://www.cet.uct.ac.za
> stephen.marquard at uct.ac.za
> Phone: +27-21-650-5037 Cell: +27-83-500-5290
>
>
>
> ________________________________
>
> UNIVERSITY OF CAPE TOWN
>
> This e-mail is subject to the UCT ICT policies and e-mail disclaimer
> published on our website at
> http://www.uct.ac.za/about/policies/emaildisclaimer/ or obtainable from +27
> 21 650 9111. This e-mail is intended only for the person(s) to whom it is
> addressed. If the e-mail has reached you in error, please notify the author.
> If you are not the intended recipient of the e-mail you may not use,
> disclose, copy, redirect or print the content. If this e-mail is not related
> to the business of UCT it is sent by the sender in the sender's individual
> capacity.
>
>
> _______________________________________________
> 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"
--
Aaron Zeckoski - Software Architect - http://tinyurl.com/azprofile
________________________________
UNIVERSITY OF CAPE TOWN
This e-mail is subject to the UCT ICT policies and e-mail disclaimer published on our website at http://www.uct.ac.za/about/policies/emaildisclaimer/ or obtainable from +27 21 650 9111. This e-mail is intended only for the person(s) to whom it is addressed. If the e-mail has reached you in error, please notify the author. If you are not the intended recipient of the e-mail you may not use, disclose, copy, redirect or print the content. If this e-mail is not related to the business of UCT it is sent by the sender in the sender's individual capacity.
More information about the sakai-dev
mailing list