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

Improve removal of course module completion data

XMLWordPrintable

    • MOODLE_38_STABLE
    • MOODLE_38_STABLE, MOODLE_39_STABLE
    • MDL-69687-master
    • Hide

      Notes

      • This issue must be tested against MySQL or MariaDB, and Postgres (i.e. MySQL and Postgres, or MariaDB and Postgres)

      Setup

      1. Have a look at your config.php and locate the dbprefix. Make a note of it.
      2. Run the following SQL in your database (replace 'mdl_' with the prefix in use), and make a note of the resulting number: 

        SELECT COUNT(1) FROM mdl_course_modules_completion;

      3. Make a note of the result.
        We will re-run this query a number of times and we'll need this

      Before applying the patch

      1. Enable completion tracking on your Moodle site:
        1. Login as admin
        2. Navigate to Site administration -> Advanced features
        3. Enable completion tracking
      2. Create three new courses (with completion enabled).
      3. Create a new assignment in each course
        1. Under "Activity completion" set "Completion tracking" to "Students can manually mark the activity as completed"
      4. Duplicate the assignment twice in each course (Each course now has three assignments)
      5. Enrol a student in each course
      6. Log in as the test student account and mark each of the activities complete.
      7. Run the following SQL, using the original result you had earlier: 

        SELECT COUNT(1) - [resultfromsetup] FROM mdl_course_modules_completion;
        		 
         
        		 
        -- For example:
        		 
        SELECT COUNT(1) - 308 FROM mdl_course_modules_completion;

        1. Confirm that the result is 9
      8. Log back in as admin
      9. Delete one of the assignments in each course
      10. Run the SQL again using the same value - i.e. in the example 308
        1. Confirm that the result is 6
      11. Delete one of the courses
      12. Run the SQL again using the same value - i.e. in the example 308
        1. Confirm that the result has not changed and is still 6

      Apply the patch

      1. Ensure that you're on the latest version of the code for your branch
      2. Run the Moodle upgrade: 

        php admin/cli/upgrade.php

      3. Run the SQL again using the same value - i.e. in the example 308
        1. Confirm that the result has not changed and is still 4
      4. Delete one of the remaining courses.
      5. Run the SQL again using the same value - i.e. in the example 308
        1. Confirm that the result has not changed and is still 2
      Show
      Notes This issue must be tested against MySQL or MariaDB, and Postgres (i.e. MySQL and Postgres, or MariaDB and Postgres) Setup Have a look at your config.php and locate the dbprefix . Make a note of it. Run the following SQL in your database (replace 'mdl_' with the prefix in use), and make a note of the resulting number: SELECT COUNT(1) FROM mdl_course_modules_completion; Make a note of the result. We will re-run this query a number of times and we'll need this Before applying the patch Enable completion tracking on your Moodle site: Login as admin Navigate to Site administration -> Advanced features Enable completion tracking Create three new courses (with completion enabled). Create a new assignment in each course Under "Activity completion" set "Completion tracking" to "Students can manually mark the activity as completed" Duplicate the assignment twice in each course (Each course now has three assignments) Enrol a student in each course Log in as the test student account and mark each of the activities complete. Run the following SQL, using the original result you had earlier: SELECT COUNT(1) - [resultfromsetup] FROM mdl_course_modules_completion;   -- For example: SELECT COUNT(1) - 308 FROM mdl_course_modules_completion; Confirm that the result is 9 Log back in as admin Delete one of the assignments in each course Run the SQL again using the same value - i.e. in the example 308 Confirm that the result is 6 Delete one of the courses Run the SQL again using the same value - i.e. in the example 308 Confirm that the result has not changed and is still 6 Apply the patch Ensure that you're on the latest version of the code for your branch Run the Moodle upgrade: php admin/cli/upgrade.php Run the SQL again using the same value - i.e. in the example 308 Confirm that the result has not changed and is still 4 Delete one of the remaining courses. Run the SQL again using the same value - i.e. in the example 308 Confirm that the result has not changed and is still 2

      The query used to delete course module completion data from a course is inefficient.

      The current query which can be found within the remove_course_contents function in /lib/moodlelib.php at lines 5327-5329 relies upon a subquery:

      DELETE FROM mdl_course_modules_completion WHERE coursemoduleid IN (SELECT id FROM mdl_course_modules WHERE course=?)

      The subquery approach is handy as it works across data engines but is very inefficient.  An explain plan on MySQL show the query requiring a full table scan on mdl_course_modules_completion.  On a database with about 2.1 million records in the mdl_course_modules_completion table, the delete statement frequently takes over 60 seconds to run.

      By comparison using a join allows the query to use indexes on both database tables, mdl_courmodu_cou_ix and mdl_courmoducomp_cou_ix. 

      DELETE cmc
      FROM mdl_course_modules_completion cmc
      INNER JOIN mdl_course_modules cm ON cm.id = cmc.coursemoduleid
      WHERE cm.course = 180;

      I believe the above syntax would also be valid on Microsoft SQL Server.  PostgreSQL would require different syntax:

      DELETE from mdl_course_modules_completion cmc
      USING mdl_course_modules cm
      WHERE cm.id = cmc.coursemoduleid
      AND cm.course = ?

      I am less familiar with PostgreSQL to say if this approach is any better there.  On a very small test database, I get the same explain plan with the above query as with the original version with the subquery.

        1. image-2020-10-19-14-58-42-109.png
          image-2020-10-19-14-58-42-109.png
          90 kB
        2. mysql.jpg
          mysql.jpg
          55 kB
        3. postgres.jpg
          postgres.jpg
          65 kB

            quen Sam Marshall
            brian.winstead Brian Winstead
            Tim Hunt Tim Hunt
            Andrew Lyons Andrew Lyons
            Janelle Barcega Janelle Barcega
            Votes:
            1 Vote for this issue
            Watchers:
            8 Start watching this issue

              Created:
              Updated:
              Resolved:

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 5 hours, 45 minutes
                5h 45m

                  Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.