We were not experiencing the deadlock issue in MDL-78580, but the question bank and quiz statistics pages were taking upwards of 40 seconds to load, if they did not timeout. We were also seeing the timeout on the quiz_statistics cron task, so it never finished. We used the cron script for debugging and found a delete query that was taking 2.5-4 seconds per question during the task in question/classes/statistics/responses/analysis_for_question.php:211:
$DB->delete_records_select('question_response_count',
'analysisid IN (
SELECT id
FROM {question_response_analysis}
WHERE hashcode= ? AND whichtries = ? AND questionid = ?
)', [$qubaids->get_hash_code(), $whichtries, $questionid]);
We replaced this query with:
$DB->execute("
DELETE qrc
FROM mdl_question_response_count qrc
JOIN mdl_question_response_analysis qra
ON qrc.analysisid = qra.id
WHERE qra.hashcode = ?
AND qra.whichtries = ?
AND qra.questionid = ?",
[$qubaids->get_hash_code(), $whichtries, $questionid]);
and now the query runs in 0.00038 seconds, we were able to get the cron task to complete, and the pages load significantly faster now.
We also added indexes to a few of the impacted tables:
create index mdl_question_response_analysis_test_idx on mdl_question_response_analysis (hashcode, whichtries, questionid);
create index mdl_question_response_count_test_idx on mdl_question_response_count (analysisid);
I realize we need to adapt the query for other supported DB engines, and would ask for the community's help in developing that, as we run a MySQL instance. Please advise of preferred next steps. Thank you!
- has a non-specific relationship to
-
MDL-29589 New $DB method for bulk deleting/updating records using a subselect
-
- Closed
-