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

Change postgres lock implementation to not depend on mdl_lock_db

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide

      Mostly covered by existing unit tests. To show that the system is using less db calls overall lets load test the adhoc task queue before and after the patch:

      1) Install this plugin to make testing easier:

      https://github.com/catalyst/moodle-tool_testtasks

      2) To expose the number of housekeeping db calls temp patch this code:

      +++ b/admin/tool/task/cli/adhoc_task.php
      @@ -108,10 +108,13 @@ mtrace("Server Time: {$humantimenow}\n");
       
       // Run all adhoc tasks.
       $taskcount = 0;
      +$predbqueries = $DB->perf_get_queries();
       while (!\core\task\manager::static_caches_cleared_since($timenow) &&
               $task = \core\task\manager::get_next_adhoc_task($timenow)) {
           cron_run_inner_adhoc_task($task);
           $taskcount++;
           unset($task);
      +    mtrace("... used " . ($DB->perf_get_queries() - $predbqueries) . " overhead dbqueries");
      +    $predbqueries = $DB->perf_get_queries();
       }
       mtrace("Ran {$taskcount} adhoc tasks found at {$humantimenow}");
      

      3) Test a single ad hoc task in the queue:

      $ php admin/tool/testtasks/cli/queue_adhoc_tasks.php -n=1 -d=1
      $ php admin/tool/task/cli/adhoc_task.php --execute | grep overhead
      </pre>... used 12 overhead dbqueries
      

      after the patch:

      $ php admin/tool/task/cli/adhoc_task.php --execute | grep overhead
      </pre>... used 9 overhead dbqueries
      
      

      At scale this gets tons worse / better. Queue up 1000 tasks:

      $ php admin/tool/testtasks/cli/queue_adhoc_tasks.php -n=1000 -d=1
      

      Now run this ad hoc task processors 5 times:

      $ php admin/tool/task/cli/adhoc_task.php --execute | grep overhead &
      

      and you should see these before the patch:

      ... used 19 overhead dbqueries
      

      and after the patch:

      ... used 13 overhead dbqueries 
      

      Show
      Mostly covered by existing unit tests. To show that the system is using less db calls overall lets load test the adhoc task queue before and after the patch: 1) Install this plugin to make testing easier: https://github.com/catalyst/moodle-tool_testtasks 2) To expose the number of housekeeping db calls temp patch this code: +++ b/admin/tool/task/cli/adhoc_task.php @@ - 108 , 10 + 108 , 13 @@ mtrace( "Server Time: {$humantimenow}\n" ); // Run all adhoc tasks. $taskcount = 0 ; +$predbqueries = $DB->perf_get_queries(); while (!\core\task\manager::static_caches_cleared_since($timenow) && $task = \core\task\manager::get_next_adhoc_task($timenow)) { cron_run_inner_adhoc_task($task); $taskcount++; unset($task); + mtrace( "... used " . ($DB->perf_get_queries() - $predbqueries) . " overhead dbqueries" ); + $predbqueries = $DB->perf_get_queries(); } mtrace( "Ran {$taskcount} adhoc tasks found at {$humantimenow}" ); 3) Test a single ad hoc task in the queue: $ php admin/tool/testtasks/cli/queue_adhoc_tasks.php -n= 1 -d= 1 $ php admin/tool/task/cli/adhoc_task.php --execute | grep overhead </pre>... used 12 overhead dbqueries after the patch: $ php admin/tool/task/cli/adhoc_task.php --execute | grep overhead </pre>... used 9 overhead dbqueries At scale this gets tons worse / better. Queue up 1000 tasks: $ php admin/tool/testtasks/cli/queue_adhoc_tasks.php -n= 1000 -d= 1 Now run this ad hoc task processors 5 times: $ php admin/tool/task/cli/adhoc_task.php --execute | grep overhead & and you should see these before the patch: ... used 19 overhead dbqueries and after the patch: ... used 13 overhead dbqueries
    • Pull Master Branch:
      MDL-65722-no-lock-db

      Description

      The postgres advisory locks works with an integer, but the moodle lock API works with a string. So the postgres impl re-uses the mdl_lock_db table to manage this mapping:

      https://github.com/moodle/moodle/blob/master/lib/classes/lock/postgres_lock_factory.php#L144-L156

      This impl made some sense back when the number of resource keys was discrete, and essentially limited to just the names of scheduled tasks.

      Now I don't think this makes any sense, there are lots of ad hocs tasks and other clients of the Lock API which are single use (eg objectfs leverages this heaps). In our largest client we have 10's of million rows in mdl_lock_db and it is all junk.

      So I'm proposing to eliminate the db lookup and polling and convert this to a deterministic fast hash, eg something like string -> md5 / sha256 -> truncate to N chars -> convert to integer that fits inside a postgres bigint

      https://www.postgresql.org/docs/current/datatype-numeric.html

      bigint 8 bytes large-range integer -9223372036854775808 to 9223372036854775807

      And an upgrade task to truncate that table as needed

       

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                brendanheywood Brendan Heywood
                Reporter:
                brendanheywood Brendan Heywood
                Peer reviewer:
                Matt Porritt
                Participants:
                Component watchers:
                Andrew Nicols, Mathew May, Michael Hawkins, Shamim Rezaie, Simey Lameze, Matteo Scaramuccia, Jake Dallimore, Jun Pataleta
              • Votes:
                4 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated: