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

Fix dbread perf regression introduced by MDL-55074

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 3.2
    • Fix Version/s: 3.2
    • Component/s: Themes
    • Labels:
    • Testing Instructions:
      Hide
      1. Enable MDL_PERF and check that after applying the patch you see 1 or 2 less db reads in each moodle page
      2. Do some regressions testing, check that everything looks like before
      More-specific testing
      1. Create two users and enrol one of them in several courses. Do not enrol the other in any courses
      2. Logging in as each user, confirm that:
        1. User 1 has all relevant courses listed and the Courses navigation is absent
        2. User 2 has no My courses tree node, and the Courses navigation node is present and expandable
      3. Toggle the $CFG->navshowallcourses to be true
      4. Logging in as each user, confirm that:
        1. User 1 has all relevant courses listed and the Courses navigation is present and expandable
        2. User 2 has no My courses tree node, and the Courses navigation node is present and expandable
      Show
      Enable MDL_PERF and check that after applying the patch you see 1 or 2 less db reads in each moodle page Do some regressions testing, check that everything looks like before More-specific testing Create two users and enrol one of them in several courses. Do not enrol the other in any courses Logging in as each user, confirm that: User 1 has all relevant courses listed and the Courses navigation is absent User 2 has no My courses tree node, and the Courses navigation node is present and expandable Toggle the $CFG->navshowallcourses to be true Logging in as each user, confirm that: User 1 has all relevant courses listed and the Courses navigation is present and expandable User 2 has no My courses tree node, and the Courses navigation node is present and expandable
    • Affected Branches:
      MOODLE_32_STABLE
    • Fixed Branches:
      MOODLE_32_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      MDL-56612_master

      Description

      MDL-55074 introduced a serious performance regression. It adds two new DB queries to every page in Moodle.

      These are examples of the new queries:

      SELECT c.id,c.category,c.sortorder,c.shortname,c.fullname,c.idnumber,c.startdate,c.visible,c.groupmode,c.groupmodeforce,c.cacherev , ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance\n              FROM mdl_course c\n              JOIN (SELECT DISTINCT e.courseid\n                      FROM mdl_enrol e\n                      JOIN mdl_user_enrolments ue ON (ue.enrolid = e.id AND ue.userid = $1)\n                     WHERE ue.status = $2 AND e.status = $3 AND ue.timestart < $4 AND (ue.timeend = 0 OR ue.timeend > $5)\n                   ) en ON (en.courseid = c.id)\n           LEFT JOIN mdl_context ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = $6)\n             WHERE c.id <> $7\n          ORDER BY c.visible DESC,c.sortorder ASC [array (\n  0 => '8',\n  1 => 0,\n  2 => 0,\n  3 => 1477529200.0,\n  4 => 1477529200.0,\n  5 => 50,\n  6 => '1',\n)], referer: http://loganberry.local/im/course/view.php?id=7
      SELECT c.id,c.category,c.sortorder,c.shortname,c.fullname,c.idnumber,c.startdate,c.visible,c.groupmode,c.groupmodeforce,c.cacherev , ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance\n              FROM mdl_course c\n              JOIN (SELECT DISTINCT e.courseid\n                      FROM mdl_enrol e\n                      JOIN mdl_user_enrolments ue ON (ue.enrolid = e.id AND ue.userid = $1)\n                     WHERE ue.status = $2 AND e.status = $3 AND ue.timestart < $4 AND (ue.timeend = 0 OR ue.timeend > $5)\n                   ) en ON (en.courseid = c.id)\n           LEFT JOIN mdl_context ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = $6)\n             WHERE c.id <> $7\n          ORDER BY c.visible DESC,c.sortorder ASC [array (\n  0 => '8',\n  1 => 0,\n  2 => 0,\n  3 => 1477529200.0,\n  4 => 1477529200.0,\n  5 => 50,\n  6 => '1',\n)], referer: http://loganberry.local/im/course/view.php?id=7
      

      One of these comes from:

      commit 99061152af3976dc276777463328a1295a65d4cb
      Author: Damyon Wiese <damyon@moodle.com>
      Date:   Thu Oct 6 14:10:14 2016 +0800
       
          MDL-55074 theme_boost: Navigation and blocks
       
          This patch includes a big set of changes that are all designed to work together to provide
          a better way to navigate in the new theme, and a different way of working with blocks.
       
          Blocks have been moved to a "drawer" that can be opened and closed (this is remembered in a user pref).
       
          A new "flat navigation" element is also available in a drawer - which should let you do 90% of things
          without needing to open the "blocks" drawer.
       
          The flat navigation is build from specific parts of the nav tree - the top nodes like "calendar, dashboard" are
          hand picked. There is a mycourses node listing your enrolled courses.
       
          There is a node for the current course, built from the top nodes in the current course node in the nav tree.
       
          Administrators have a link to the Site admin settings here too.
       
          These nav elements are used by the templates for the new theme, which also has a resigned layout for login and signup.
       
          There have also been some additional fixes / improvements to the scss for the new theme which goes along with these
          layout changes.
       
          This set of changes is a collaboration between Martin, Damyon and Alberto (thanks!).
      

      The other comes from:

      commit 8a2ab5f72d2cc246ea54f74c2b6f4fbc6b3f7705
      Author: Damyon Wiese <damyon@moodle.com>
      Date:   Thu Oct 13 11:34:57 2016 +0800
       
          MDL-55074 navigation: Always show dashboard/home in flat nav
      

      I believe that these function call changes are the causes:

      8a2ab5f72d2cc246ea54f74c2b6f4fbc6b3f7705

      @@ -1230,13 +1232,8 @@ class global_navigation extends navigation_node {
       
               // Load the users enrolled courses if they are viewing the My Moodle page AND the admin has not
               // set that they wish to keep the My Courses branch collapsed by default.
      -        if (!empty($CFG->navexpandmycourses) && $this->page->pagelayout === 'mydashboard'){
      -            $this->rootnodes['mycourses']->forceopen = true;
      -            $this->load_courses_enrolled();
      -        } else {
      -            $this->rootnodes['mycourses']->collapse = true;
      -            $this->rootnodes['mycourses']->make_inactive();
      -        }
      +        $this->rootnodes['mycourses']->forceopen = true;
      +        $this->load_courses_enrolled();
       
               $canviewcourseprofile = true;
      

      99061152af3976dc276777463328a1295a65d4cb

      @@ -1128,15 +1208,18 @@ class global_navigation extends navigation_node {
               $this->load_course_sections($SITE, $this->rootnodes['site']);
       
               $course = $this->page->course;
      +        $this->load_courses_enrolled();
      

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  5/Dec/16