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

Course Participation Report can bring down the site

    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...

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Votes:
                1 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

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