-
Bug
-
Resolution: Fixed
-
Minor
-
2.6.6
-
MOODLE_26_STABLE
-
MOODLE_27_STABLE, MOODLE_28_STABLE
-
MDL-49641-get_external_files_master -
Easy
-
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