Home > OS >  Slow performance on course main page due too much DB queries (seeing on performance profiler)
Slow performance on course main page due too much DB queries (seeing on performance profiler)

Time:02-16

I have a problem with a Moodle 3.10.5 and two courses, let me explain.

I have two courses that are created on the development moodle, and we import them on the production server one week ago. This night, 200 students was their enrolment activated, and the production courses have this issue. Main course page do thousands of db queries and it's too slow, but only if I login with a manager/admin profile. Students have not this problem.

On the development server the db queries are normal (DB reads/writes: 252/1), but on the production server, that the only change is 200 active students (yesterday that students are enrolled but inactive, the enrolment was active since 0:00h of today) have several more queries (DB reads/writes: 11012/1).

I can't found the reason, the students run fine with normal db queries, but admins and managers are too slow but only on the main course page (course/view.php?id=XX)!

This is annoying because on the same production platform there are more courses that runs fine. The problem is not the server performance, is the amount of dbqueries that moodle do on this two courses, only on the course main page and with teacher and manager logged profiles. Note: these manager&teacher profiles comes inside the course .mbz package.

How can I see where that db queries come? I add the "logall" queries on the config.php, but 11k of queries registered on the database it's hard to debug and make distinction for what is good or not.

Development server: development server

Production server: production server

Edit

I activate the profiling as davosmith suggest on its response, I think is a problem of Adaptable Theme, counting the active users on the course, but still weird that is doing this things when we are using this system on several courses and servers without issues. I don't know how to resolve this.

profiling

CodePudding user response:

I would recommend you set up some PHP profiling on your system, using the information (and links) listed here: https://docs.moodle.org/dev/Profiling_PHP - I have only used Xhprof, so I cannot really comment on how well Xdebug works.

The information returned by this profiling will enable you to see which functions are taking up the most time when viewing the course page and should help you to narrow down what the likely problem is.

CodePudding user response:

I solve the problem. It's caused by the theme "Adaptable" (https://moodle.org/plugins/theme_adaptable), that shows on the main course page the number of tries of students on quiz modules. I have a course with several quiz and hundreds of active students, so the performance was critical.

I configure the profiling on moodle as @davosmith suggest on his response. There are two solutions:

1. Disable the adaptable theme feature

Disable the theme feature to see the quiz attempts on the main page. Go to Admin > Appearance > Themes > Adaptable > Configuration, go to Course sections (admin/settings.php?section=themesettingadaptable#theme_adaptable_course) and disable the following features:

  • coursesectionactivityfurtherinformationdata
  • coursesectionactivityfurtherinformationlesson
  • coursesectionactivityfurtherinformationfeedback
  • coursesectionactivityfurtherinformationchoice
  • coursesectionactivityfurtherinformationquiz
  • coursesectionactivityfurtherinformationassign

or 2. Try to apply a patch on the code

Try to apply a patch that a user commits to the theme. This patch is for activity.php, I think is the file that process this feature. You can see the conversation and the patch here:

  • Related