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

Major Performance Problem with "Moving Modules" upgrade step

    Details

    • Testing Instructions:
      Hide

      Much of this testing is a duplicate of MDL-37939 as we are just altering the SQL query in that upgrade step.

      Install a fresh 2.3 version just prior to 2012062504.08

      1. Create a new course
      2. Create 5-6 course modules in each of 3 sections.
      3. Move course modules between sections. .

      4. They will appear to move, but they will be broken sequence data
      Confirm that with SQL queries;

      SELECT id, section, sequence from mdl_course_sections where course = ? order by section asc;
      SELECT id, section from mdl_course_modules where course = ?;

      Refresh the page and make some more changes:
      Confirm that although the drag/drop appears to work, when you actually refresh the page it doesn't persist across a refresh

      5. Now apply the patch and upgrade
      confirm that the CMs didn't appear to move from where they were before the upgrade
      confirm that the DB looks correct now (no duplicates in sequence data)
      move some CMs around sections
      refresh
      confirm that the section sequence data is correct
      confirm that the cm.section is correct

      On 2.4 and 2.5

      1. Take a course with a fair few CMs
      2. Apply the upgrade
      3. Confirm that the CMs didn't appear to move from where they were before the upgrade
      4. Run the SQL to confirm sequences are correct
      SELECT id, section, sequence from mdl_course_sections where course = ? order by section asc;
      SELECT id, section from mdl_course_modules where course = ?;

      Breaking&fixing test:

      By editing the DB directly create situations when fields course_modules.section and course_sections.sequence (inside the same course) do not match, for example:

      • Module points to the wrong section
      • Module points to the non-existing section (i.e. section 0)
      • Module is present is several section sequences
      • Module is present several times in its section sequence
      • Module is not present in any section sequences

      Re-run the upgrade script and make sure it is fixed. Note that in 2.6 you will need to actually view the course to complete fixing.

      Hint, to re-run the upgrade script you can create a file testupgrade.php :

      <?php
      include "config.php";
      require_once($CFG->libdir.'/db/upgradelib.php'); 
      upgrade_course_modules_sequences();
      purge_all_caches();
      echo "Done.";
      

      Show
      Much of this testing is a duplicate of MDL-37939 as we are just altering the SQL query in that upgrade step. Install a fresh 2.3 version just prior to 2012062504.08 1. Create a new course 2. Create 5-6 course modules in each of 3 sections. 3. Move course modules between sections. . 4. They will appear to move, but they will be broken sequence data Confirm that with SQL queries; SELECT id, section, sequence from mdl_course_sections where course = ? order by section asc; SELECT id, section from mdl_course_modules where course = ?; Refresh the page and make some more changes: Confirm that although the drag/drop appears to work, when you actually refresh the page it doesn't persist across a refresh 5. Now apply the patch and upgrade confirm that the CMs didn't appear to move from where they were before the upgrade confirm that the DB looks correct now (no duplicates in sequence data) move some CMs around sections refresh confirm that the section sequence data is correct confirm that the cm.section is correct On 2.4 and 2.5 1. Take a course with a fair few CMs 2. Apply the upgrade 3. Confirm that the CMs didn't appear to move from where they were before the upgrade 4. Run the SQL to confirm sequences are correct SELECT id, section, sequence from mdl_course_sections where course = ? order by section asc; SELECT id, section from mdl_course_modules where course = ?; Breaking&fixing test: By editing the DB directly create situations when fields course_modules.section and course_sections.sequence (inside the same course) do not match, for example: Module points to the wrong section Module points to the non-existing section (i.e. section 0) Module is present is several section sequences Module is present several times in its section sequence Module is not present in any section sequences Re-run the upgrade script and make sure it is fixed. Note that in 2.6 you will need to actually view the course to complete fixing. Hint, to re-run the upgrade script you can create a file testupgrade.php : <?php include "config.php"; require_once($CFG->libdir.'/db/upgradelib.php'); upgrade_course_modules_sequences(); purge_all_caches(); echo "Done.";
    • Affected Branches:
      MOODLE_23_STABLE, MOODLE_24_STABLE, MOODLE_25_STABLE, MOODLE_26_STABLE
    • Fixed Branches:
      MOODLE_24_STABLE, MOODLE_25_STABLE, MOODLE_26_STABLE
    • Pull Master Branch:
      wip-MDL-38228-master
    • Story Points (Obsolete):
      20

      Description

      The upgrade script portion of the fix for MDL-37939 is inefficient and causes a considerable performance problem for upgrading large sites. The process queries course modules for each course section individually. That's a pretty big deal for Moodlerooms, where our largest site has nearly 445,000 course sections, and an average of over 6,000 course sections across 1,200 sites.

      The root of the problem is finding the course modules which don't exist in the sequence field of the related course section. The following query will get those course section records for you.

      SELECT cs.*
      FROM mdl_course_sections cs
      INNER JOIN mdl_course_modules cm
        ON (cm.course = cs.course AND cm.section = cs.id)
      WHERE CONCAT(',', cs.sequence, ',') NOT LIKE CONCAT('%,', cm.id, ',%')
      

      The following returns an average of 33 course sections for affected sites (~80%), with a maximum of 772 course sections.

      Note: FIND_IN_SET is an operator that only works for MySQL and PG, but the query can be tweaked to support any RDBMS.

      Additonally: for 2.5 and 2.6 branches the new upgrade script has to be run again because of another bug. In version 2.4 we can just replace the old script with a faster one.

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Votes:
                  6 Vote for this issue
                  Watchers:
                  15 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:
                    Fix Release Date:
                    11/Nov/13