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

              David.Saylor David Saylor
              josephfelix Joseph Felix
              Jun Pataleta Jun Pataleta
              Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
              CiBoT CiBoT
              Amaia Anabitarte, Bas Brands, Carlos Escobedo, Laurent David, Raquel Ortega, Sabina Abellan, Sara Arjona (@sarjona), Matteo Scaramuccia, David Woloszyn, Huong Nguyen, Jake Dallimore, Michael Hawkins, Stevani Andolo
              Votes:
              4 Vote for this issue
              Watchers:
              12 Start watching this issue

              Dates

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