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

Performance drop with a lot of data in mdl_cache_flags.

    XMLWordPrintable

    Details

    • Database:
      MySQL
    • Testing Instructions:
      Hide
      1. Prerequisites: A course exists that requires login. There is a forum, a user with the student role and a user with the instructor role.
      2. In your primary browser, log in as an admin.
      3. In a secondary browser (or private browsing window), log in as the student and view the forum.
      4. Back in your primary browser, remove the student role assignment from the student user.
      5. Back in the secondary browser, attempt to view the forum again; you should see the following message: "You do not have the permission to view discussions in this forum".
      6. Still in your secondary browser, log out, then log in as an instructor, view the forum and switch role to student.
      7. Back in your primary browser, remove the instructor role assignment from the instructor user.
      8. Back in the secondary browser, attempt to view the forum again; you should see the following message: "You do not have the permission to view discussions in this forum".
      Show
      Prerequisites: A course exists that requires login. There is a forum, a user with the student role and a user with the instructor role. In your primary browser, log in as an admin. In a secondary browser (or private browsing window), log in as the student and view the forum. Back in your primary browser, remove the student role assignment from the student user. Back in the secondary browser, attempt to view the forum again; you should see the following message: "You do not have the permission to view discussions in this forum". Still in your secondary browser, log out, then log in as an instructor, view the forum and switch role to student. Back in your primary browser, remove the instructor role assignment from the instructor user. Back in the secondary browser, attempt to view the forum again; you should see the following message: "You do not have the permission to view discussions in this forum".
    • Affected Branches:
      MOODLE_30_STABLE, MOODLE_35_STABLE
    • Fixed Branches:
      MOODLE_36_STABLE
    • Pull Master Branch:
      MDL-54035-lazy_reload

      Description

      There is a big performance drop due to inefficient indexing of mdl_cache_flags table.

      This table can grow big if there is a lot of users (eg. 200k+) and bulk user action is performed.

      During login/logout the following query is executed:

      SELECT name,value FROM mdl_cache_flags WHERE flagtype = 'accesslib/dirtycontexts' AND expiry >= '1461934824' AND timemodified > '1461934822'

      there are three indexes in this table:
      id,
      mdl_cachflag_fla_ix,
      mdl_cachflag_nam_ix

      but mysql cannot use these in the above query so it takes signigicantly longer.

      Creating additional indexes for columns expiry and timemodified solves this problem right away (in my testing environment with ~1000000 rows in this table, login time was reduced from minutes to almost instant).

      This seems to be the same issue:
      https://moodle.org/mod/forum/discuss.php?d=313049#p1253421

      To reproduce this issue, you can insert a large ammount of any data into mdl_cache_flags or create a large number of users and do a bulk operation for all users.

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                15 Vote for this issue
                Watchers:
                18 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  3/Dec/18

                  Time Tracking

                  Estimated:
                  Original Estimate - 0 minutes
                  0m
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 10 minutes
                  10m