performance issue on some courses (mainly due to required grading)

[mouneyrac]

I am testing on a big site (hundreds of courses, thousands of students, courses containing many activities, and even more attempts/submissions), on some courses there are performance issues. These course pages take more than 10 seconds to load (on Clean it is less than 1 second).

Looking at the slowest postgres queries it is the query in assign_num_submissions_ungraded (over 200ms) which is a problem because, as there are multiple assignments in the course, it is called multiple time (to calculate submissions/attempts).

[mouneyrac]

I did a bit more testing. I commented:

if (method_exists('theme_snap\\activity', $methodnungraded)) {
                //$meta->numrequiregrading = call_user_func('theme_snap\\activity::'.$methodnungraded, $courseid, $mod->instance);
            }

then if I reload the course it is almost as fast as Clean. Now the problem is that if I open the hidden menu to switch to another big course, as the code triggers a similar require grading REST call, the course takes a long time to open again (obviously a bit less time than with the line before but still close to 10 seconds). If you wait 10 seconds before to click on the course in the hidden menu, then the course load once again as fast as Clean. My postgres log confirms that there are locks.

[gthomas2]

Note to self: Test assign_num_submissions_ungraded using postgres for db

[gthomas2]

Ran an explain on the offending query - might be some performance tune ups for Moodle 2.8

[gthomas2]

@mouneyrac

Jérôme - I have made some optimisations which I hope will address this issue - I'm basically taking advantage of the new 'latest' field in the assign_submissions table (introduced in Moodle 2.8)

If you could see if this branch fixes your performance issues that would be really helpful:

https://github.com/gthomas2/moodle-theme_snap/tree/issue7_performance_issue_some_courses

Thanks in advance

Guy

[gthomas2]

@mouneyrac

I've checked to make sure that when "assign_num_submissions_ungraded" is called that the SQL query is only ran once (it gets everything in one go and then caches it in a static variable).

This is definitely the case for me (testing with postgres) - the database is only being hit once per page load.

[mouneyrac]

Hi Guy,
just for the info, the site is still slow with your branch but I think it's normal as the site run on 2.7. Note that I won't be able to test on this big site anymore. Sorry to not been able to help further, the only site I could test on from now is a very small one (bepaw.com) and snap works well on it, so it won't be really helpful.

[gthomas2]

Thank you for testing this for us Jérôme

I should have mentioned that the optimisations will possibly (very likely) break things on 2.7

[mouneyrac]

No worries, I quickly read the change before so I expected it too :)