Moodle

Gradebook 1.9 takes a long time to load the initial report page when database is big

Details

  • Type: Improvement Improvement
  • Status: Open Open
  • Priority: Major Major
  • Resolution: Unresolved
  • Affects Version/s: 1.9
  • Fix Version/s: STABLE backlog
  • Component/s: Gradebook
  • Labels:
    None
  • Affected Branches:
    MOODLE_19_STABLE

Description

Looks like the grade/report/grader/index.php could use some performance optimization. Here are my quick testing result:

Test case #1: Loaded with 40,000 courses and 60,000 users, load with the moodle test case course, took 110 seconds to load (ticks: 11089)
Test case #2: Loaded with only 3 courses and 25 users, load with moodle test case course, took, 69 seconds to load the page (ticks: 6892)

See screenshots for more details. Tested on the same server.

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Petr Škoda (skodak) added a comment -

what db are you using?

Show
Petr Škoda (skodak) added a comment - what db are you using?
Hide
Permalink
Wen Hao Chuang added a comment - - edited

MySQL (on Mac Xserve running Mac OSX, with PHP 5.2.3, MySQL 5.0.37). This is on one of our testing servers.

Show
Wen Hao Chuang added a comment - - edited MySQL (on Mac Xserve running Mac OSX, with PHP 5.2.3, MySQL 5.0.37). This is on one of our testing servers.
Hide
Permalink
Martin Dougiamas added a comment -

What are the numbers for the second and subsequent loads?

Show
Martin Dougiamas added a comment - What are the numbers for the second and subsequent loads?
Hide
Permalink
Wen Hao Chuang added a comment - - edited

Martin, the second and subsequent loads are about the same. The number of DB queries are low, but the overall time taking is still high. Here is another experiment:

=================================================================

1st attempt:
62.393589 secs
RAM: 11.6MB
Included 70 files
DB queries 49
ticks: 6239 user: 39 sys: 7 cuser: 0 csys: 0
Load average: 1.95
Record cache hit/miss ratio : 0/0

=================================================================

2nd load: (click on the "Grader report" on the breadcrumb) => see screenshot
60.466671 secs
RAM: 11.6MB
Included 70 files
DB queries 49
ticks: 6047 user: 39 sys: 7 cuser: 0 csys: 0
Load average: 1.79
Record cache hit/miss ratio : 0/0

=================================================================

I haven't dig into the code level yet but it seems that again the /grade/report/grader/index.php might have called some functions that would go through the whole mdl_user table, as it seems that if you have a loaded (60,000+) courses in the mdl_course table, the performance is about the same (still slow, but the key seems to be the mdl_user table), just a quick FYI...

