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

Optimise query in files api to use existing index

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide

      When testing please note that master and stable branches are different

      1. Set up dropbox repo and filesystem repo
      2. Add shortcuts to files from dropbox and filesystem
      3. Run cron, make sure that dropbox cron was executed and there were no errors there
      4. Remove instances of filesystem and dropbox repo
      5. Make sure that existing shortcuts were converted to copies
      Show
      When testing please note that master and stable branches are different Set up dropbox repo and filesystem repo Add shortcuts to files from dropbox and filesystem Run cron, make sure that dropbox cron was executed and there were no errors there Remove instances of filesystem and dropbox repo Make sure that existing shortcuts were converted to copies
    • Difficulty:
      Easy
    • Affected Branches:
      MOODLE_26_STABLE
    • Fixed Branches:
      MOODLE_27_STABLE, MOODLE_28_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      MDL-49641-get_external_files_master

      Description

      Hi!

      I reported a comment into our forums [1], and Howard Miller suggested me to create an issue, given that he suspects it may be an issue. And here I am.

      In our University (with Moodle 2.4) we have enabled the dropbox repository and we were having performance troubles in the MySQL server side regarding to the temporary space (that space used for orderings and so).

      The affecting query was this one:

      mysql> explain SELECT f.id AS id, f.contenthash, f.pathnamehash, f.contextid, f.component, f.filearea, f.itemid, f.filepath, f.filename, f.userid, f.filesize, f.mimetype, f.status, f.source, f.author, f.license, f.timecreated, f.timemodified, f.sortorder, f.referencefileid, r.repositoryid AS repositoryid, r.reference AS reference, r.lastsync AS referencelastsync, r.lifetime AS referencelifetime FROM mdl_files f LEFT JOIN mdl_files_reference r ON f.referencefileid = r.id WHERE r.repositoryid = '6' ORDER BY sortorder, itemid, filepath, filename;
      ----------------------------------------------------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      ----------------------------------------------------------------------------------------------------------------------------------------+

      1 SIMPLE f ALL mdl_file_ref_ix NULL NULL NULL 3376658 Using filesort
      1 SIMPLE r eq_ref PRIMARY,mdl_filerefe_repref_uix,mdl_filerefe_rep_ix PRIMARY 8 moodle.f.referencefileid 1 Using where

      ----------------------------------------------------------------------------------------------------------------------------------------+
      2 rows in set (0.00 sec)

      That "Using filesort" is aweful and makes the cron abort for having insufficient space for the temporary part in the Mysql side. The message shown in the logs is like this:

      Processing cron function for repository_dropbox...
      Default exception handler: S'ha produït un error en llegir a la base de dades Debug: Error writing file '/global/mysqltmp/MYR8ayOe' (Errcode: 49)
      SELECT f.id AS id, f.contenthash, f.pathnamehash, f.contextid, f.component, f.filearea, f.itemid, f.filepath, f.filename, f.userid, f.filesize, f.mimetype, f.status, f.source, f.author, f.license, f.timecr
      eated, f.timemodified, f.sortorder, f.referencefileid, r.repositoryid AS repositoryid, r.reference AS reference, r.lastsync AS referencelastsync, r.lifetime AS referencelifetime
      FROM mdl_files f
      LEFT JOIN mdl_files_reference r
      ON f.referencefileid = r.id
      WHERE r.repositoryid = ? ORDER BY sortorder, itemid, filepath, filename
      [array (
      0 => '6',
      )]
      Error code: dmlreadexception

      • line 426 of /lib/dml/moodle_database.php: dml_read_exception thrown
      • line 1023 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
      • line 383 of /lib/filestorage/file_storage.php: call to mysqli_native_moodle_database->get_records_sql()
      • line 707 of /repository/dropbox/lib.php: call to file_storage->get_external_files()
      • line 724 of /repository/dropbox/lib.php: call to repository_dropbox->cron()
      • line 534 of /lib/cronlib.php: call to repository_dropbox_cron()
      • line 417 of /lib/cronlib.php: call to cron_execute_plugin_type()
      • line 61 of /admin/cli/cron.php: call to cron_run()

      Having seen that, I decided to workaround this issue by generating a new index to improve this query's performance.

      mysql> alter table mdl_files add index mdl_file_refsoritefilfil_ix (referencefileid,sortorder,itemid,filepath,filename);
      Query OK, 0 rows affected (1 min 8.30 sec)
      Records: 0 Duplicates: 0 Warnings: 0

      Now, the result is like this:

      mysql> explain SELECT f.id AS id, f.contenthash, f.pathnamehash, f.contextid, f.component, f.filearea, f.itemid, f.filepath, f.filename, f.userid, f.filesize, f.mimetype, f.status, f.source, f.author, f.license, f.timecreated, f.timemodified, f.sortorder, f.referencefileid, r.repositoryid AS repositoryid, r.reference AS reference, r.lastsync AS referencelastsync, r.lifetime AS referencelifetime FROM mdl_files f LEFT JOIN mdl_files_reference r ON f.referencefileid = r.id WHERE r.repositoryid = '6' ORDER BY sortorder, itemid, filepath, filename;
      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      1 SIMPLE r ref PRIMARY,mdl_filerefe_repref_uix,mdl_filerefe_rep_ix mdl_filerefe_repref_uix 8 const 19 Using where; Using temporary; Using filesort
      1 SIMPLE f ref mdl_file_ref_ix,mdl_file_refsoritefilfil_ix mdl_file_refsoritefilfil_ix 9 moodle.r.id 16883 Using where

      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      2 rows in set (0.01 sec)

      And the neverending sql query now performs like this:

      11 rows in set (0.01 sec)

      Even though we have Moodle 2.4, it seems to me that this database index does not exist in any next Moodle version. Anyhow, hoping this helps. This, by now, seems having solved our performance issues.

      Jordi

      [1] https://moodle.org/mod/forum/discuss.php?d=310372&parent=1242018

        Attachments

          Activity

            People

            Assignee:
            jpahullo Jordi Pujol-Ahulló
            Reporter:
            jpahullo Jordi Pujol-Ahulló
            Peer reviewer:
            Marina Glancy
            Integrator:
            Dan Poltawski
            Tester:
            Dave Cooper
            Participants:
            Component watchers:
            Matteo Scaramuccia, Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Fix Release Date:
              11/May/15