Long loading time for question bank

Long loading time for question bank

by Robert Schrenk -
Number of replies: 17
Picture of Core developers Picture of Plugin developers

Hello,

since a few days we suffer from a very long page load (30 - 40 secs) when using the question bank. Starting and performing a quiz works normally, its just every function that shows questions or categories from the question bank when editing a quiz or managing the question bank.

There is nothing logged as error, even if the debug-level is set to "developer". We upgraded to Moodle 3.5.2 (build 20180920) and it may have to do with this update, but it is not for sure if the problem has been there before.

I made screenshots of a normal page load (dashboard), the question categories and the list of questions of a course.

A short comparison:


Value Dashboard Categories Questions
 Load time  0.1 secs  38.1 27.5 secs
RAM 9.9 MB  16.8 MB11.1 MB
DB reads/writes 82/0  765/279/0
Context filters  1  3 1
Included files  878  390 446

I have no idea where I could start debugging. None of the parameters seems to indicate the long loading time. Is there anyone who can help me in this matter?

Kind regards

Robert

Attachment dashboard.png
Attachment question_categories.png
Average of ratings: -
In reply to Robert Schrenk

Re: Long loading time for question bank

by Robert Schrenk -
Picture of Core developers Picture of Plugin developers

As there are only two attachments allowed per post, here is the third screenshot

Attachment question_list.png
Average of ratings: -
In reply to Robert Schrenk

Re: Long loading time for question bank

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

So, for the two question bank pages, almost all the time is in 'DB queries time', and when you look at the number of reads, particularly the 765, that is bad.

On one of our test servers running 3.5.2, question/edit.php showing a list of several hundred questions, I get:

  • 0.813476 secs
  • RAM: 15.1MB
  • RAM peak: 15.3MB
  • Included 575 files
  • get_string calls: 2795
  • DB reads/writes: 77/0
  • DB queries time: 0.12867 secs
  • ticks: 82 user: 44sys: 8 cuser: 0 csys: 0
  • Load average: 0.00
That looks fine for such a complex page.

For the list of categories, I get:

  • 0.301291 secs
  • RAM: 11.9MB
  • RAM peak: 13.4MB
  • Included 480 files
  • get_string calls: 1167
  • DB reads/writes: 181/0
  • DB queries time: 0.12454 secs
  • ticks: 30 user: 13sys: 2 cuser: 0 csys: 0
  • Load average: 0.01
That number of DB queries is not great, but also, it need not be slow.

I wonder if everrything is OK in your database. Perhaps use mysqlcheck on some of the tables like mdl_question and mdl_question_categories and make sure they are OK. Also, go to Admin -> Development -> XMLDB Editor and do [Check indexes] to make sure none are missing.

Average of ratings: -
In reply to Robert Schrenk

Re: Long loading time for question bank

by Jerry Lau -

We've been having really bad response times when classes take quizzes even after we upgraded our platform to separate the app and have it's own db server. The computing resources are both fine and go above the minimum but yet when about 30 people take a quiz at same time, it takes then 5 minutes to get to the next question and times them out that they have to login again!

check indexes are fine, though we get foreign key violations from time to time - this one I don't know why it happens and how to fix them ...

BTW, how did you do you dashboard?

Average of ratings: -
In reply to Jerry Lau

Re: Long loading time for question bank

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Something is not right on your server. Moodle can do much better than this. I have run load tests with fairly modest hardware (2 web servers, one DB server, none of them particularly crazy) which could handle a simulated 1000 simultaneous users when we were load-testing. The real quiz had ~200 users and ran with no problems at all. And, I know other people who have run even bigger quizzes than that.

So, it can be done. There must be something about your set-up that is not well tuned. I hope you can track down what it is, and get it sorted out. A Moodle partner might be able to help.

Average of ratings: -
In reply to Robert Schrenk

Re: Long loading time for question bank

by Robert Schrenk -
Picture of Core developers Picture of Plugin developers

Hi,

thanks for your replies.

