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

Optimize SQL for Task logs page

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Duplicate
    • Affects Version/s: 3.7.4, 3.8.1, 3.9
    • Fix Version/s: None
    • Component/s: Tasks
    • Affected Branches:
      MOODLE_37_STABLE, MOODLE_38_STABLE, MOODLE_39_STABLE

      Description

      On big sites it takes 30-60 secs to open Task logs page even when log retention is enabled. It might take more time if `task_logretention` and `task_logretainruns` are set to bigger values than defaults.

      It happens because  this SQL uses LIKEs in WHERE clause and that blocks using db indexes.

      If we can exclude logs that have classname in output field and replace LIKE by `=` for classname field we will let this SQL hit the index and drop down execution time to couple of seconds even on big sites.

      Current SQL:

         SELECT tl.id, tl.type, tl.component, tl.classname, tl.userid, tl.timestart, tl.timeend,
                tl.dbreads, tl.dbwrites, tl.result,
                tl.dbreads + tl.dbwrites AS db,
                tl.timeend - tl.timestart AS duration,
                u.id AS userid2,u.picture AS userpicture,u.firstname AS userfirstname,u.lastname AS userlastname,u.firstnamephonetic AS userfirstnamephonetic,u.lastnamephonetic AS userlastnamephonetic,u.middlename AS usermiddlename,u.alternatename AS useralternatename,u.imagealt AS userimagealt,u.email AS useremail
           FROM mdl_task_log tl
      LEFT JOIN mdl_user u ON u.id = tl.userid
          WHERE (classname ILIKE '%core\\task\\task\_log\_cleanup\_task%' ESCAPE '\' OR output ILIKE '%core\\task\\task\_log\_cleanup\_task%' ESCAPE '\')
       ORDER BY timestart DESC;
      

      Proposed SQL:

         SELECT tl.id, tl.type, tl.component, tl.classname, tl.userid, tl.timestart, tl.timeend,
                tl.dbreads, tl.dbwrites, tl.result,
                tl.dbreads + tl.dbwrites AS db,
                tl.timeend - tl.timestart AS duration,
                u.id AS userid2,u.picture AS userpicture,u.firstname AS userfirstname,u.lastname AS userlastname,u.firstnamephonetic AS userfirstnamephonetic,u.lastnamephonetic AS userlastnamephonetic,u.middlename AS usermiddlename,u.alternatename AS useralternatename,u.imagealt AS userimagealt,u.email AS useremail
           FROM mdl_task_log tl
      LEFT JOIN mdl_user u ON u.id = tl.userid
          WHERE classname = 'core\task\task_log_cleanup_task'
          ORDER BY timestart DESC;
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              mikhailgolenkov Mikhail Golenkov
              Reporter:
              mikhailgolenkov Mikhail Golenkov
              Participants:
              Component watchers:
              Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Sara Arjona (@sarjona), Víctor Déniz Falcón
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: