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

Remove most SQL_QUERY_AUX calls from running on the primary db connection

XMLWordPrintable

    • Any
    • MOODLE_401_STABLE
    • MOODLE_401_STABLE
    • MDL-71000-SQL_QUERY_AUX_READONLY
    • Hide
      1. Covered by unit tests.

      The following testing instructions were added before the unit tests. We keep them here in case someone wanted to test the patch manually.

      1. Unit tests should give a good cover of change. tests reside in lib/dml/tests. Some test are always skipped, they are database driver specific and run only if the connected database is postgres/mysql.
      2. Change in master/readonly reads
        • Config: in absence of a proper master/readonly setup a mock one can be used:

          $CFG->dboptions = [
            'readonly' => [
              'instance' => [ $CFG->dbhost ],
            ]
          ];
          $CFG->debugdisplay = 1;
          $CFG->perfdebug = 15;
          $CFG->debugpageinfo = 1;

        • Footer info, observed change: change in master/readonly reads. Some examples, could be done for any page. You may need to clear cache though.
          • Visit admin/index.php?cache=1 (notifications page)
          • Confirm the number of reads on the slave / readonly connection is higher

            Before patch:
            DB reads/writes: 89/1
            DB reads from slave: 81 (min 79, max 81)

            After patch:
            DB reads/writes: 89/1
            DB reads from slave: 87 average (min 85, max 88)
          • Visit /my/ (dashboard page)
          • Confirm the number of reads on the slave / readonly connection is higher

            Before patch:
            DB reads/writes: 47/1
            DB reads from slave: 37 (min 37, max 42)

            After patch:
            DB reads/writes: 47/0
            DB reads from slave: 46 average (min 41, max 46)
      3. Change in readwrite database traffic - optional. In order to fully test, one needs a proper primary/replica setup. What follows is a possible postges setup, same goes for mysql:
        • Testbed:
          • Database: Two postgres containers, one master and one readonly standby; install iptraf-ng in containers (apt install iptraf-ng or similar)
          • Installation:
            • vanilla Moodle fresh installation, only admin user, dbhost set to primary IP
            • config.php dboptions 'readonly' 'instance' set to replica IP
        • Database traffic change:
          • Action: refresh Dashboard Verification method: observing iptraf-ng in containers - in both database containers execute shell, install iptraf-ng (if not installed), run iptraf-ng -> IP traffic monitor
            • Before patch: master: 14 kB readonly: 66 kB
            • After patch: master: 9 kB readonly: 71 kB
          • This can be repeated for any page
      Show
      Covered by unit tests. The following testing instructions were added before the unit tests. We keep them here in case someone wanted to test the patch manually. Unit tests should give a good cover of change. tests reside in lib/dml/tests. Some test are always skipped, they are database driver specific and run only if the connected database is postgres/mysql. Change in master/readonly reads Config: in absence of a proper master/readonly setup a mock one can be used: $CFG->dboptions = [   'readonly' => [     'instance' => [ $CFG->dbhost ],   ] ]; $CFG->debugdisplay = 1; $CFG->perfdebug = 15; $CFG->debugpageinfo = 1; Footer info, observed change: change in master/readonly reads. Some examples, could be done for any page. You may need to clear cache though. Visit admin/index.php?cache=1 (notifications page) Confirm the number of reads on the slave / readonly connection is higher – Before patch: DB reads/writes: 89/1 DB reads from slave: 81 (min 79, max 81) – After patch: DB reads/writes: 89/1 DB reads from slave: 87 average (min 85, max 88) Visit /my/ (dashboard page) Confirm the number of reads on the slave / readonly connection is higher – Before patch: DB reads/writes: 47/1 DB reads from slave: 37 (min 37, max 42) – After patch: DB reads/writes: 47/0 DB reads from slave: 46 average (min 41, max 46) Change in readwrite database traffic - optional. In order to fully test, one needs a proper primary/replica setup. What follows is a possible postges setup, same goes for mysql: Testbed : Database : Two postgres containers, one master and one readonly standby; install iptraf-ng in containers ( apt install iptraf-ng or similar) Installation : vanilla Moodle fresh installation, only admin user, dbhost set to primary IP config.php dboptions 'readonly' 'instance' set to replica IP Database traffic change : Action:  refresh Dashboard Verification method : observing iptraf-ng in containers - in both database containers execute shell, install iptraf-ng (if not installed), run iptraf-ng -> IP traffic monitor Before patch: master: 14 kB readonly: 66 kB After patch: master: 9 kB readonly: 71 kB This can be repeated for any page

      There are a bunch of various AUX queries which don't need to be on the primary such as:

      https://github.com/moodle/moodle/blob/master/lib/dml/pgsql_native_moodle_database.php#L354

      https://github.com/moodle/moodle/blob/master/lib/dml/pgsql_native_moodle_database.php#L761

      In fact almost all AUX should be safe to go to a read replica but I'm not sure we can assume that for 100% of AUX queries.

      I'm proposing the simplest way might be to introduce a new type of query so we have both  SQL_QUERY_AUX and SQL_QUERY_AUX_READONLY and then each driver can easily declare which is which.

            srdjan Srdjan Jankovic
            brendanheywood Brendan Heywood
            Kevin Pham Kevin Pham
            Victor Déniz Falcón Victor Déniz Falcón
            CiBoT CiBoT
            Votes:
            1 Vote for this issue
            Watchers:
            10 Start watching this issue

              Created:
              Updated:
              Resolved:

                Estimated:
                Original Estimate - 0 minutes
                0m
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 2 days, 2 hours, 35 minutes
                2d 2h 35m

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