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

Performance boost for data delete request inside the cron

XMLWordPrintable

    • Icon: Improvement Improvement
    • Resolution: Duplicate
    • Icon: Major Major
    • None
    • 3.9.16
    • Logging, Privacy
    • None
    • MOODLE_39_STABLE

      Recently we deleted lots of user accounts manually and approved the delete data requests for deleted users. That resulted that the cron job runs took much longer almost like it was hanging. It turned out to be the `tool_log` component during the data cleanup which was causing in the slow down. We have ~34M records in the `logstore_standard_log` table and the following SQL query takes about ~35 seconds to finish.

      SELECT 
        ctx.id AS ctxid,
        ctx.path AS ctxpath,
        ctx.depth AS ctxdepth,
        ctx.contextlevel AS ctxlevel,
        ctx.instanceid AS ctxinstance,
        ctx.locked AS ctxlocked
      FROM 
        mdl_context ctx
      JOIN (
        SELECT 
          l.contextid
        FROM 
          mdl_logstore_standard_log l
        WHERE 
          l.userid = USERID
          OR l.relateduserid = USERID
          OR l.realuserid = USERID) 
      target ON ctx.id = target.contextid;

      After adding two more indexes on the `logstore_standard_log`  table for `relateduserid` and `realuserid` and rewriting the SQL query to

      SELECT
       ctx.id AS ctxid,
       ctx.path AS ctxpath,
       ctx.depth AS ctxdepth,
       ctx.contextlevel AS ctxlevel,
       ctx.instanceid AS ctxinstance,
       ctx.locked AS ctxlocked
      FROM 
       mdl_context ctx
      JOIN (
       SELECT
        l.contextid
       FROM 
        mdl_logstore_standard_log l
       WHERE l.userid = 'USERID'
       
       UNION
       
       SELECT
        l.contextid
       FROM mdl_logstore_standard_log l
       WHERE l.relateduserid = 'USERID'
       
       UNION
       
       SELECT
        l.contextid
       FROM
        mdl_logstore_standard_log l
       WHERE
        l.realuserid = 'USERID')
      target ON ctx.id = target.contextid;

      the query was executing in <1 second.

      As a side note on smaller tables the new query is slower but on larger tables significantly faster. Maybe this would be a good thing to change.

            Unassigned Unassigned
            jackermann Jakob Ackermann
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated:
              Resolved:

                Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.