[Building Sakai] Another slow query

[Kusnetz, Jeremy]

We have been working with MySQL/Oracle on performance issues, besides
the Samigo queries, this was one of our top slow queries, and what their
support had to say about it.

 

I have been looking at the query

SELECT SAKAI_REALM_ROLE.ROLE_NAME, SAKAI_REALM_FUNCTION.FUNCTION_NAME
FROM SAKAI_REALM_RL_FN INNER JOIN SAKAI_REALM ON SAKAI_REALM.REALM_KEY =
SAKAI_REALM_RL_FN.REALM_KEY AND SAKAI_REALM.REALM_ID =
x'2F736974652F313531303437' INNER JOIN SAKAI_REALM_ROLE ON
SAKAI_REALM_ROLE.ROLE_KEY = SAKAI_REALM_RL_FN.ROLE_KEY INNER JOIN
SAKAI_REALM_FUNCTION ON SAKAI_REALM_FUNCTION.FUNCTION_KEY =
SAKAI_REALM_RL_FN.FUNCTION_KEY;

which is one of those showing up often in the slow query log. I noticed
that the query optimizer chooses to do a table (or rather index) scan on
the SAKAI_REALM_ROLE table even though there is an index it can use. It
might not be faster to force it to use an index and I don't know to what
extend you are able to influence the exact queries generated by your
application, but it might be worth a shot.

When you have a chance, can I get you to run the queries below to
explore some alternative query paths? (the reformatting is just to make
it easier to read). The \P tail -10 ensures only the last to lines of
the queries are displayed, so you are not flooded with data.

\P tail -10

-- The original query - for reference
EXPLAIN
SELECT SAKAI_REALM_ROLE.ROLE_NAME, SAKAI_REALM_FUNCTION.FUNCTION_NAME
FROM SAKAI_REALM_RL_FN
INNER JOIN SAKAI_REALM ON SAKAI_REALM.REALM_KEY =
SAKAI_REALM_RL_FN.REALM_KEY AND SAKAI_REALM.REALM_ID =
x'7268796E63686F63657068616C69616E'
INNER JOIN SAKAI_REALM_ROLE ON SAKAI_REALM_ROLE.ROLE_KEY =
SAKAI_REALM_RL_FN.ROLE_KEY
INNER JOIN SAKAI_REALM_FUNCTION ON SAKAI_REALM_FUNCTION.FUNCTION_KEY =
SAKAI_REALM_RL_FN.FUNCTION_KEY;

SELECT SAKAI_REALM_ROLE.ROLE_NAME, SAKAI_REALM_FUNCTION.FUNCTION_NAME
FROM SAKAI_REALM_RL_FN
INNER JOIN SAKAI_REALM ON SAKAI_REALM.REALM_KEY =
SAKAI_REALM_RL_FN.REALM_KEY AND SAKAI_REALM.REALM_ID =
x'7268796E63686F63657068616C69616E'
INNER JOIN SAKAI_REALM_ROLE ON SAKAI_REALM_ROLE.ROLE_KEY =
SAKAI_REALM_RL_FN.ROLE_KEY
INNER JOIN SAKAI_REALM_FUNCTION ON SAKAI_REALM_FUNCTION.FUNCTION_KEY =
SAKAI_REALM_RL_FN.FUNCTION_KEY;

-- Forcing to use the index on SAKAI_REALM_ROLE.ROLE_KEY (the PRIMARY
KEY)
EXPLAIN
SELECT SAKAI_REALM_ROLE.ROLE_NAME, SAKAI_REALM_FUNCTION.FUNCTION_NAME
FROM SAKAI_REALM_RL_FN
INNER JOIN SAKAI_REALM ON SAKAI_REALM.REALM_KEY =
SAKAI_REALM_RL_FN.REALM_KEY AND SAKAI_REALM.REALM_ID =
x'7268796E63686F63657068616C69616E'
INNER JOIN SAKAI_REALM_ROLE FORCE INDEX (PRIMARY) ON
SAKAI_REALM_ROLE.ROLE_KEY = SAKAI_REALM_RL_FN.ROLE_KEY
INNER JOIN SAKAI_REALM_FUNCTION ON SAKAI_REALM_FUNCTION.FUNCTION_KEY =
SAKAI_REALM_RL_FN.FUNCTION_KEY;

