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

Completion cron task very slow when has more than 100k users

    XMLWordPrintable

    Details

      Description

      I have a problem, the completion cron task run on slow mode when system has more than 100k users.

      Relates to completion_cron_mark_started function in file /completion/cron.php

      I suspect a problem in this query inside cron file.
      RETURN MORE THAN 200 MILLION ROWS

      SELECT
      	c.id AS course,
      	u.id AS userid,
      	crc.id AS completionid,
      	ue.timestart AS timeenrolled,
      	ue.timecreated
      FROM
      	{user} u
      INNER JOIN
      	{user_enrolments} ue
       ON ue.userid = u.id
      INNER JOIN
      	{enrol} e
       ON e.id = ue.enrolid
      INNER JOIN
      	{course} c
       ON c.id = e.courseid
      INNER JOIN
      	{role_assignments} ra
       ON ra.userid = u.id
      LEFT JOIN
      	{course_completions} crc
       ON crc.course = c.id
      AND crc.userid = u.id
      WHERE
      	c.enablecompletion = 1
      AND crc.timeenrolled IS NULL
      AND ue.status = 0
      AND e.status = 0
      AND u.deleted = 0
      AND ue.timestart < ?
      AND (ue.timeend > ? OR ue.timeend = 0)
      	$roles
      ORDER BY
      	course,
      	userid
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              David.Saylor David Saylor
              Reporter:
              josephfelix Joseph Felix
              Peer reviewer:
              Jun Pataleta
              Integrator:
              Eloy Lafuente (stronk7)
              Tester:
              CiBoT
              Participants:
              Component watchers:
              Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Sara Arjona (@sarjona), Matteo Scaramuccia, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              4 Vote for this issue
              Watchers:
              12 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Fix Release Date:
                11/May/20

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 6 hours, 15 minutes
                  6h 15m