Details
-
Type:
Bug
-
Status:
Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 1.8.2
-
Fix Version/s: 1.9
-
Component/s: Administration, Roles / Access
-
Labels:None
-
Environment:Xeon MP 2000 x 4, 8 GB RAM, Linux, MySQL 5.0.44
-
Affected Branches:MOODLE_18_STABLE
-
Fixed Branches:MOODLE_19_STABLE
Description
Perfomance info of subscripe global roles operation :
—
494.095403 secs RAM: 23.1????? Included 153 files ticks: 49417 user: 2303 sys: 109 cuser: 0 csys: 2 Load average: 1.52 Record cache hit/miss ratio : 0/0
—
Missing Indexes Found: 0
Mysql "SHOW FULL PROCESSLIST" show such request.
Status "coping to tmp table" (my.cfg: tmp_table_size =800MB).
Each request is executed 5-10 seconds (earlier when tmp_table_size was less, they were executed 30-50 seconds).
--------------
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 =287
AND c1.id
IN ( 197, 10, 5, 29458, 1, 21879 )
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 =287
AND c1.id
IN ( 197, 10, 5, 29458, 1, 21879 )
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
--------------
We're having the same problem here. v 1.8.2. Mysql 4.
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...