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

Speed up file system \core\task\file_trash_cleanup_task

    XMLWordPrintable

Details

    • MOODLE_39_STABLE
    • MOODLE_39_STABLE
    • MDL-67635-filesystem-cleanup
    • 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).    

    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

            brendanheywood Brendan Heywood
            brendanheywood Brendan Heywood
            Amaia Anabitarte Amaia Anabitarte
            Sara Arjona (@sarjona) Sara Arjona (@sarjona)
            Anna Carissa Sadia Anna Carissa Sadia
            Matteo Scaramuccia, Andrew Lyons, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Stevani Andolo, Matteo Scaramuccia, Andrew Lyons, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Stevani Andolo, Amaia Anabitarte, Carlos Escobedo, Ferran Recio, Ilya Tregubov, Laurent David, Raquel Ortega, Sara Arjona (@sarjona)
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

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