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

\core\task\analytics_cleanup_task extremely slow on postgres site.

XMLWordPrintable

    • MOODLE_310_STABLE, MOODLE_38_STABLE, MOODLE_39_STABLE
    • MOODLE_38_STABLE, MOODLE_39_STABLE
    • master_MDL-67440
    • Hide

      Create a new, empty, site, with default configuration (this needs to be checked at very least with postgres and one of mysql/mariadb).

      1. Go to Site Administration > Users > Accounts > Upload Users
        1. Upload the provided file (2000Users-Upload.csv) to create 2000 users (in the second page you'll need to adjust the "Email address" (in Default values) to "%u@example.com", then press "Upload users".
        2. Wait till the process ends (can take a while).
        3. Go to Admin -> Users -> Accounts -> Browse list of users.
        4. Verify that there are a lot of users (around 2000) in the site.
      2. Run the predictions task to calculate some "upcoming... predictions" (can take a while, you will see a lot of "Analysing id...." lines in the output). 

        php admin/cli/scheduled_task.php --execute='\tool_analytics\task\predict_models'

      3. Run the cleanup task 

        php admin/cli/scheduled_task.php --execute='\core\task\analytics_cleanup_task'

      4. Verify it ends without error.
      Show
      Create a new, empty, site, with default configuration (this needs to be checked at very least with postgres and one of mysql/mariadb). Go to Site Administration > Users > Accounts > Upload Users Upload the provided file (2000Users-Upload.csv) to create 2000 users (in the second page you'll need to adjust the "Email address" (in Default values) to "%u@example.com", then press "Upload users". Wait till the process ends (can take a while). Go to Admin -> Users -> Accounts -> Browse list of users. Verify that there are a lot of users (around 2000) in the site. Run the predictions task to calculate some "upcoming... predictions" (can take a while, you will see a lot of "Analysing id...." lines in the output). php admin/cli/scheduled_task.php --execute='\tool_analytics\task\predict_models' Run the cleanup task php admin/cli/scheduled_task.php --execute='\core\task\analytics_cleanup_task' Verify it ends without error.

      The following part of the clean up task seems to take hours on my local machine running postgres (it's not optimised well - but we're also seeing this take ages on prod server as well.)

       

      https://github.com/moodle/moodle/blob/master/analytics/classes/manager.php#L628

      $DB->delete_records_select('analytics_predictions', "contextid NOT IN ($contextsql)"); 
      $DB->delete_records_select('analytics_indicator_calc', "contextid NOT IN ($contextsql)");

       

      Changing it to use a join makes it run in less than a second

      -        $contextsql = "SELECT id FROM {context} ctx";
      		 
      -        $DB->delete_records_select('analytics_predictions', "contextid NOT IN ($contextsql)");
      		 
      -        $DB->delete_records_select('analytics_indicator_calc', "contextid NOT IN ($contextsql)");
      		 
      +        $DB->execute("DELETE FROM {analytics_predictions} WHERE id IN (
      		 
      +                        SELECT p.id
      		 
      +                        FROM {analytics_predictions} p
      		 
      +                        LEFT JOIN {context} ctx ON p.contextid = ctx.id
      		 
      +                        WHERE ctx.id IS NULL
      		 
      +                     )");
      		 
      +
      		 
      +        $DB->execute("DELETE FROM {analytics_indicator_calc} WHERE id IN (
      		 
      +                        SELECT c.id
      		 
      +                        FROM {analytics_indicator_calc} c
      		 
      +                        LEFT JOIN {context} ctx ON c.contextid = ctx.id
      		 
      +                        WHERE ctx.id IS NULL
      		 
      +                    )");

       

      Haven't tested on mysql/other dbs sorry!

        1. 2000Users-Upload.csv
          91 kB
        2. 70000Users-Upload.csv
          3.59 MB
        3. image-2020-07-22-10-40-00-587.png
          image-2020-07-22-10-40-00-587.png
          235 kB

            matt.clarkson Matt Clarkson
            danmarsden Dan Marsden
            Dan Marsden Dan Marsden
            Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
            Janelle Barcega Janelle Barcega
            Votes:
            1 Vote for this issue
            Watchers:
            7 Start watching this issue

              Created:
              Updated:
              Resolved:

                Estimated:
                Original Estimate - 0 minutes
                0m
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 3 hours, 55 minutes
                3h 55m

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