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

Statistics in the Moodle 4.0 question bank use many DB queries causing performance problems

    XMLWordPrintable

Details

    • MOODLE_400_STABLE
    • MOODLE_400_STABLE
    • MDL-74762-Master
    • Hide

      Setting up a test course.

      1. Make sure cron task is set up and works. (Otherwise we can run 'sudo -u www-data php admin/cli/cron.php' manually )
      2. Log in as admin user.
      3. Go to {moodle.url}/admin/tool/task/scheduledtasks.php and  set '\quiz_statistics\task\quiz_statistics_recalculate'  to run every minute.
      4. Create a course
      5. Create a quiz in the course
      6. Add 2 questions to the quiz
      7. create a user and enrol the user to the course with student role

      Scheduled task testing

      1.  Log in with the student account created above
      2. Finish an attempt on the quiz

      Expected:

      1. Wait for 1 minute, or run the cron manually
      2. Run the following query: 'select * from mdl_quiz_statistics;' There should be one record, note the time modified of the records
      3. Log in as admin
      4. Go to Course > Question bank: {moodle.url}/question/edit.php?courseid={courseid}
      5. Run the query in step 2 again, check that there is still one record and the time modified is unchanged. This implies the the question bank view does not trigger re-calculation, and hence use the values calculated by scheduled tasks.

      Recalculation testing

      1.  Log in with the student account
      2. Finish another attempt on the quiz

      Expected:

      1. Wait for 1 minute, or run the cron manually
      2. Run the following query: 'select * from mdl_quiz_statistics;' There should be only one record, but the time modified is now different. That implies the stats has been recalculated

       

       

      Show
      Setting up a test course. Make sure cron task is set up and works. (Otherwise we can run ' sudo -u www-data php admin/cli/cron.php ' manually ) Log in as admin user. Go to {moodle.url}/admin/tool/task/scheduledtasks.php and  set '\quiz_statistics\task\quiz_statistics_recalculate'   to run every minute. Create a course Create a quiz in the course Add 2 questions to the quiz create a user and enrol the user to the course with student role Scheduled task testing  Log in with the student account created above Finish an attempt on the quiz Expected: Wait for 1 minute, or run the cron manually Run the following query: ' select * from mdl_quiz_statistics;' There should be one record, note the time modified of the records Log in as admin Go to Course > Question bank: {moodle.url}/question/edit.php?courseid={courseid } Run the query in step 2 again, check that there is still one record and the time modified is unchanged. This implies the the question bank view does not trigger re-calculation, and hence use the values calculated by scheduled tasks. Recalculation testing  Log in with the student account Finish another attempt on the quiz Expected: Wait for 1 minute, or run the cron manually Run the following query: 'select * from mdl_quiz_statistics;' There should be only one record, but the time modified is now different. That implies the stats has been recalculated    

    Description

      See https://moodle.org/mod/forum/discuss.php?d=434456

      Question bank in 4.0 being very slow.

      Looking at the screen-grabs there, it is clear that is because thousands of DB queries are being done.

      The user reports that disabling the statistics plugins fixes the performace issue, so that is where the performance bug lies.

      Attachments

        1. 1_MDL-74762_master.png
          1_MDL-74762_master.png
          24 kB
        2. 1_MDL-74762_v400.png
          1_MDL-74762_v400.png
          25 kB
        3. 2_MDL-74762_master.png
          2_MDL-74762_master.png
          14 kB
        4. 2_MDL-74762_v400.png
          2_MDL-74762_v400.png
          18 kB
        5. perf-part2.png
          perf-part2.png
          127 kB
        6. perfs-part1.png
          perfs-part1.png
          203 kB
        7. screenshot-1.png
          screenshot-1.png
          4 kB

        Issue Links

          Activity

            People

              Nguyen Nathan Nguyen
              timhunt Tim Hunt
              Tim Hunt Tim Hunt
              Victor Déniz Falcón Victor Déniz Falcón
              John Edward Pedregosa John Edward Pedregosa
              Votes:
              16 Vote for this issue
              Watchers:
              38 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                14/Nov/22

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 day, 3 hours, 23 minutes
                  1d 3h 23m