[Building Sakai] Slow permissions checks in large sites (mysql)

Stephen Marquard stephen.marquard at uct.ac.za
Fri May 3 11:03:22 PDT 2013 

Possible, though hard to say for sure without knowing more about the internals of the mysql query optimizer (or whatever it uses to select indexes).

The fixes are fairly innocuous (adding 'force index' statements for mysql dialog for 3 or 4 sql statements) so would seem to be a good investment. There are similar 'force index' hints for mysql for other queries dating back to very early 2.x releases.

Regards
Stephen
________________________________________
From: azeckoski at gmail.com [azeckoski at gmail.com] on behalf of Aaron Zeckoski [azeckoski at unicon.net]
Sent: 03 May 2013 03:15 PM
To: Stephen Marquard
Cc: sakai-dev
Subject: Re: [Building Sakai] Slow permissions checks in large sites (mysql)

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