We started having severe performance problemance at a certain point. It is hard to be sure, but looking at the queries that get piled up, it seems that queries which access mdl_log are the culprit. We only have about 6 millions lines in mdl_log, surely not exceptional?
Innotop says that apparently it is these kinds of queries that sometimes take over a minute to execute:
SELECT instance,cmid,l.course,COUNT(l.id) as count FROM mdl_log l JOIN mdl_course_modules cm ON cm.id = cmid JOIN mdl_questionnaire q ON cm.instance = q.id WHERE ((l.course = 33 AND l.time > 1288529878) OR (l.course = 24 AND l.time > 1290682109) OR (l.course = 25 AND l.time > 1290682513) OR (l.course = 38 AND l.time > 1288304419) ) AND l.module = 'questionnaire' AND action = 'submit' AND userid != 775 AND q.resp_view <> 1 GROUP BY cmid,l.course,instance
I think these queries are run when a user acesses moodle/my .
Are there any sugestions to deal with these queries, or with large mdl_log in general? How large can mdl_log be before people start pruning it? Still, it should be possible to run for 6 weeks without pruning the logs?