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

DB performance issue with get_config siteidentifier

    XMLWordPrintable

Details

    • MOODLE_26_STABLE, MOODLE_310_STABLE, MOODLE_400_STABLE
    • MDL-47456-avoid-siteid-db
    • 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

    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

        Issue Links

          Activity

            People

              brendanheywood Brendan Heywood
              bushido Mark Nielsen
              Dmitrii Metelkin Dmitrii Metelkin
              Andrew Lyons Andrew Lyons
              Janelle Barcega Janelle Barcega
              David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo, Matteo Scaramuccia, David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo
              Votes:
              4 Vote for this issue
              Watchers:
              14 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

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