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

Recently accessed items block: Upgrade step poor performance

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 3.6.5, 3.7.1, 3.8
    • Fix Version/s: 3.6.6, 3.7.2
    • Component/s: Blocks
    • Labels:
    • Testing Instructions:
      Hide
      Setup
      1. Uninstall your current instance.
      2. Check out the first release of Moodle 3.6

        git checkout v3.6.0

      3. Install this Moodle instance
      4. Set up the following
        • 3 courses (C1, C2 and C3) with 2 forums in each of them
        • A user (admin is fine) enrolled in the courses
        • "Recently accessed items" (RAI) block added on the Dashboard
      5. As the user, go to each of the forums in the courses.
      6. Go to the dashboard and confirm the 6 forums appear in the RAI block
      7. Delete course C3
      8. Delete a forum from C2.
      Test 1 - Upgrade
      1. Checkout the branch that you're testing (e.g. if you're on 3.6, "git checkout MOODLE_36_STABLE")
      2. Upgrade your moodle instance.
      3. Confirm that upgrade completes successfully and you don't see any errors.
      4. Go to the Dashboard
      5. Confirm that you don't encounter any errors.
      6. Confirm that the forums from C3 are no longer being shown in the RAI block.
      7. Confirm that the forum that you deleted in C2 is no longer being shown in the RAI block.
      Show
      Setup Uninstall your current instance. Check out the first release of Moodle 3.6 git checkout v3.6.0 Install this Moodle instance Set up the following 3 courses (C1, C2 and C3) with 2 forums in each of them A user (admin is fine) enrolled in the courses "Recently accessed items" (RAI) block added on the Dashboard As the user, go to each of the forums in the courses. Go to the dashboard and confirm the 6 forums appear in the RAI block Delete course C3 Delete a forum from C2. Test 1 - Upgrade Checkout the branch that you're testing (e.g. if you're on 3.6, " git checkout MOODLE_36_STABLE ") Upgrade your moodle instance. Confirm that upgrade completes successfully and you don't see any errors. Go to the Dashboard Confirm that you don't encounter any errors. Confirm that the forums from C3 are no longer being shown in the RAI block. Confirm that the forum that you deleted in C2 is no longer being shown in the RAI block.
    • Affected Branches:
      MOODLE_36_STABLE, MOODLE_37_STABLE, MOODLE_38_STABLE
    • Fixed Branches:
      MOODLE_36_STABLE, MOODLE_37_STABLE
    • Pull 3.6 Branch:
      MDL-66309-m36
    • Pull 3.7 Branch:
      MDL-66309-m37
    • Pull Master Branch:
      MDL-66309-master

      Description

      This upgrade step in block_recentlyaccesseditems does not complete within reasonable time (it takes more than 40 minutes) on our database:

      $sql = "courseid NOT IN (SELECT c.id from {course} c) OR cmid NOT IN (SELECT cm.id from {course_modules} cm)";
      $DB->delete_records_select("block_recentlyaccesseditems", $sql);
      

      We have 300k rows in the block table and about 1.1m in course_modules, and using Postgres 11.4.

      Seems to me that Postgres should be able to do this quickly, but for whatever reason, it doesn't. I propose changing it to some more natural relational SQL along the lines of this SELECT:

      select * from mdl_block_recentlyaccesseditems rai
      left join mdl_course c on c.id = rai.courseid
      left join mdl_course_modules cm on cm.id = rai.cmid
      where c.id is null or cm.id is null
      

      This completes in about 0.5 seconds on our system.

      Note: I have hacked it on our local system to get us past this upgrade, so this is not critical for us, but (a) there may be other people who haven't yet done the upgrade, and (b) it seems like in the past, this upgrade step has been reused, so I figure perhaps it might be in future too.

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  9/Sep/19

                  Time Tracking

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