Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-61517

Gradebook needs a serious tune-up for large courses

    XMLWordPrintable

Details

    • Bug
    • Status: Open
    • Critical
    • Resolution: Unresolved
    • 3.3.2, 3.3.3, 3.4, 3.4.1
    • None
    • Gradebook, Performance
    • MOODLE_33_STABLE, MOODLE_34_STABLE

    Description

      We've been struggling with gradebook performance issues in large courses lately. For instance, in a course with 861 students and over 50 grade items we're seeing that adding a grade item is very costly. To see just how bad it is, I enabled query logging on a 3.4 WEEKLY_STABLE release test server and added a grade item. I then filtered the results for ones that originate from grade_regrade_final_grades_if_required(). The biggest culprits are:

       

       

      Description  num. queries Example
      Total queries 40544 That's a lot of queries
      Update

      mdl_grade_grades


      per item, per user

      11390

      UPDATE mdl_grade_grades SET aggregationstatus = '(used|unknown|novalue)', aggregationweight = (some number) WHERE itemid = (some itemid) AND userid = '(userid)'
      

      Add new grade item to

      mdl_grade_grade

      1452

      INSERT INTO mdl_grade_grades (itemid,userid,rawgrade,rawgrademax,rawgrademin,rawscaleid,usermodified,finalgrade,hidden,locked,locktime,exported,overridden,excluded,timecreated,timemodified,aggregationstatus,aggregationweight) VALUES(764080,'12345',NULL,'100.00000','0.00000',NULL,NULL,NULL,'0','0','0','0','0','0',1519254162,NULL,'0',NULL,'0',1,'54608776','system','111')

      Add new grade item to

      mdl_grade_grades_history

      1452

      INSERT INTO mdl_grade_grades_history (itemid,userid,rawgrade,rawgrademax,rawgrademin,rawscaleid,usermodified,finalgrade,hidden,locked,locktime,exported,overridden,excluded,timemodified,feedback,feedbackformat,information,informationformat,action,oldid,source,loggeduser) VALUES('764080','12345',NULL,'100.00000','0.00000',NULL,NULL,NULL,'0','0','0','0','0','0',1519254162,NULL,'0',NULL,'0',1,'54608779','system','111')

      Find grades for a user from a particular category 11615

      SELECT itemid FROM mdl_grade_grades WHERE itemid IN (685754,685775,685796,685802,685808,685835,685868,685871,685874,685877,685880,723106,739294,740623,740692,740695,758392,760236,762615) AND userid = '12345'

      Fetch grade_grades object, per id 1452

      SELECT * FROM mdl_grade_grades WHERE id = 54607394

      Fetch grade aggregation status and weight 13067

      SELECT gi.id, gg.aggregationstatus, gg.aggregationweight FROM mdl_grade_grades gg JOIN mdl_grade_items gi ON (gg.itemid = gi.id) WHERE gg.userid = '12345' AND (gi.categoryid = '73457' OR gi.id IN (712643,712676,712709,712694,712688,726262,741031,764079,741016,685883,739327,764078,685907,724609,761973,712682,741034,764080))

       

      Has anyone given thought about restructuring how and when the gradebook performs computations? We're happy to jump in, but don't want to duplicate work that might already be underway.

       

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              szuta Patryk Szuta
              Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Sujith Haridasan, Matteo Scaramuccia, Andrew Lyons, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              13 Vote for this issue
              Watchers:
              14 Start watching this issue

              Dates

                Created:
                Updated: