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

Speed up file system \core\task\file_trash_cleanup_task - version 2

    XMLWordPrintable

Details

    • Improvement
    • Status: Open
    • Minor
    • Resolution: Unresolved
    • 3.9.7
    • None
    • Tasks
    • MOODLE_39_STABLE

    Description

      Thanks brendanheywood for implementing MDL-67635. However, it seems like the task is still very slow at quering data from DB.

      It takes about 30 minutes to complete the task and as I can see the following SQL is the cause:

      moodle_39_uat=> \timing on
      Timing is on.
      moodle_39_uat=> 
      moodle_39_uat=> 
      moodle_39_uat=> SELECT p.*
      moodle_39_uat->                   FROM mdl_files p
      moodle_39_uat->              LEFT JOIN mdl_files o ON (p.filename = o.contenthash)
      moodle_39_uat->                  WHERE p.contextid = 1
      moodle_39_uat->                    AND p.component = 'core'
      moodle_39_uat->                    AND (p.filearea = 'preview' OR p.filearea = 'documentconversion')
      moodle_39_uat->                    AND p.itemid = 0
      moodle_39_uat->                    AND o.id IS NULL;
      Time: 1126423.504 ms (18:46.424)
      

      Only 7 rows returned.

      Query explain:

      moodle_39_uat=> EXPLAIN SELECT p.*
      moodle_39_uat->                   FROM mdl_files p
      moodle_39_uat->              LEFT JOIN mdl_files o ON (p.filename = o.contenthash)
      moodle_39_uat->                  WHERE p.contextid = 1
      moodle_39_uat->                    AND p.component = 'core'
      moodle_39_uat->                    AND (p.filearea = 'preview' OR p.filearea = 'documentconversion')
      moodle_39_uat->                    AND p.itemid = 0
      moodle_39_uat->                    AND o.id IS NULL;
                                                            QUERY PLAN                                                       
      -----------------------------------------------------------------------------------------------------------------------
       Gather  (cost=1129.53..143047.51 rows=1 width=294)
         Workers Planned: 1
         ->  Nested Loop Left Join  (cost=129.53..142047.41 rows=1 width=294)
               Filter: (o.id IS NULL)
               ->  Parallel Index Scan using mdl_file_comfilconite_ix on mdl_files p  (cost=0.69..55609.78 rows=7 width=294)
                     Index Cond: (((component)::text = 'core'::text) AND (contextid = 1) AND (itemid = 0))
                     Filter: (((filearea)::text = 'preview'::text) OR ((filearea)::text = 'documentconversion'::text))
               ->  Bitmap Heap Scan on mdl_files o  (cost=128.84..12317.07 rows=3116 width=49)
                     Recheck Cond: ((p.filename)::text = (contenthash)::text)
                     ->  Bitmap Index Scan on mdl_file_con_ix  (cost=0.00..128.06 rows=3116 width=0)
                           Index Cond: ((contenthash)::text = (p.filename)::text)
      (11 rows)
      

      The mdl_files table has an extra index, but nothing is missing:

      root@629cf2f9b27e:/site# php admin/cli/check_database_schema.php 
      -------------------------------------------------------------------------------
      files
       * Unexpected index 'mdl_file_time_ix'.
      -------------------------------------------------------------------------------
       
      moodle_39_uat=> \d mdl_files
                                              Table "public.mdl_files"
           Column      |          Type          | Collation | Nullable |                Default                
      -----------------+------------------------+-----------+----------+---------------------------------------
       id              | bigint                 |           | not null | nextval('mdl_files_id_seq'::regclass)
       contenthash     | character varying(40)  |           | not null | ''::character varying
       pathnamehash    | character varying(40)  |           | not null | ''::character varying
       contextid       | bigint                 |           | not null | 
       component       | character varying(100) |           | not null | ''::character varying
       filearea        | character varying(50)  |           | not null | ''::character varying
       itemid          | bigint                 |           | not null | 
       filepath        | character varying(255) |           | not null | ''::character varying
       filename        | character varying(255) |           | not null | ''::character varying
       userid          | bigint                 |           |          | 
       filesize        | bigint                 |           | not null | 
       mimetype        | character varying(100) |           |          | 
       status          | bigint                 |           | not null | 0
       source          | text                   |           |          | 
       author          | character varying(255) |           |          | 
       license         | character varying(255) |           |          | 
       timecreated     | bigint                 |           | not null | 
       timemodified    | bigint                 |           | not null | 
       sortorder       | bigint                 |           | not null | 0
       referencefileid | bigint                 |           |          | 
      Indexes:
          "mdl_file_id_pk" PRIMARY KEY, btree (id)
          "mdl_file_pat_uix" UNIQUE, btree (pathnamehash)
          "mdl_file_comfilconite_ix" btree (component, filearea, contextid, itemid)
          "mdl_file_con2_ix" btree (contextid)
          "mdl_file_con_ix" btree (contenthash)
          "mdl_file_lic_ix" btree (license)
          "mdl_file_ref_ix" btree (referencefileid)
          "mdl_file_time_ix" btree (contenthash, filesize, timecreated, id) WHERE filesize > 0
          "mdl_file_use_ix" btree (userid)
      

      Task profiling:

       

      Attachments

        Activity

          People

            Unassigned Unassigned
            mikhailgolenkov Misha Golenkov
            David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated: