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

Quiz messaging DB query not scaling - poor performance

    XMLWordPrintable

Details

    • Bug
    • Status: Open
    • Major
    • Resolution: Unresolved
    • 3.9.10
    • None
    • Messages
    • MOODLE_39_STABLE

    Description

      In messagelib.php, in function message_get_providers_for_user() there is a SQL query:

       

      $sql = "SELECT DISTINCT rc.capability, 1
      FROM {role_assignments} ra
       JOIN {context} actx ON actx.id = ra.contextid
       JOIN {role_capabilities} rc ON rc.roleid = ra.roleid
       JOIN {context} cctx ON cctx.id = rc.contextid
      WHERE ra.userid = :userid
       AND rc.capability $capcondition
       AND rc.permission > 0
       AND (".$DB->sql_concat('actx.path', "'/'")." LIKE ".$DB->sql_concat('cctx.path', "'/%'").
       " OR ".$DB->sql_concat('cctx.path', "'/'")." LIKE ".$DB->sql_concat('actx.path', "'/%'").")";
      
      

      See in github:  https://github.com/moodle/moodle/blob/MOODLE_39_STABLE/lib/messagelib.php#L628

      This query will perform poorly for Moodle instances with a lot of enrollments and lot of permission overrides.

      To reproduce the problem:

      1. Install clean Moodle 3.9.10
      2. Create empty course called course1
      3. Create quiz activity.
      4. Edit quiz permissions.
        In Advanced role override for role teacher set:
        mod/quiz:emailnotifysubmission
        to non-default value (allow)
        Save changes.
      5. Duplicate quiz 20 times.
      6. Create user called teacher1.
      7. Enroll teacher1 as editing teacher in course1
      8. Backup the course. Include user's data.
      9. Run the query, notice it's super quick.

      SELECT DISTINCT rc.capability, 1 FROM mdl_role_assignments ra JOIN mdl_context actx ON actx.id = ra.contextid JOIN mdl_role_capabilities rc ON rc.roleid = ra.roleid WHERE ra.userid = '3'
       AND rc.capability IN ('mod/quiz:emailnotifysubmission') AND rc.permission > 0;

      1. Restore the course 200 times:
        $ for r in {1..200} ; do moosh course-restore backup-moodle2-course-2-course1-20210914-1855.mbz 1; done
      2. Run the query again:

      mysql> SELECT DISTINCT rc.capability, 1 FROM mdl_role_assignments ra JOIN mdl_context actx ON actx.id = ra.contextid JOIN mdl_role_capabilities rc ON rc.roleid = ra.roleid WHERE ra.userid = '3'
       AND rc.capability IN ('mod/quiz:emailnotifysubmission') AND rc.permission > 0;
      +--------------------------------+---+
      | capability | 1 |
      +--------------------------------+---+
      | mod/quiz:emailnotifysubmission | 1 |
      +--------------------------------+---+
      1 row in set (0.93 sec)

      1. Restore the course 200 times more. Run the query again:

      mysql> SELECT DISTINCT rc.capability, 1 FROM mdl_role_assignments ra JOIN mdl_context actx ON actx.id = ra.contextid JOIN mdl_role_capabilities rc ON rc.roleid = ra.roleid WHERE ra.userid = '3'
       AND rc.capability IN ('mod/quiz:emailnotifysubmission') AND rc.permission > 0;
      +--------------------------------+---+
      | capability | 1 |
      +--------------------------------+---+
      | mod/quiz:emailnotifysubmission | 1 |
      +--------------------------------+---+
      1 row in set (3.11 sec)

       

      The impact of this query is visible when setting up quiz notifications. When a student finishes quiz attempt, notifications are sent to teachers. And this is when this check kicks in - synchronously, during submission.
      We have a production system where this query takes about 2 minutes. And in the course, there are 4 teachers assigned, meaning that student quiz submission takes about 8 minutes.

      Attachments

        Activity

          People

            Unassigned Unassigned
            tmuras Tomasz Muras
            Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Ilya Tregubov, Laurent David, Sara Arjona (@sarjona)
            Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated: