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

Performance regression: slow login because get_user_access_sitewide takes way too long since moodle 2.2

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2.2, 2.3
    • Fix Version/s: 2.2.1
    • Component/s: Libraries, Performance
    • Labels:
    • Environment:

      Used server is a recent 5.1 mysql server on 64 bit Linux.
    • Database:
      MySQL
    • Testing Instructions:
      Hide

      Note to tester: See last comment below, about unit-tests already passed and other thoughts (before testing).

      1/ run /lib/simpletest/fulltestaccesslib.php for all 4 supported databases to make sure there are no regressions
      2/ find some large test site and verify it is faster on pg and mysql

      Show
      Note to tester: See last comment below, about unit-tests already passed and other thoughts (before testing). 1/ run /lib/simpletest/fulltestaccesslib.php for all 4 supported databases to make sure there are no regressions 2/ find some large test site and verify it is faster on pg and mysql
    • Workaround:
      Hide

      use PostgreSQL

      Show
      use PostgreSQL
    • Affected Branches:
      MOODLE_22_STABLE, MOODLE_23_STABLE
    • Fixed Branches:
      MOODLE_22_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      w51_MDL-30761_m23_slowmysql

      Description

      The query for getting "overrides of interesting roles in all interesting child contexts" takes very very long since the update to moodle 2.2

      With moodle 2.1.3 the created query (not captured) took about 0.7 seconds, with moodle 2.2 it takes about 40 seconds. This depends on the user, not all users are affected - seems to depend on the number of entries in the IN - clause, because with every entry the query execution time increases by about 1 second.

      query created by moodle 2.2 (which took measurably long)

      /lib/accesslib.php around line 770

      SELECT ctx.path, rc.roleid, rc.capability, rc.permission
                           FROM mdl_role_capabilities rc
                           JOIN mdl_context ctx
                                ON (ctx.id = rc.contextid)
                      LEFT JOIN mdl_context cctx
                                ON (cctx.contextlevel = 50
                                    AND ctx.path LIKE CONCAT(cctx.path, '/%'))
                           JOIN mdl_context pctx
                                ON (pctx.path IN (XXXX)
                                    AND (ctx.id = pctx.id
                                         OR ctx.path LIKE CONCAT(pctx.path, '/%')
                                         OR pctx.path LIKE CONCAT(ctx.path, '/%')))
                          WHERE rc.roleid = 'XXXX'
                                AND cctx.id IS NULL

      About 1200 entries in the mdl_role_capabilites, about 25000 in the mdl_context table, slow query log states "Query_time: 36.833683 Lock_time: 0.000498 Rows_sent: 449 Rows_examined: 29503116". Most time spent with this query.

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Votes:
                  0 Vote for this issue
                  Watchers:
                  5 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:
                    Fix Release Date:
                    9/Jan/12