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

Performance problem on the enrol group members page

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 3.4.6, 3.5.3, 3.6.5, 3.7.1, 3.8
    • Fix Version/s: 3.6.6, 3.7.2
    • Component/s: Groups, Performance
    • Labels:

      Description

      We have a strange performance problem with the page group/members.php. On a very large course (~45,000 users) the sometimes it completely fails to load, and sometimes it loads in a few seconds. It just depends on which group you select.

      The problem in this query: https://github.com/moodle/moodle/blob/master/user/selector/lib.php#L900, which for the course I am looking at expands to:

      SELECT r.id AS roleid, u.id AS userid,
          u.id,u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.firstname,u.lastname,u.email,u.idnumber,u.username,
          (   SELECT count(igm.groupid)
                FROM mdl_groups_members igm
                JOIN mdl_groups ig ON igm.groupid = ig.id
               WHERE igm.userid = u.id AND ig.courseid = 100071
          ) AS numgroups
       
        FROM mdl_user u
        JOIN (
              SELECT DISTINCT eu1_u.id
                FROM mdl_user eu1_u
                JOIN mdl_user_enrolments ej1_ue ON ej1_ue.userid = eu1_u.id
                JOIN mdl_enrol ej1_e ON (ej1_e.id = ej1_ue.enrolid AND ej1_e.courseid = 100071)
               WHERE 1 = 1 AND eu1_u.deleted = 0
             ) e ON e.id = u.id
        LEFT JOIN mdl_role_assignments ra ON (ra.userid = u.id
                                              AND ra.contextid IN (1,3,210076)
                                              AND ra.roleid IN (3,4,5))
        LEFT JOIN mdl_role r ON r.id = ra.roleid
        LEFT JOIN mdl_groups_members gm ON (gm.userid = u.id AND gm.groupid = 1905)
       WHERE u.deleted = 0
        AND gm.id IS NULL
        AND u.id <> 1 AND u.deleted = 0 AND u.confirmed = 1
       ORDER BY u.lastname, u.firstname, u.id
      

      For some groupids, that complets in about 4.5 seconds. For other group ids it never finishes executing (I left it running for more than 10 minutes.)

      Note the highly dangerous featuer that it mentions the mdl_user table twice in a join that involves a subquery. Our mdl_user table contains 1 million rows, so if the query optimiser gets confusedin how to handle that, the query runs forever.

      The query is fairly cleary equivalent to:

      SELECT r.id AS roleid, u.id AS userid,
          u.id,u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.firstname,u.lastname,u.email,u.idnumber,u.username,
          (   SELECT count(igm.groupid)
                FROM mdl_groups_members igm
                JOIN mdl_groups ig ON igm.groupid = ig.id
               WHERE igm.userid = u.id AND ig.courseid = 100071
          ) AS numgroups
       
        FROM mdl_user u
                JOIN mdl_user_enrolments ej1_ue ON ej1_ue.userid = u.id
                JOIN mdl_enrol ej1_e ON (ej1_e.id = ej1_ue.enrolid AND ej1_e.courseid = 100071)
        LEFT JOIN mdl_role_assignments ra ON (ra.userid = u.id
                                              AND ra.contextid IN (1,3,210076)
                                              AND ra.roleid IN (3,4,5))
        LEFT JOIN mdl_role r ON r.id = ra.roleid
        LEFT JOIN mdl_groups_members gm ON (gm.userid = u.id AND gm.groupid = 1905)
       WHERE u.deleted = 0
        AND gm.id IS NULL
        AND u.id <> 1 AND u.deleted = 0 AND u.confirmed = 1
       ORDER BY u.lastname, u.firstname, u.id
      

      That gets rid of the subquery and the duplicate mention of the user table, and reliably runs in 4.5s for all group ids on the course that sometimes timing out.

      Of course, making this change in the query in the Moodle code will not be trivial because the query is built up from different part. However, it should be possible. Instead of using get_enrolled_sql to get SQL to put in a subquery, it should be possible to use get_enrolled_join.

        Attachments

          Activity

            People

            • Assignee:
              HuongNV Huong Nguyen
              Reporter:
              timhunt Tim Hunt
              Peer reviewer:
              Tim Hunt
              Integrator:
              Andrew Nicols
              Tester:
              CiBoT
              Participants:
              Component watchers:
              Andrew Nicols, Mathew May, Michael Hawkins, Shamim Rezaie, Simey Lameze, Matteo Scaramuccia, Jake Dallimore, Jun Pataleta
            • Votes:
              0 Vote for this issue
              Watchers:
              4 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 - 45 minutes
                45m