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

File deletion very slow with large number of file records

    XMLWordPrintable

    Details

      Description

      On a client's site, file deletion is very slow and I've tracked the performance issue down to the file_storage::search_references function (taking up 99% of the processing time, according to xhprof).

      There are 8 million entries in mdl_files and 50 entries in mdl_files_reference.

      There appear to be valid indexes on the 'mdl_files.referencefileid' column and 'mdl_files_reference.id' column.
      However, this query takes about 10 seconds to run:

      SELECT [fields]
      FROM mdl_files f
      JOIN mdl_files_reference r ON f.referencefileid = r.id
      JOIN mdl_repository_instances ri ON r.repositoryid = ri.id
      WHERE r.referencehash = '[reference]'
      AND (f.component <> 'user' OR f.filearea <> 'draft');

      Whereas these two queries take 0.00 seconds to run each:

      SELECT r.id
      FROM mdl_files_reference r
      WHERE r.referencehash = '[reference]';

      SELECT [fields]
      FROM mdl_files f
      JOIN mdl_files_reference r ON f.referencefileid = r.id
      JOIN mdl_repository_instances ri ON r.repositoryid = ri.id
      WHERE r.id in ([values returned by first query])
      AND (f.component <> 'user' OR f.filearea <> 'draft');

      (Obviously, I've omitted the long list of fields and inserted a note where the reference hash + results of the first query are found).

      After messing around with explain, I still can't understand why the first query causes a complete search of all 8 million records, but the second goes direct to the relevant indexes.

      Would it be sensible to switch to split queries in Moodle core?

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                1 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  10/Nov/14