Issue Details (XML | Word | Printable)

Key: MDL-10848
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Yu Zhang
Reporter: Alex Djachenko
Votes: 4
Watchers: 1
Operations

Add/Edit UI Mockup to this issue
If you were logged in you would be able to see more operations.
Moodle

Subscribe/Unsubscribe roles very slow

Created: 14/Aug/07 07:34 PM   Updated: 01/Nov/07 03:32 AM
Component/s: Administration, Roles
Affects Version/s: 1.8.2
Fix Version/s: 1.9

Environment: Xeon MP 2000 x 4, 8 GB RAM, Linux, MySQL 5.0.44

Participants: Alex Djachenko, Garret Gengler, Martin Dougiamas and Yu Zhang
Security Level: None
Resolved date: 10/Oct/07
Affected Branches: MOODLE_18_STABLE
Fixed Branches: MOODLE_19_STABLE


 Description  « Hide
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
--------------



 All   Comments   Change History   Version Control      Sort Order: Ascending order - Click to sort in descending order
Garret Gengler added a comment - 16/Aug/07 05:49 AM
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...


Martin Dougiamas added a comment - 16/Aug/07 03:09 PM
To add to the picture, Yu

Yu Zhang added a comment - 16/Aug/07 03:32 PM
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?

Alex Djachenko added a comment - 16/Aug/07 05:48 PM
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.


Alex Djachenko added a comment - 16/Aug/07 06:14 PM
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


Garret Gengler added a comment - 17/Aug/07 05:06 AM
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."


Yu Zhang added a comment - 17/Aug/07 09:57 AM - edited
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


Alex Djachenko added a comment - 17/Aug/07 06:33 PM
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.


Yu Zhang added a comment - 21/Aug/07 03:17 PM
Hi Alex, are you able to provide a database dump?

Cheers,

Yu


Alex Djachenko added a comment - 21/Aug/07 08:49 PM
Hi, Yu!
Yes of course. I can provide sql-dump.
Which tables you interest (full backup borrows 1.3Gb)?

Yu Zhang added a comment - 22/Aug/07 10:01 AM
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


Alex Djachenko added a comment - 04/Sep/07 06:12 PM
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).


Alex Djachenko added a comment - 05/Sep/07 02:01 AM
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?


Martin Dougiamas added a comment - 10/Oct/07 04:22 PM
I think this should be fixed by the new code in HEAD ... Alex can you check and re-open if it's still bad?

Alex Djachenko added a comment - 01/Nov/07 03:32 AM
We have updated moodle. Really, all works very quickly. Thanks, Martin and Yu!