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, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, 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:
              15/Jun/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