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

Minimize unneeded uses of recordset during moodle bootstrap

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide

      As this is a performance patch it's easier to compare before and after the patch is applied.

       

      1) Apply a small hack to expose what sql is going to the primary db:

      diff --git a/lib/dml/moodle_read_slave_trait.php b/lib/dml/moodle_read_slave_trait.php
      index 95d05a49845..4452dcce12d 100644
      --- a/lib/dml/moodle_read_slave_trait.php
      +++ b/lib/dml/moodle_read_slave_trait.php
      @@ -284,6 +284,7 @@ trait moodle_read_slave_trait {
                   $this->set_db_handle($this->dbhreadonly);
                   return;
               }
      +error_log($sql . ' found at ' . format_backtrace(debug_backtrace(), true));
               $this->set_dbhwrite();
           }
      
      

       

      2) Tail the logs and load a page and confirm you can see all the sql reads and writes

      3) Now setup a fake read replica in config.php, you can set the replica to use the same database as the main one, eg:

      $CFG->dbhost = 'localhost';
      ...
      $CFG->dboptions = array (
          ...    'readonly' => [
              'instance' => 'localhost',
          ],
      );
      

       4) Load a page and confirm the log is now much reduce and now only shows just writes and a few selected queries to mdl_config, mdl_sessions and 2 cursors

      5) Apply the patch

      6) Reload and confirm that the two cursor queries or now missing, but everything still works fine

       Rinse and repeat with various important pages and confirm that there are no extraneous cursors in use

      a) / home page

      b) /my/ dashboard

      c) a course page

       

      Show
      As this is a performance patch it's easier to compare before and after the patch is applied.   1) Apply a small hack to expose what sql is going to the primary db: diff --git a/lib/dml/moodle_read_slave_trait.php b/lib/dml/moodle_read_slave_trait.php index 95d05a49845..4452dcce12d 100644 --- a/lib/dml/moodle_read_slave_trait.php +++ b/lib/dml/moodle_read_slave_trait.php @@ - 284 , 6 + 284 , 7 @@ trait moodle_read_slave_trait { $ this ->set_db_handle($ this ->dbhreadonly); return ; } +error_log($sql . ' found at ' . format_backtrace(debug_backtrace(), true )); $ this ->set_dbhwrite(); }   2) Tail the logs and load a page and confirm you can see all the sql reads and writes 3) Now setup a fake read replica in config.php, you can set the replica to use the same database as the main one, eg: $CFG->dbhost = 'localhost' ; ... $CFG->dboptions = array ( ... 'readonly' => [ 'instance' => 'localhost' , ], );  4) Load a page and confirm the log is now much reduce and now only shows just writes and a few selected queries to mdl_config, mdl_sessions and 2 cursors 5) Apply the patch 6) Reload and confirm that the two cursor queries or now missing, but everything still works fine  Rinse and repeat with various important pages and confirm that there are no extraneous cursors in use a) / home page b) /my/ dashboard c) a course page  
    • Pull 3.11 Branch:
      MDL-70997-avoid-recordset-bootstrap-MOODLE_311_STABLE
    • Pull Master Branch:
      MDL-70997-avoid-recordset-bootstrap

      Description

      We want the moodle bootstrap to be as minimal and lightweight as possible, and using a recordset forces db traffic onto the primary instead of a replica. There are a couple places which use a recordset for no reason, ie the data returned is very small and processed in memory either way, so these can be swapped back to normal get_records called and shifted to a replica db.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              brendanheywood Brendan Heywood
              Reporter:
              brendanheywood Brendan Heywood
              Participants:
              Component watchers:
              Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias, Sujith Haridasan, Andrew Nicols, Dongsheng Cai, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Ilya Tregubov, Sara Arjona (@sarjona), Matteo Scaramuccia, Andrew Nicols, Dongsheng Cai, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: