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

Additional caching of pg_field_type postgres field metadata

    XMLWordPrintable

Details

    • MOODLE_400_STABLE
    • MDL-68164-cache_pg_type
    • Hide

      Do this before the patch is applied and compare it to after

      Setup
      1. Edit postgresql.conf to set up the slowlog to log all db queries, eg in /etc/postgresql/10/main/postgresql.conf file set 

        log_min_duration_statement = 0
        

      2. Restart your PostgreSQL server for the config changes to take effect. E.g. "sudo service postgresql restart"
      3. Copy the test script testpg.php to your Moodle root folder.
      4. Open a separate terminal window and tail the Postgres logs. e.g. for PostgreSQL 10 in Ubuntu:

        tail -f /var/log/postgresql/postgresql-10-main.log

      Before patch test
      1. Run the test script.

         php testpg.php

      2. Run it 2-3 times to make sure everything is warm and stable.
      3. You'll see the following logs every time you run the test script.

        LOG: duration: 3.448 ms statement: select oid,typname from pg_type

      4. On the log window press enter a couple of times to make space and distinguish the new logs from the old logs.
      After patch test
      1. On the log window press enter a couple of times to make space and distinguish the new logs from the old logs.
      2. Run the test script again
      3. You'll see the following log on your first test script run after the patch.

        LOG: duration: 3.448 ms statement: select oid,typname from pg_type

      4. Run the test script again for 2 or 3 more times. Make sure you press enter a couple of times each time you run the test script to distinguish new logs.
      5. Confirm that you don't see the following log in the subsequent runs.

        LOG: duration: 3.448 ms statement: select oid,typname from pg_type

      Show
      Do this before the patch is applied and compare it to after Setup Edit postgresql.conf to set up the slowlog to log all db queries, eg in /etc/postgresql/10/main/postgresql.conf file set  log_min_duration_statement = 0 Restart your PostgreSQL server for the config changes to take effect. E.g. "sudo service postgresql restart" Copy the test script testpg.php to your Moodle root folder. Open a separate terminal window and tail the Postgres logs. e.g. for PostgreSQL 10 in Ubuntu: tail -f /var/log/postgresql/postgresql-10-main.log Before patch test Run the test script. php testpg.php Run it 2-3 times to make sure everything is warm and stable. You'll see the following logs every time you run the test script. LOG: duration: 3.448 ms statement: select oid,typname from pg_type On the log window press enter a couple of times to make space and distinguish the new logs from the old logs. After patch test On the log window press enter a couple of times to make space and distinguish the new logs from the old logs. Run the test script again You'll see the following log on your first test script run after the patch. LOG: duration: 3.448 ms statement: select oid,typname from pg_type Run the test script again for 2 or 3 more times. Make sure you press enter a couple of times each time you run the test script to distinguish new logs. Confirm that you don't see the following log in the subsequent runs. LOG: duration: 3.448 ms statement: select oid,typname from pg_type

    Description

      Both pg_field_type and pg_field_name are called a ton on every page. They are typically extremely fast but they are still load on the db for something which is static in many situations. pg_num_fields might also be in the same boat.

      The driver here is not really increasing an individual page load but the total sql load.

      I suspect it's not possible to use MUC, and it would just shift load to redis etc, so probably the way is directly into apcu and behind a config.php option.

      Attachments

        1. MDL-68164_After Patch_1.png
          MDL-68164_After Patch_1.png
          179 kB
        2. MDL-68164_After Patch_2.png
          MDL-68164_After Patch_2.png
          116 kB
        3. MDL-68164_Before Patch.png
          MDL-68164_Before Patch.png
          212 kB
        4. testpg.php
          0.2 kB

        Issue Links

          Activity

            People

              brendanheywood Brendan Heywood
              brendanheywood Brendan Heywood
              Jun Pataleta Jun Pataleta
              Angelia Dela Cruz Angelia Dela Cruz
              Votes:
              2 Vote for this issue
              Watchers:
              10 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 - 3 hours
                  3h

                  Clockify

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