[Building Sakai] Slow permissions checks in large sites (mysql)
Stephen Marquard
stephen.marquard at uct.ac.za
Fri May 3 03:43:41 PDT 2013
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<http://www.cet.uct.ac.za/>
stephen.marquard at uct.ac.za<mailto: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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20130503/7d6ec131/attachment.html
More information about the sakai-dev
mailing list