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

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

    XMLWordPrintable

    Details

    • Testing Instructions:
      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.
    • Affected Branches:
      MOODLE_310_STABLE, MOODLE_38_STABLE, MOODLE_39_STABLE
    • Fixed Branches:
      MOODLE_38_STABLE, MOODLE_39_STABLE
    • Pull 3.8 Branch:
      m38_MDL-67440
    • Pull 3.9 Branch:
      m39_MDL-67440
    • Pull Master Branch:
      master_MDL-67440

      Description

      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!

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              matt.clarkson Matt Clarkson
              Reporter:
              danmarsden Dan Marsden
              Peer reviewer:
              Dan Marsden
              Integrator:
              Eloy Lafuente (stronk7)
              Tester:
              Janelle Barcega
              Participants:
              Component watchers:
              Elizabeth Dalton, Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Sara Arjona (@sarjona)
              Votes:
              1 Vote for this issue
              Watchers:
              7 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                14/Sep/20

                  Time Tracking

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