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

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

    XMLWordPrintable

Details

    • MOODLE_310_STABLE, MOODLE_38_STABLE, MOODLE_39_STABLE
    • MOODLE_38_STABLE, MOODLE_39_STABLE
    • m39_MDL-67440
    • 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.

    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

          has a non-specific relationship to

          Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-66498 Analytics cleanup() causes error for large moodle installations under postgres

          • Major - Major loss of function, incorrect output
          • Closed

          Activity

            People

              matt.clarkson Matt Clarkson
              danmarsden Dan Marsden
              Dan Marsden Dan Marsden
              Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
              Janelle Barcega Janelle Barcega
              David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo
              Votes:
              1 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                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