Moodle

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...

To add to the picture, Yu

Hi, this query is run when you log in as well, if log in is ok for you then i suspect it's something else. Do you guys have initialsubscription capability allowed for the particular role?

Hi, Yu.
I am sorry, I can not precisely translate your question.

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.

I seem have understood in what a problem. But even if you appoint in system context in a role for which initialsubscriptions it is forbidden, unsubscribe from this role all the same lasts 180 seconds.
Still well to make any prevention, on similarity of a badge in the list of roles or messages, after the termination of the long addition reminding why it has occured.

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

In my case, the slow queries happen when I, as administrator, try to remove a user from a Global Role. The global rule is a custom one we created for instructional support staff.

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

Hi,

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

Hi, 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.

Hi Alex, are you able to provide a database dump?

Cheers,

Yu

Hi, Yu!
Yes of course. I can provide sql-dump.
Which tables you interest (full backup borrows 1.3Gb)?

Hi Alex,

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

Hi All!
I have found the decision.

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).

What for each time to check the rights in all contexts?

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?

I think this should be fixed by the new code in HEAD ... Alex can you check and re-open if it's still bad?

We have updated moodle. Really, all works very quickly. Thanks, Martin and Yu!