SELECT SAKAI_REALM_ROLE.ROLE_NAME, SAKAI_REALM_FUNCTION.FUNCTION_NAME
FROM SAKAI_REALM_RL_FN
INNER JOIN SAKAI_REALM ON SAKAI_REALM.REALM_KEY =
SAKAI_REALM_RL_FN.REALM_KEY AND SAKAI_REALM.REALM_ID =
x'7268796E63686F63657068616C69616E'
INNER JOIN SAKAI_REALM_ROLE FORCE INDEX (PRIMARY) ON
SAKAI_REALM_ROLE.ROLE_KEY = SAKAI_REALM_RL_FN.ROLE_KEY
INNER JOIN SAKAI_REALM_FUNCTION ON SAKAI_REALM_FUNCTION.FUNCTION_KEY =
SAKAI_REALM_RL_FN.FUNCTION_KEY;

-- Force the JOIN order
EXPLAIN
SELECT STRAIGHT_JOIN SAKAI_REALM_ROLE.ROLE_NAME,
SAKAI_REALM_FUNCTION.FUNCTION_NAME
FROM SAKAI_REALM
INNER JOIN SAKAI_REALM_RL_FN ON SAKAI_REALM.REALM_KEY =
SAKAI_REALM_RL_FN.REALM_KEY
INNER JOIN SAKAI_REALM_ROLE ON SAKAI_REALM_ROLE.ROLE_KEY =
SAKAI_REALM_RL_FN.ROLE_KEY
INNER JOIN SAKAI_REALM_FUNCTION ON SAKAI_REALM_FUNCTION.FUNCTION_KEY =
SAKAI_REALM_RL_FN.FUNCTION_KEY
WHERE SAKAI_REALM.REALM_ID = x'7268796E63686F63657068616C69616E';

SELECT STRAIGHT_JOIN SAKAI_REALM_ROLE.ROLE_NAME,
SAKAI_REALM_FUNCTION.FUNCTION_NAME
FROM SAKAI_REALM
INNER JOIN SAKAI_REALM_RL_FN ON SAKAI_REALM.REALM_KEY =
SAKAI_REALM_RL_FN.REALM_KEY
INNER JOIN SAKAI_REALM_ROLE ON SAKAI_REALM_ROLE.ROLE_KEY =
SAKAI_REALM_RL_FN.ROLE_KEY
INNER JOIN SAKAI_REALM_FUNCTION ON SAKAI_REALM_FUNCTION.FUNCTION_KEY =
SAKAI_REALM_RL_FN.FUNCTION_KEY
WHERE SAKAI_REALM.REALM_ID = x'7268796E63686F63657068616C69616E';

-- Alternative for forced JOIN ordier
EXPLAIN
SELECT STRAIGHT_JOIN SAKAI_REALM_ROLE.ROLE_NAME,
SAKAI_REALM_FUNCTION.FUNCTION_NAME
FROM SAKAI_REALM_RL_FN
INNER JOIN SAKAI_REALM ON SAKAI_REALM.REALM_KEY =
SAKAI_REALM_RL_FN.REALM_KEY AND SAKAI_REALM.REALM_ID =
x'7268796E63686F63657068616C69616E'
INNER JOIN SAKAI_REALM_ROLE ON SAKAI_REALM_ROLE.ROLE_KEY =
SAKAI_REALM_RL_FN.ROLE_KEY
INNER JOIN SAKAI_REALM_FUNCTION ON SAKAI_REALM_FUNCTION.FUNCTION_KEY =
SAKAI_REALM_RL_FN.FUNCTION_KEY;

SELECT STRAIGHT_JOIN SAKAI_REALM_ROLE.ROLE_NAME,
SAKAI_REALM_FUNCTION.FUNCTION_NAME
FROM SAKAI_REALM_RL_FN
INNER JOIN SAKAI_REALM ON SAKAI_REALM.REALM_KEY =
SAKAI_REALM_RL_FN.REALM_KEY AND SAKAI_REALM.REALM_ID =
x'7268796E63686F63657068616C69616E'
INNER JOIN SAKAI_REALM_ROLE ON SAKAI_REALM_ROLE.ROLE_KEY =
SAKAI_REALM_RL_FN.ROLE_KEY
INNER JOIN SAKAI_REALM_FUNCTION ON SAKAI_REALM_FUNCTION.FUNCTION_KEY =
SAKAI_REALM_RL_FN.FUNCTION_KEY;

This message is private and confidential. If you have received it in error, please notify the sender and remove it from your system.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20111017/2007237a/attachment.html