This issue exists on Moodle 2.3.2 at least. The affecting code that produces part of the horrible performance is still present in 2.3.4. Even though we have not specifically tested on 2.3.4.
What we are seeing in our environment is;
The temp table query;
SELECT f.id AS btfid, f.contextid, ... FROM mdl_backup_files_temp f JOIN mdl_backup_ids_temp i ON i.backupid = f.backupid AND i.itemid = f.itemid WHERE ...
This code is in; backup/util/dbops/restore_dbops.class.php:866 ff
Once you get a large number of rows in those tables it causes problems for us. The first appears on PostgreSQL as it doesn't analyze that data as it's a temp table. This results in a guess the tables are small and does a nested loop join. This results in 2seconds per query. Hundreds of these queries run to restore a course with large numbers of quizzes.
The code for that can be found in;
To reduce this time, we've tested hacking an analyze table into;
This dropped the query from 2 seconds to 35ms in examples where the number of rows in those tables were large.
When you scale that up we have the following example running on our environment right now. A 1.5Mb backup of a course, it's currently been restoring for 5.5 hours and still not finished. On a different less powerful environment I put in the PostgreSQL only hack above and the restoration time was 30minutes. So that's at least a 7x increase without taking into consideration the following.
The comments at the top of the restore_create_question_files function in restore_stepslib.php says there is a TODO to improve this code. It indicates there will be a huge amount of queries saved if we do this per context rather than per question. This clearly appears to be a significant location for improvement. When we are restoring subjects like this we see low CPU usage on both the database server process and the web server process. That indicates lots of queries and waiting on millisecond level network latency.
We latency and high numbers of queries issues when upgraded from 2.0 to 2.2. We reduced the upgrade time from 12 hours to 5 hours by adjusting the quiz upgrade code to insert data in large chunks where possible, instead of thousands of small queries. I theorize this is also what's happening here.
We are discussing options with our Moodle Partner at the moment to look at resolutions to our issue. Is anybody already looking at this as I'm sure there are lots who would like to see a benefit from this. I would also allow us and our Partner to get on the right track if somebody has already proposed a way to work towards a solution.
Has anybody else got any theories or understand of other things that could be causing the slowness?