[Building Sakai] Slow permissions checks in large sites (mysql)
Stephen Marquard
stephen.marquard at uct.ac.za
Fri May 3 04:28:35 PDT 2013
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<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
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<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/03c26896/attachment.html
More information about the sakai-dev
mailing list