153.448825 secs
Included 146 files
Load average: 2.31
Record cache hit/miss ratio : 16317/2323

That query hangs up mysql, writing temporary tables...

mod/forum:initialsubscriptions allowed for teachers, non-editing teacher, students. I'm login as "administrator". This role by default on my system have moodle/site:doanything privilegies, but don't have mod/forum:initialsubscriptions.

unsubcribe SHOW PROCESSLIST:
SELECT rc.capability, c1.id AS id1, c1.id AS id2, (
c1.contextlevel *100
) AS aggrlevel, SUM( rc.permission ) AS sum
FROM mdl_role_assignments ra, mdl_role_capabilities rc, mdl_context c1
WHERE ra.contextid = c1.id
AND ra.roleid = rc.roleid
AND ra.userid =195
AND c1.id
IN ( 519, 4, 5, 29458, 1, 22315 )
AND rc.contextid =1
AND (
rc.capability = 'mod/forum:viewdiscussion'
OR rc.capability = 'moodle/site:doanything'
)
GROUP BY rc.capability, c1.id, c1.contextlevel *100
HAVING SUM( rc.permission ) !=0
UNION ALL
SELECT rc.capability, c1.id AS id1, c2.id AS id2, (
c1.contextlevel *100 + c2.contextlevel
) AS aggrlevel, SUM( rc.permission ) AS sum
FROM mdl_role_assignments ra
LEFT JOIN mdl_role_capabilities rc ON ra.roleid = rc.roleid
LEFT JOIN mdl_context c1 ON ra.contextid = c1.id
LEFT JOIN mdl_context c2 ON rc.contextid = c2.id
LEFT JOIN mdl_context_rel cr ON cr.c1 = c2.id
WHERE ra.userid =195
AND c1.id
IN ( 519, 4, 5, 29458, 1, 22315 )
AND rc.contextid !=1
AND (
rc.capability = 'mod/forum:viewdiscussion'
OR rc.capability = 'moodle/site:doanything'
)
AND cr.c2 = c1.id
GROUP BY rc.capability, c1.id, c2.id, c1.contextlevel *100 + c2.contextlevel
HAVING SUM( rc.permission ) !=0
ORDER BY aggrlevel ASC

For that role, mod/forum:initialsubscriptions is set to "Not Set."

If mod/forum:initialsubscriptions is set to "Not Set", assigning a user to a global role should be fast. However removing a user from global role could be slow, because the code recursively tries to unsubscribe users from all courses (if the user no longer has capability to view forum posts). We have made some improvements in 1.9 and are still working on this. Most likely this query is not the culprit.

Cheers,

Yu

I understand, that there can be some difficulties, however optimization is necessary: yesterday the request of removal of several users of a rate from a system context was carried out more than three hours and at this time the server worked very slowly.
For a web-application it is inadmissible: it is necessary to make even progress-bar.
Courses at us only 400. How on unsubscribe from them can 3 hours and hundreds inquiries to a database which are executed on 3-40 seconds are request and create 1Gb tmp tables?

Possibly, request contain noindexes merges which demand direct search of millions values.

By the way, according to mysql, Handler_read_rnd_next = 3,591.76M and Handler_read_rnd= 23M, Created_tmp_disk_tables=444k (at tmp table size=800M!!!) that confirms my words.

Cheers,

Yu

I will need all the core tables so the whole sql-dump will be easier. Surely the zipped version of the sql-dump can't be 1.3G since you only have 400 courses?

Cheers,

Yu

I have found the request, like is:
—
SELECT rc.capability, c1.id AS id1, c1.id AS id2, (
c1.contextlevel *100
) AS aggrlevel, SUM( rc.permission ) AS sum
FROM mdl_role_assignments ra, mdl_role_capabilities rc, mdl_context c1
WHERE ra.contextid = c1.id
AND ra.roleid = rc.roleid
AND ra.userid =1156
AND c1.id
IN ( 158, 20, 1, 309 )
AND rc.contextid =1
AND (
rc.capability = 'moodle/course:view'
OR rc.capability = 'moodle/site:doanything'
)
GROUP BY rc.capability, c1.id, c1.contextlevel *100
HAVING SUM( rc.permission ) !=0
UNION ALL
SELECT rc.capability, c1.id AS id1, c2.id AS id2, (
c1.contextlevel *100 + c2.contextlevel
) AS aggrlevel, SUM( rc.permission ) AS sum
FROM mdl_role_assignments ra
LEFT JOIN mdl_role_capabilities rc ON ra.roleid = rc.roleid
LEFT JOIN mdl_context c1 ON ra.contextid = c1.id
LEFT JOIN mdl_context c2 ON rc.contextid = c2.id
LEFT JOIN mdl_context_rel cr ON cr.c1 = c2.id
WHERE ra.userid =1156
AND c1.id
IN ( 158, 20, 1, 309 )
AND rc.contextid !=1
AND (
rc.capability = 'moodle/course:view'
OR rc.capability = 'moodle/site:doanything'
)
AND cr.c2 = c1.id
GROUP BY rc.capability, c1.id, c2.id, c1.contextlevel *100 + c2.contextlevel
HAVING SUM( rc.permission ) !=0
ORDER BY aggrlevel ASC
—

The first parte of this request working very slowest, becouse index on column mdl_role_capabilities.permission is epsent.

Second part of this request working very slowest and devours memory, becouse fo wrong merge:
"LEFT JOIN mdl_context_rel cr ON cr.c1 = c2.id" creates the huge tmp table, which all is filtered on a condition
"AND cr.c2 = c1.id".
Simple replece "LEFT JOIN mdl_context_rel cr ON cr.c1 = c2.id" on "LEFT JOIN mdl_context_rel cr ON cr.c1 = c2.id AND cr.c2 = c1.id" gives huge acceleration. Though I in general doubt, that here is necessary "Left Joun".

Both these corrections together accelerate performance of inquiry about three orders (in 1000 times).

Carrying unsubscribe of users, etc. the Moodle starts a series of request "SELECT rc.capability, c1.id".
In a series last varies only last context id. What for each time to do check on all contexts?