Moodle Community Sites

Samuli, thanks for your report. Reassigning to our expert sys admin

Hi Samuli,

While im not 100% familier with the Moodle SQL schema yet, here is the output from the queries on MDL-15748.

mysql> SELECT sctx.path,
-> ra.roleid,
-> ctx.path AS parentpath,
-> rco.capability,
-> rco.permission
-> FROM role_assignments ra
-> JOIN context ctx ON ra.contextid=ctx.id
-> JOIN context sctx ON (sctx.path LIKE CONCAT(ctx.path,'/%') )
-> JOIN role_capabilities rco ON (rco.roleid=ra.roleid AND rco.contextid=sctx.id)
-> WHERE ra.userid = 1
-> AND sctx.contextlevel <= 50
-> ORDER BY sctx.depth, sctx.path, ra.roleid;
Empty set (0.04 sec)

mysql> SELECT inn.path,
-> inn.roleid,
-> inn.parentpath,
-> rco.capability,
-> rco.permission
-> FROM role_capabilities rco
-> JOIN (SELECT sctx.id,
-> sctx.path,
-> sctx.depth,
-> ra.roleid,
-> ctx.path AS parentpath
-> FROM role_assignments ra
-> JOIN context ctx ON ra.contextid=ctx.id
-> JOIN context sctx ON (sctx.path LIKE CONCAT(ctx.path,'/%') )
-> WHERE ra.userid = 1
-> AND sctx.contextlevel <= 50
-> ) inn
-> ON (rco.roleid=inn.roleid AND rco.contextid=inn.id)
-> ORDER BY inn.depth, inn.path, inn.roleid;

Empty set (21.26 sec)

FYI, moodle.org is slow because the hardware needs to be replaced which will happen soon.

Ok, so you did not benefit from Eloy's suggested fix. But we had the same thing, and I suggested another fix in a later comment, changing the original query to

SELECT sctx.path, ra.roleid, rco.capability, rco.permission FROM mdl5_role_assignments ra
JOIN mdl5_context ctx ON ra.contextid=ctx.id
JOIN mdl5_context sctx ON (sctx.path LIKE CONCAT(ctx.path,'/%') )
JOIN mdl5_role_capabilities rco ON (rco.roleid=ra.roleid AND rco.contextid=sctx.id)
WHERE ra.userid = {a_variable} AND ctx.contextlevel <= 40 AND sctx.contextlevel <= 50;

Could you check that moodle.org is logging slow queries and then take a peek if this subcontext query is amongst the slow queries? If it is, I think that adding the "AND ctx.contextlevel <= 40" to the WHERE clause will make it much faster.

I can confirm the query exists in the mysql slow log multiple times.
I will discuss this with a developer to see about getting your patch implimented on moodle.org

I've performed some test in moodle.org. Results at MDL-15748

I really don't think it's a problematic query there at all (due to the small number of contexts in that server). Also, perhaps you're seeing it in old slow log (I rolled it yesterday because it was really old).

Other queries (like searches, database module, logs... are, afaik, the main problem for now in moodle.org).

About Samuli proposed change, I'd like to see some more test results... but I think has perfectly sense to apply it (more after performing the tests commented above).

Ciao

The new moodle.org site has been switched on, please advise if this is still an issue?

Much faster now, thanks!