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), Víctor Déniz Falcón, Jake Dallimore, Jun Pataleta, Matteo Scaramuccia, Jake Dallimore, Jun Pataleta
            • Votes:
              1 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

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