[Building Sakai] Slow permissions checks in large sites (mysql)
Aaron Zeckoski
azeckoski at unicon.net
Fri May 3 06:15:44 PDT 2013
OK, so it is possible this is a non-issue in mysql 5.5 or 5.6.
-AZ
On Fri, May 3, 2013 at 8:22 AM, Stephen Marquard
<stephen.marquard at uct.ac.za> wrote:
> 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.
--
Aaron Zeckoski - Software Architect - http://tinyurl.com/azprofile
More information about the sakai-dev
mailing list