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

DB performance issue with get_config siteidentifier

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide

      Performance test

      Before applying the patch

      1. Turn on performance debugging in config.php:

        $CFG->perfdebug = 15;
        

      2. Load any page, reload it a couple times to get everything warm and look at DB reads in the footer (eg I see 56)

      After applying the patch

      1. Apply the patch
      2. Reload the same page several times
        1. Confirm that the DB reads went down by 1 (e.g. I see 55 instead of 56)

      Upgrade test

      1. Open the version.php file in your editor
      2. Find the $version variable and increment it by one at the end
      3. Run an upgrade in the UI (visit /admin)
        1. Confirm that there were no errors
      4. Increment the version number in $version again
      5. Perform a CLI upgrade:

        php admin/cli/upgrade.php
        

        1. Confirm that there were no errors

      Installation test

      1. Ensure that the code is up-to-date and clean:

        git checkout .
        git reset --hard origin/[branch]
        git clean -df
        

      2. Uninstall your current site and remove the config.php
      3. Perform a fresh install via the UI
        1. Confirm that there were no errors
      4. Drop the database again but retain the config.php
      5. Perform a fresh install via the UI
        1. Confirm that there were no errors
      6. Uninstall your current site and remove the config.php
      7. Perform a fresh install via the CLI using php admin/cli/install.php
        1. Confirm that there were no errors
      8. Drop the database again but retain the config.php
      9. Perform a fresh install via the CLI using php admin/cli/install_database.php
        1. Confirm that there were no errors
      Show
      Performance test Before applying the patch Turn on performance debugging in config.php: $CFG->perfdebug = 15; Load any page, reload it a couple times to get everything warm and look at DB reads in the footer (eg I see 56) After applying the patch Apply the patch Reload the same page several times Confirm that the DB reads went down by 1 (e.g. I see 55 instead of 56) Upgrade test Open the version.php file in your editor Find the $version variable and increment it by one at the end Run an upgrade in the UI (visit /admin) Confirm that there were no errors Increment the version number in $version again Perform a CLI upgrade: php admin/cli/upgrade.php Confirm that there were no errors Installation test Ensure that the code is up-to-date and clean: git checkout . git reset --hard origin/[branch] git clean -df Uninstall your current site and remove the config.php Perform a fresh install via the UI Confirm that there were no errors Drop the database again but retain the config.php Perform a fresh install via the UI Confirm that there were no errors Uninstall your current site and remove the config.php Perform a fresh install via the CLI using php admin/cli/install.php Confirm that there were no errors Drop the database again but retain the config.php Perform a fresh install via the CLI using php admin/cli/install_database.php Confirm that there were no errors
    • Affected Branches:
      MOODLE_26_STABLE, MOODLE_310_STABLE, MOODLE_400_STABLE
    • Fixed Branches:
      MOODLE_311_STABLE
    • Pull Master Branch:
      MDL-47456-avoid-siteid-db

      Description

      We are seeing slowness on our servers from this query:

      SELECT value FROM mdl_config WHERE name = 'siteidentifier'
      

      We tracked the query down to get_config method which is being called from initialise_cfg.

      So, it appears that on every page page load, this query is being executed. Also, the reason for the query seems a little odd, like for detecting if we are doing an install or similar. I looked at the original code that added this in and it used to disable caches when the query failed, but that line was removed.

      Anyways, seeking advice on refactoring so this query isn't executed on every page.

        Attachments

        1. Screenshot_1.jpg
          Screenshot_1.jpg
          81 kB
        2. Screenshot_2.jpg
          Screenshot_2.jpg
          85 kB
        3. Screenshot_3.jpg
          Screenshot_3.jpg
          30 kB

          Activity

            People

            Assignee:
            brendanheywood Brendan Heywood
            Reporter:
            bushido Mark Nielsen
            Peer reviewer:
            Dmitrii Metelkin
            Integrator:
            Andrew Nicols
            Tester:
            Janelle Barcega
            Participants:
            Component watchers:
            Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Sara Arjona (@sarjona), Matteo Scaramuccia, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
            Votes:
            4 Vote for this issue
            Watchers:
            12 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Fix Release Date:
              10/May/21

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 1 day, 30 minutes
                1d 30m