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

Speed up file system \core\task\file_trash_cleanup_task

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide
      1. Login as admin.
      2. Create user (u1).
      3. Logout.
      4. Login as u1.
      5. Edit the profile and upload a picture.
      6. Logout.
      7. Login as admin.
      8. Access to "Site administration / Users / Accounts / Browse list of users" and remove user u1.
      9. Execute SQL and annotate the result (in empty installations, it should be 1): 

        SELECT count(p.*)
        FROM mdl_files p
        LEFT JOIN mdl_files o ON (p.filename = o.contenthash)
        WHERE p.component = 'core'
        AND (p.filearea = 'preview' OR p.filearea = 'documentconversion')
        AND p.itemid = 0
        AND o.id IS NULL

      10. Run the cleanup task: 

        php admin/tool/task/cli/schedule_task.php --execute='\core\task\file_trash_cleanup_task'

      11. Execute again SQL and annotate the result: 

        SELECT count(p.*)
        FROM mdl_files p
        LEFT JOIN mdl_files o ON (p.filename = o.contenthash)
        WHERE p.component = 'core'
        AND (p.filearea = 'preview' OR p.filearea = 'documentconversion')
        AND p.itemid = 0
        AND o.id IS NULL

      12. Check the result now is lower than number in #9 (in empty installations, it should be 0).

       
       

      Show
      Login as admin. Create user (u1). Logout. Login as u1. Edit the profile and upload a picture. Logout. Login as admin. Access to "Site administration / Users / Accounts / Browse list of users" and remove user u1. Execute SQL and annotate the result (in empty installations, it should be 1):  SELECT count(p.*) FROM mdl_files p LEFT JOIN mdl_files o ON (p.filename = o.contenthash) WHERE p.component = 'core' AND (p.filearea = 'preview' OR p.filearea = 'documentconversion') AND p.itemid = 0 AND o.id IS NULL Run the cleanup task:  php admin/tool/task/cli/schedule_task.php --execute='\core\task\file_trash_cleanup_task' Execute again SQL and annotate the result:  SELECT count(p.*) FROM mdl_files p LEFT JOIN mdl_files o ON (p.filename = o.contenthash) WHERE p.component = 'core' AND (p.filearea = 'preview' OR p.filearea = 'documentconversion') AND p.itemid = 0 AND o.id IS NULL Check the result now is lower than number in #9 (in empty installations, it should be 0).    
    • Affected Branches:
      MOODLE_39_STABLE
    • Fixed Branches:
      MOODLE_39_STABLE
    • Pull Master Branch:
      MDL-67635-filesystem-cleanup

      Description

      There are two sql queries with a left join which are pretty bad at scale:

      https://github.com/moodle/moodle/blob/master/lib/filestorage/file_storage.php#L2211-L2249

      At minimum these could be combined into a single sql -> result set loop

        Attachments

          Activity

            People

            • Assignee:
              brendanheywood Brendan Heywood
              Reporter:
              brendanheywood Brendan Heywood
              Peer reviewer:
              Amaia Anabitarte
              Integrator:
              Sara Arjona (@sarjona)
              Tester:
              Anna Carissa Sadia
              Participants:
              Component watchers:
              Matteo Scaramuccia, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Matteo Scaramuccia, Jake Dallimore, Jun Pataleta, Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Sara Arjona (@sarjona), Víctor Déniz Falcón
            • Votes:
              0 Vote for this issue
              Watchers:
              7 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 - 1 hour, 35 minutes
                1h 35m