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

Course Participation Report can bring down the site

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 1.9.9, 2.0
    • Fix Version/s: 1.9.10
    • Labels:
      None
    • Environment:
      Discovered on MySQL, but inefficient SQL likely causes performance issues on other DBMSes also
    • Database:
      Any
    • Affected Branches:
      MOODLE_19_STABLE, MOODLE_20_STABLE
    • Fixed Branches:
      MOODLE_19_STABLE

      Description

      Running a Course Participation report can bring down a Moodle site, if the log table is large enough. On an instance with ~18.8 million rows in the log table, the entire site slows to a crawl and then flicks over to giving the "Error: Database connection failed" error screen (screenshot attached).

      I've been able to improve the performance considerably by removing the join to the log table in the query that's only used for counting users (as it was completely unnecessary), and switching the queries to use subqueries to filter large tables before joining them to other tables, rather than joining to the entire table and then filtering the result of the join. On my test environment, I managed to go from ~15 seconds to run a report to <0.5 seconds.

      Patches attached for both 1.9.9 and 2.0 - it could probably be backported into earlier versions easily also (I haven't tried). The patches use subqueries for both the log table and the role_assignments table, though the performance gain on the latter may be negligible unless you have many thousands of users, each enrolled in many, many courses...

        Attachments

          Activity

            People

            Assignee:
            samhemelryk Sam Hemelryk
            Reporter:
            paul.n Paul Nicholls
            Tester:
            Nobody
            Participants:
            Component watchers:
            Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Sara Arjona (@sarjona), Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Matteo Scaramuccia, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
            Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Fix Release Date:
              25/Oct/10