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

Performance regression in block_manager::load_blocks()

XMLWordPrintable

    • MySQL
    • MOODLE_32_STABLE, MOODLE_34_STABLE
    • MOODLE_32_STABLE, MOODLE_33_STABLE, MOODLE_34_STABLE
    • Hide

      Test to confirm -MDL-58744- is still fixed

      1. Set $CFG->forcedefaultmymoodle = true; in config.php
      2. Login as an admin
      3. Go to the Site home page
      4. Turn editing on
      5. Add a block "Latest announcements"
      6. Click on the settings icon of the "Latest Announcements" and click on the configure option
      7. Under Page contexts select "Display throughout the entire site"
      8. Save
      9. Under Site administratrion -> Appearance -> Default Dashboard page
      10. Click on the settings for the "Latest Announcements" block and click on the configure option
      11. In the "On this page" box select the region "content"
      12. Save
      13. Click in the Dashboard in the Navigation
        1. Confirm The "Latest Announcements" block resides in the content/center region.
      14. View the "Site Home"
        1. Confirm The "Latest Announcements" block resides in the right region.
      15. Remove $CFG->forcedefaultmymoodle = true; in config.php
      16. Go to the dashboard
      17. Click on "Customize this page "
      18. Move the "Latest Announcements" block to the right
        1. Confirm The "Latest Announcements" block should reside in the right region.
      19. View the "Site Home"
        1. Confirm The "Latest Announcements" block resides in the right region.
      20. Set $CFG->forcedefaultmymoodle = true; in config.php
      21. Click in the Dashboard in the Navigation
        1. Confirm The "Latest Announcements" block resides in the content/center region.
      22. View the "Site Home"
        1. Confirm The "Latest Announcements" block resides in the right region.

      Tests to confirm blocks in general still display correctly

      1. Create a course in the Miscellaneous category
      2. Login as an admin
      3. Go to the Site home page
      4. Turn editing on
      5. Add 3 html blocks using the following settings to the same block region:
        1. Sticky block 1
          • Block title: Sticky block 1
          • Content: Default and On this page values same on site ho
          • Page contexts: Display throughout the entire site
          • Default weight: 2
          • Weight: 2
        2. Sticky block 2
          • Block title: Sticky block 2
          • Content: Default and On this page values different on site home
          • Page contexts: Display throughout the entire site
          • Default weight: -10 (first)
          • Weight: 10 (last)
        3. Sticky block 3
          • Block title: Sticky block 3
          • Content: Default and On this page values same on site home, position override on category
          • Page contexts: Display throughout the entire site
          • Default weight: 3
          • Weight: 3
      6. After the blocks are added they should appear in the following order on site home: Sticky block 1, Sticky block 3, Sticky block 2
      7. Go to the Miscellaneous category, the blocks should appear in the order Sticky block 2, Sticky block 1, Sticky block 3
      8. Turn on editing if necessary and change the 'Weight' on Sticky block 3 to 1
      9. The order of the blocks in the category should now be: Sticky block 2, Sticky block 3, Sticky block 1
      10. Now visit the course, the order of blocks should be: Sticky block 2, Sticky block 1, Sticky block 3
      Show
      Test to confirm - MDL-58744 - is still fixed Set $CFG->forcedefaultmymoodle = true; in config.php Login as an admin Go to the Site home page Turn editing on Add a block "Latest announcements" Click on the settings icon of the "Latest Announcements" and click on the configure option Under Page contexts select "Display throughout the entire site" Save Under Site administratrion -> Appearance -> Default Dashboard page Click on the settings for the "Latest Announcements" block and click on the configure option In the "On this page" box select the region "content" Save Click in the Dashboard in the Navigation Confirm The "Latest Announcements" block resides in the content/center region. View the "Site Home" Confirm The "Latest Announcements" block resides in the right region. Remove $CFG->forcedefaultmymoodle = true; in config.php Go to the dashboard Click on "Customize this page " Move the "Latest Announcements" block to the right Confirm The "Latest Announcements" block should reside in the right region. View the "Site Home" Confirm The "Latest Announcements" block resides in the right region. Set $CFG->forcedefaultmymoodle = true; in config.php Click in the Dashboard in the Navigation Confirm The "Latest Announcements" block resides in the content/center region. View the "Site Home" Confirm The "Latest Announcements" block resides in the right region. Tests to confirm blocks in general still display correctly Create a course in the Miscellaneous category Login as an admin Go to the Site home page Turn editing on Add 3 html blocks using the following settings to the same block region: Sticky block 1 Block title: Sticky block 1 Content: Default and On this page values same on site ho Page contexts: Display throughout the entire site Default weight: 2 Weight: 2 Sticky block 2 Block title: Sticky block 2 Content: Default and On this page values different on site home Page contexts: Display throughout the entire site Default weight: -10 (first) Weight: 10 (last) Sticky block 3 Block title: Sticky block 3 Content: Default and On this page values same on site home, position override on category Page contexts: Display throughout the entire site Default weight: 3 Weight: 3 After the blocks are added they should appear in the following order on site home: Sticky block 1, Sticky block 3, Sticky block 2 Go to the Miscellaneous category, the blocks should appear in the order Sticky block 2, Sticky block 1, Sticky block 3 Turn on editing if necessary and change the 'Weight' on Sticky block 3 to 1 The order of the blocks in the category should now be: Sticky block 2, Sticky block 3, Sticky block 1 Now visit the course, the order of blocks should be: Sticky block 2, Sticky block 1, Sticky block 3

      We recently upgraded from 3.2.4+ (Build: 20170714) to 3.2.5

      After the upgrade when term started we found that Moodle was performing poorly during periods of heavier load, with the database server CPU running at maximum.

      The most time consuming query was the one in block_manager::load_blocks()

      We found that the only change to this query was in MDL-58744

      The change made in MDL-58744 changes the query execution plan on MySQL from:

      id select_type table type possible_keys key key_len ref rows Extra
      1 SIMPLE bi range mdl_blocinst_parshopagsub_ix, mdl_blocinst_par_ix mdl_blocinst_par_ix 8 NULL 27 Using index condition; Using where; Using temporary; Using filesort
      1 SIMPLE bp eq_ref mdl_blocposi_bloconpagsub_uix, mdl_blocposi_blo_ix, mdl_blocposi_con_ix mdl_blocposi_bloconpagsub_uix 260 livemoodle.bi.id, const, const, const 1 Using where
      1 SIMPLE b eq_ref mdl_bloc_nam_uix mdl_bloc_nam_uix 122 livemoodle.bi.blockname 1 Using where
      1 SIMPLE ctx eq_ref mdl_cont_conins_uix, mdl_cont_ins_ix mdl_cont_conins_uix 16 const, livemoodle.bi.id 1 NULL

      to

      id select_type table type possible_keys key key_len ref rows Extra
      1 SIMPLE bi range mdl_blocinst_parshopagsub_ix, mdl_blocinst_par_ix mdl_blocinst_par_ix 8 NULL 27 Using index condition; Using where; Using temporary; Using filesort
      1 SIMPLE bp ref mdl_blocposi_bloconpagsub_uix, mdl_blocposi_blo_ix, mdl_blocposi_con_ix mdl_blocposi_bloconpagsub_uix 8 livemoodle.bi.id 1 Using where
      1 SIMPLE b eq_ref mdl_bloc_nam_uix mdl_bloc_nam_uix 122 livemoodle.bi.blockname 1 Using where
      1 SIMPLE ctx eq_ref mdl_cont_conins_uix, mdl_cont_ins_ix mdl_cont_conins_uix 16 const, livemoodle.bi.id 1 NULL

      Notice that the join on the block_positions table has changed from an eq_ref (meaning a single row comparison) to a ref (will compare multiple rows)

      The number of records we have in each table involved is:

      tablename records
      mdl_block_instances 793923
      mdl_block 58
      mdl_block_positions 285301
      mdl_context 3256997

      We can see the change in database query times from the upgrade:

      And after we reverted the MDL-58744 change:

        1. MDL-58744-reverted.PNG
          MDL-58744-reverted.PNG
          42 kB
        2. sql-times-upgrade.PNG
          sql-times-upgrade.PNG
          57 kB

            nmagill Neill Magill
            nmagill Neill Magill
            Damyon Wiese Damyon Wiese
            David Monllaó David Monllaó
            Shamim Rezaie Shamim Rezaie
            Votes:
            2 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated:
              Resolved:

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