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

Prohibit checks may be slow on large sites

XMLWordPrintable

      get_with_capability_join() uses NOT IN SELECT to excluded users with CAP_PROHIBIT. On our site this is very slow. We have 3.7 million rows in mdl_role_assignments and are using Amazon Aurora.

      This problem is particularly bad when viewing mod_assign's grading table (mod/assign/view.php?id=ID&action=grading) with Students submit in groups = Yes because – due to the way assignsubmission_comments works – each group to be displayed is checked (unnecessarily?) to see if it's visible to the user. If there are 5 different groups on the page, then get_users_by_capability() is called 5 times, thus if each call takes 2 seconds, that's 10 seconds. (I say "unnecessarily?", I think assign_grading_table checks if the submission should be visible, but due to the way the comment subsystem works, there's a subsequent check for each group to be displayed when assignsubmission_comments is enabled for the site).

      The NOT IN SELECT was introduced by MDL-57027. There was actually an intermediate fix for MDL-57027 which instead used LEFT JOIN (a6210651a9f). As far as I can tell the NOT IN SELECT was chosen for performance reasons, but on our site the LEFT JOIN is much faster.

      Timings (Using tool_excimer): with the LEFT JOIN the call to get_users_by_capability() was reduced from 1.7 seconds to 0.4 seconds. The call to assign->list_participants() was reduced from 3.6 seconds to 0.2 seconds. For an assignment grading table with three groups the server-side time to generate the page was was reduced from 18 seconds to 2 seconds.

      It may be that with smaller sites or different database types the existing code performs better.

      A footnote: Petr Skoda says "people need to really understand that CAP_PROHIBIT is really for exceptional uses only". If the code is kept as it is now (with the NOT IN SELECT) then perhaps the MoodleDocs could mention that prohibits may adversely affect performance on larger sites.

        1. current_notinselect.csv
          3 kB
        2. proposed_leftjoin.csv
          3 kB

            leonstr Leon Stringer
            leonstr Leon Stringer
            Matthias Opitz Matthias Opitz
            Votes:
            2 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated:

                Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.