The thing is, that doing the quizzes from the learning perspective works fast and reliable. 

The problem only occurs when a teacher wants to open the question bank or add a question from the question bank to a quiz.

  • I checked the databases and did a repair on them - no effect
  • I checked for inconsistent entries and fixed 6 questions that were related to non existing categories - no effect
  • I checked for inconsistent categories and fixed 2 categories that had a parent in another context - no effect
  • In XMLDB-Editor I checked for indices - everything fine - no effect
  • I tuned the mysql config to give more RAM to it and enabled query cache - no effect

puh.... any further suggestions?

Kind regards



Average of ratings: -
In reply to Robert Schrenk

Re: Long loading time for question bank

by Robert Schrenk -
Picture of Core developers Picture of Plugin developers

Oh .. there is one possible inconsistency.

We have about 1.5mio questions that have a "parent" that seems not to exist. I have not found any documentation what this "parent" of a question is (the source of a derivative?)

Do you think this can have an effect on the performance?

Regards

Average of ratings: -
In reply to Robert Schrenk

Re: Long loading time for question bank

by Marcus Green -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers

What is 1.5mio?

Average of ratings: -
In reply to Robert Schrenk

Re: Long loading time for question bank

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

For most questions, parent will be 0 which is a non-existant question id. So depending on how you did your check, you may just have found 1.5 million rows where parent is 0, which is not a problem.

The other cases where parent is set is:

  1. qtype_random. Here, parent is set to be the same as the id.
  2. qtype_multianswer (and similar third-party question types like qtype_combined). Here, for the subquestions that make up the main question, parent should point to the main question.
Can what you are seeing be attributed to one of those two things that has gone wrong?
Average of ratings: -
In reply to Tim Hunt

Re: Long loading time for question bank

by Robert Schrenk -
Picture of Core developers Picture of Plugin developers

Hello,

It seems that a vast amount of random questions has been duplicated or so. We would have a few thousand source-questions, that may scale to a few ten thousand questions by use of Stack-Derivatives. But after testing we saw that Stack does not duplicate questions in the question table itself.

Out of this amount of questions more than 6 millions have been of qtype random! We do not believe that on our system with a few thousand people 6 million random questions are used wink

We suppose that due to a moodle update there has been an error that caused a duplication of random questions or so.

Over night I am running a cron job that removes all questions through the moodle function "question_delete_question" - so it only deletes questions that aren't used. Tomorrow I will see how many of the questions will still be there....

Thanks for all your tips in this matter!

Regards

Average of ratings: -
In reply to Robert Schrenk

Re: Long loading time for question bank

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Ah! You may be suffering from MDL-63260, and it may be that is more serious than first thought.

Average of ratings: -
In reply to Tim Hunt

Re: Long loading time for question bank

by Robert Schrenk -
Picture of Core developers Picture of Plugin developers

Yes, it seems that this is the same issue.

Do I understand correctly, that Moodle duplicates ALL random questions - not only those that are used by the cloned quiz?

Deleting the unused questions is a workaround, but no solution.

Average of ratings: -
In reply to Robert Schrenk

Re: Long loading time for question bank

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

There is history here:

Previously, Moodle did not duplicate questions that were used in the quiz. This was really bad, because if the settings were altered in once quiz, they would change in the other quiz too, with no warning at all.

So, we fixed it to ensure that random questions were duplicated.

However, an unintended consequence of that is that it was duplicating all random questions in that category. I had been thinking 'oh! a few extra random questions won't do any harm'. I had not appreciated the extent to which exponential growth rapidly gets you beyond 'a few'.

Average of ratings: -
In reply to Tim Hunt

Re: Long loading time for question bank

by Robert Schrenk -
Picture of Core developers Picture of Plugin developers

Thanks for sharing the history to us wink

I think that there must be something really wrong that "a few" question can become "a few millions". Is it possible that ALL random questions on the whole system get duplicated, not only the ones in the chosen category?

Regards

Average of ratings: -