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

Performance drop with a lot of data in mdl_cache_flags.

XMLWordPrintable

    • MySQL
    • MOODLE_30_STABLE, MOODLE_35_STABLE
    • MOODLE_36_STABLE
    • MDL-54035-lazy_reload
    • 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".

      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.

        1. image-2018-09-26-16-00-57-620.png
          image-2018-09-26-16-00-57-620.png
          50 kB
        2. image-2018-09-27-08-13-30-202.png
          image-2018-09-27-08-13-30-202.png
          72 kB

            jrchamp Jonathan Champ
            tsanecki Tomasz Sanecki
            David Monllaó David Monllaó
            Janelle Barcega Janelle Barcega
            Votes:
            15 Vote for this issue
            Watchers:
            18 Start watching this issue

              Created:
              Updated:
              Resolved:

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

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