By the way all of the tests reported did not enable the cache (that is, under the Site Administration -> Server -> Performance -> Cache Type it is set to "None"

Show
Wen Hao Chuang added a comment - - edited Martin, the second and subsequent loads are about the same. The number of DB queries are low, but the overall time taking is still high. Here is another experiment: ================================================================= 1st attempt: 62.393589 secs RAM: 11.6MB Included 70 files DB queries 49 ticks: 6239 user: 39 sys: 7 cuser: 0 csys: 0 Load average: 1.95 Record cache hit/miss ratio : 0/0 ================================================================= 2nd load: (click on the "Grader report" on the breadcrumb) => see screenshot 60.466671 secs RAM: 11.6MB Included 70 files DB queries 49 ticks: 6047 user: 39 sys: 7 cuser: 0 csys: 0 Load average: 1.79 Record cache hit/miss ratio : 0/0 ================================================================= I haven't dig into the code level yet but it seems that again the /grade/report/grader/index.php might have called some functions that would go through the whole mdl_user table, as it seems that if you have a loaded (60,000+) courses in the mdl_course table, the performance is about the same (still slow, but the key seems to be the mdl_user table), just a quick FYI... By the way all of the tests reported did not enable the cache (that is, under the Site Administration -> Server -> Performance -> Cache Type it is set to "None"
Hide
Permalink
Wen Hao Chuang added a comment -

By the way, just did some more testing, same page, when you click on the "Turn editing on" link it also takes a long time to load the page (just a quick FYI)

61.247811 secs
RAM: 12.2MB
Included 70 files
DB queries 49
ticks: 6125 user: 70 sys: 10 cuser: 0 csys: 0
Load average: 1.83
Record cache hit/miss ratio : 0/0

Show
Wen Hao Chuang added a comment - By the way, just did some more testing, same page, when you click on the "Turn editing on" link it also takes a long time to load the page (just a quick FYI) 61.247811 secs RAM: 12.2MB Included 70 files DB queries 49 ticks: 6125 user: 70 sys: 10 cuser: 0 csys: 0 Load average: 1.83 Record cache hit/miss ratio : 0/0
Hide
Permalink
Petr Škoda (skodak) added a comment -

could you please try to turn off the group and column averages? there is one slow query; also please make sure you are running latest cvs, I did some patching in this area recently.

Show
Petr Škoda (skodak) added a comment - could you please try to turn off the group and column averages? there is one slow query; also please make sure you are running latest cvs, I did some patching in this area recently.
Hide
Permalink
Wen Hao Chuang added a comment - - edited

Hi Petr, in the "My report preferences" tab the "Show groups" is default as "No" (and I couldn't find another option for "Group averages"). However, the "Show column averages" is default as "Yes", and after I turned it off (change to No), as you said, now it runs MUCH faster. So I guess before we fix that slow query maybe we could change the "Show column averages" default to No? By the way after tuning the "Show column averages" to No this is the result:

(DB loaded with 40K+ courses and 60K+ users)

0.511107 secs
RAM: 11.7MB
Included 70 files
DB queries 47
ticks: 52 user: 41 sys: 9 cuser: 0 csys: 0
Load average: 1.03
Record cache hit/miss ratio : 0/0

Show
Wen Hao Chuang added a comment - - edited Hi Petr, in the "My report preferences" tab the "Show groups" is default as "No" (and I couldn't find another option for "Group averages"). However, the "Show column averages" is default as "Yes", and after I turned it off (change to No), as you said, now it runs MUCH faster. So I guess before we fix that slow query maybe we could change the "Show column averages" default to No? By the way after tuning the "Show column averages" to No this is the result: (DB loaded with 40K+ courses and 60K+ users) 0.511107 secs RAM: 11.7MB Included 70 files DB queries 47 ticks: 52 user: 41 sys: 9 cuser: 0 csys: 0 Load average: 1.03 Record cache hit/miss ratio : 0/0
Hide
Permalink
Nicolas Connault added a comment -

I've run a number of tests and can't see any performance issue there. Can you give us an update?

Show
Nicolas Connault added a comment - I've run a number of tests and can't see any performance issue there. Can you give us an update?
Hide
Permalink
Daniel Neis added a comment -

Hello,

i think there is a forum topic about this:
http://moodle.org/mod/forum/discuss.php?d=110752#p515709

Here at Universidade Federal de Santa Catarina, we have distance learning courses with aroun 500 user each, and in our MySQL this screen really took a long long time to be generated. We have added the STRAIGHT_JOIN, mentioned in the forum and the problem was solved.

Show
Daniel Neis added a comment - Hello, i think there is a forum topic about this: http://moodle.org/mod/forum/discuss.php?d=110752#p515709 Here at Universidade Federal de Santa Catarina, we have distance learning courses with aroun 500 user each, and in our MySQL this screen really took a long long time to be generated. We have added the STRAIGHT_JOIN, mentioned in the forum and the problem was solved.

Dates

  • Created:
    Updated:
Atlassian JIRA (v4.4.3#663-r165197) | Report a problem
Powered by a free Atlassian JIRA open source license for Moodle. Try JIRA - bug tracking software for your team.