Moodle in English: Correct use of the question

Correct use of the question_usages DB table

by George Schaathun - Monday, November 4, 2019, 5:38 PM

Not quite sure if this is the right place to ask a rather technical development question!?

We are developing a question plugin (CAPQuiz, already published), which worked fine with 120 odd students and 40 odd calculated questions. However, in the next assignment, using mainly STACK questions, performance degraded, and every page request to the CAPQuiz instance took annoyingly long. At busy times, the students often got blank screens or error messages.

Using a profiler and logging all queries in the DB engine, it seems that the problem is a ridiculous number of STACK related DB queries, even when no such question is needed by the page, all queries originating from the core API call to retrieve a question usage object. I believe we implemented CAPQuiz to use one question usage for all questions in the quiz, which would explain the behaviour I think. Only one question is actually needed per page presented to the student.

Could someone explain the rationale and intended usage of the question_usages table? I did not find this in the doc's. Would it be more correct/tidy to use on question_usages row per question in the quiz? If my hypothesis is correct, we should just rewrite CAPQuiz to use on question_usages row per question, but it would be useful with some confirmation before we start such a task. I'd appreciate any input to shed light on this, both conceptual and technical.

Thanks a lot in advance.

Re: Correct use of the question_usages DB table

by Tim Hunt - Monday, November 4, 2019, 9:27 PM

One usage per attempt is correct.

One usage per question is definitily wrong.

If there is a performace problem, that needs to be fixed directly.

What are the database queries that STACK is doing? If they are all queries of qtype_stack_cas_cache then acutally, that is expected and much better than any alternative. However, I would be surprised if that was causing significant performance issues. If it is something else, then that is probably a bug in STACK.

Another test you could do would be to make a normal Moodle quiz containing the same questions, and test the performance of that. If the performance is the same, you will confirm it is a problem with Moodle core, or qtype_stack. If performance is different, then it must be in your code.

It is the case that when you load a question_usage, it fully initialises all the questions in the usage, whether you are going to need them or not. When I first implemented the current Moodle question engine, I did worry about whether that would be good enough. However, at the time I decided to invoke "premature optimization is the root of all evil" and not worry about it. Since the question engine landed in Moodle 2.1 (Jan 2013), my decision to keep things simple at first has not been too bad.

However, at the time, I was wondering whether, to get accesptable performance, it would be necessary to add a $requiredfullyloadedslots argument to methods like load_questions_usage_by_activity. It could be done, but it would be quite a lot of work to do it properly.

Average of ratings: -

Re: Correct use of the question_usages DB table

by George Schaathun - Monday, November 4, 2019, 10:35 PM

Is that one usage per attempt at the activity? (As opposed to question attempt?)

What I can observe is a long list of
2019-11-04 10:18:33.073 MET [22589] moodleuser@moodle LOG: duration: 0.181 ms parse : SELECT * FROM mdl_qtype_stack_cas_cache WHERE hash = $1 ORDER BY id
in the server log, when the student is waiting to have a question graded or for the next question. These messages come as a dense stream with only a single user waiting for a page. The quiz has about 40-50 STACK questions and 18 multiple choice questions. I find it very hard to think that such a number of DB calls would not be a performance issue. Especially since I see no evidence of CPU intensive processes slowing down the response, and there are few other DB calls.

The function which appears to be generating a large number of function calls and a very long wall time is a single call to \question_engine::load_questions_usage_by_activity().

I'll look for other explanations too, but I still believe the question usages must be the problem.

Average of ratings: -

Re: Correct use of the question_usages DB table

by Tim Hunt - Tuesday, November 5, 2019, 12:58 AM

"Is that one usage per attempt at the activity? (As opposed to question attempt?)"

Yes.

\question_engine::load_questions_usage_by_activity() calls the apply_attempt_state method for every question in the usage.

For each STACK question, this should result in a single call to Maxima, to initialise all the question variables from the Random seed. Except, because calls to Maxima are slow, there is a cache in the database. So, I would expect this to be one call to $DB->get_records('qtype_stack_cas_cache', array('hash' => $cached->key), 'id'); for each question (so in your example, 40-50 DB reads). There is an index on the hash column, so that should be fast.

And, indeed, your log shows 0.181 ms which is fast. How is your Moodle talking to your database? What is the round-trip time there?

40 questions in one quiz seems like a lot. Particuarly since one of the claims of adaptive testing is that students need to answer fewer questions to get a grade with the same reliability as a standard test.

Average of ratings: -

Re: Correct use of the question_usages DB table

by George Schaathun - Tuesday, November 5, 2019, 1:54 AM

Well, postgresql logs 51801 such queries within 32s. Admittedly there may have been a couple of users active during that time, but there is no way 40-50 queries per page request could create that activity. I am not worried about the time per query at all...
The DB connection is the network interface on localhost. Postgres. I am not sure how I can measure the round trip.

Given the design of CAPQuiz, 40 questions is very few.
1. It is designed for practice, not testing. CAP = Computer Adaptive Practice. I don't care about their grade. I want to keep the students answering questions for some time.
2. Even in CAT (computer adaptive testing), you reduce the number of questions answered by each student, not the number of questions. The reliability stems from selecting questions giving maximum information about the student at hand, in statistics terms, measuring in Fisher information.
Now, there is a lot to be said about the design of CAP, and alternative models for adaptive systems too, but let's leave that for another time. Given the current design, I would be looking for hundreds of questions in a quiz.

Average of ratings: -

Re: Correct use of the question_usages DB table

by Tim Hunt - Tuesday, November 5, 2019, 6:49 AM

You need to analyse this properly, which requires more than just looking at the DB logs during a random time period.

Turn on performance info, so you get an exact count of DB queries used to generate each page: https://docs.moodle.org/37/en/Debugging#Performance_info (and the time waiting for the DB).

On a test server, profile your plugin, so you can see where time is actually being used: https://docs.moodle.org/dev/Profiling_PHP.

(You may prefer to do both of those on a test server, not your live site.)

Sorry I misjudged what your plugin was doing. Using questions for practice in interesting ways is good. I hope you can get your plugin working well.

Average of ratings: -

Re: Correct use of the question_usages DB table

by George Schaathun - Tuesday, November 5, 2019, 4:32 PM

OK. Thanks. I had missed the config to get DB call counts from moodle.

Now I am really stuck. I tried to copy the offending activity into a test server by backup/restore, and all I get when accessing the actitivity as a student is
Fatal error: Maximum execution time of 30 seconds exceeded in /var/www/moodle/question/type/stack/stack/cas/connector.unix.class.php on line 60
Thus I do not get the logs at the bottom of the page. The postgresql log shows more than 97000 queries to mdl_qtype_stack_cas_cache which should include a few quick page requests as teacher/admin and a few pages failing at that 30s limit. Presumably it fails because it has to reconstruct all the caches which have been built up slowly over time on the production server.

The profiler is clear: 100% of the wall time is from a single call to question_engine::load_questions_usage_by_activity()

This is not as bad in an quiz designed purely for testing, with few users and few prior question attempts in the DB, even with the same questions. It is noticeably slow, but working. The same call to load_questions_usage_by_activity dominates the profile also when the request completes, although not quite at 100%.

Sure, it is still possible to do a better analysis, but designing the data set which is small enough to pass under the 30s limit, and still big enough to illustrate the problem is going to take a while. I'll keep working though.

It seems clear to me that the core question engine is not designed to scale (to an unlimited number of questions per quiz), but that need not be the problem I am hitting at.

Average of ratings: -

Re: Correct use of the question_usages DB table

by Tim Hunt - Tuesday, November 5, 2019, 8:08 PM

question_usage should scale reasonably. From comments in this forum (e.g. complaints that the quiz navigation does not fit on screen), I know that quizzes can get as big as ~200 questions, and I think the system should support that (although I also think that is probably cruelty to students). I aslo think that if you just used the standard question, types, that would work fine.

However, I have also analysed data from a lot of real quizzes, and here are most common quiz size is 6 questions, and few quizzes tend to have more than 20. (We tend to favour more, smaller, quizzes, and are questions are often quite deep, so each question take considerable thought.) So, that is probably why we have not hit performance limits like this ourselves.

Then again, some of these STACK quizzes get >1000 attempts, and we can run the quiz statistics report just fine.

Reviewing an attempt at a STACK quiz containing 10 questions gives:
DB reads/writes: 192/0
DB queries time: 0.17825 secs

And, reviewing a quiz with 20 non-stack questions is
DB reads/writes: 63/0
DB queries time: 0.08606 secs

Viewing a Moodle Page resource in the same course is
DB reads/writes: 45/1
DB queries time: 0.06423 secs

So, yes. STACK is slow. But then it is powerful.

I could write more about the performance trade-offs here, but I don't have time now.

If you want a quick and dirty hack to get round your performance problems, then my suggestion would be to use two usages: Have use usage, containing a singe question, for the question the user is currently answering. Then when that question is complete, move it to a second usage, whcih stores all the completed questions. Hopefully you don't need to load that second usage very often.

There is no proper API for moving a question from one usage to another (but if you want to submit a pull request to Moodle core, I think this woudl be a valid operation to add in the right place in the API). However, I think you just need to set question_attempt->slot, and question_attempt->usageid. (The method add_question_in_place_of_other is similar but different, and that works.)

Average of ratings: -

Re: Correct use of the question_usages DB table

by George Schaathun - Tuesday, November 5, 2019, 9:16 PM

Thanks a lot Tim.
I appreciate that I have found a use case which was not anticipated. And I agree, it seems to come up only with a certain combination of CAPQuiz, STACK, and a certain amount of historic attempts in the DB.

I am not sure I understand your solution. You say a temporary usage linked to a single question, which is then moved to the real permanent usage. Should that not be a question attempt? Rather than a question? If so, I get it. Sorry if the question is silly; I did not write CAPQuiz myself, I am the field tester, with very flimsy understanding about the DB model.

Average of ratings: -

Re: Correct use of the question_usages DB table

by Tim Hunt - Wednesday, November 6, 2019, 1:29 AM

Sorry, I finished my last message in a rush, because I had to go to a meeting. Yes. I meant question_attempt, not question. Well done for de-coding what I meant to write.

Average of ratings: -

Re: Correct use of the question_usages DB table

by George Schaathun - Friday, November 8, 2019, 6:28 AM

Thank you.

Analysing the trouble further, I have managed to reproduce the bad performance in the Vanilla Quiz (rather than CAPQuiz). Given that no prior attempts existed for the Vanilla Quiz, I suppose the problem does not relate to the question_attempt but to something else. Could there be a problem with scalability in the number of deployed question variants? In this test I had only about 20 STACK questions, but OTOH each one was deployed in 100-120 variants. I realise that such numbers of deployed variants is far beyond reasonable in the default use cases. It may even be overshooting in mine, but nevertheless, this particular use case will demand both more questions and more variants than typical applications of the Vanilla Quiz activity.

If I interpret the observations correctly, load_question_usage_by_activity() will query the cas_cache table at least once per deployed variant, regardless of whether that variant or even that question has yet been referenced by a question_attempt. Are you able, Tim, to confirm or reject that hypothesis off the top of your head?

If the hypothesis is correct, I get doubts about the solution you suggested, as it would seem that all question variants in the activity instance would be loaded as soon as that instance is referenced in the first question_attempt. But I am playing with conjectures still.

Do I understand it correctly that question variants is a question bank concept, and not only a STACK concept? That is, so that any optimisation of the variant handling has to involve the moodle core?

Average of ratings: -

Re: Correct use of the question_usages DB table

by Tim Hunt - Friday, November 8, 2019, 4:41 PM

"load_question_usage_by_activity() will query the cas_cache table at least once per deployed variant"

No. cas_cache belongs to qtype_stack. Only qtype_stack will query the cache. load_question_usage_by_activity will not directly touch it.

And, qtype_stack will only query the cache for things related to the current variant.

Variants are a core question bank concept. The handling of variants is efficient.

I am pretty sure the answer is that STACK is currently a bit inefficient, because the area of most interest to STACK is rich questions about relatively complex mathematics. Therefore most stack quizzes probably have relatively few quetions that each take a lot of thought. Therefore no one has hit this performance limit before, and so more work on performance has not been a priority. (Although I know Chris Sangwin, creator of STACK, has done some work on performance.) Now that this problem has been identified, then like most performance problems, it should be possible to do some profiling, find the inefficiency, and make things faster. Just a matter of someone having time to do that.

Average of ratings: -

Re: Correct use of the question_usages DB table

by Tim Hunt - Friday, November 8, 2019, 4:51 PM

Actually, there may be a quick and dirty way to improve things just by changing the core question engine.

At the moment, we fully initialise each question when the question_attempt is loaded (https://github.com/moodle/moodle/blob/master/question/engine/questionattempt.php#L1557).

We could change that to a lazy-load. So that the question is only initialised using $question->apply_attempt_state the first time that a method that requires the question to be fully initialised is called.

It would just be a matter of analysing all the methods of question_attempt to work out which need an initialised question. And then hoping that our automated tests are good enough to catch any mistakes made.

Moodle 3.8 is in the final stages of release, so it is not the time to make a risky change like this. The soonest it could be done is for Moodle 3.9.

I am not sure if this would help the standard quiz module. I wonder if rendering the Quiz navigation UI needs enough details about the question that it has to be initialised? But, for other activity modules, this could be a useful change.

(Edit: filed as MDL-67183.)

Average of ratings: -

Re: Correct use of the question_usages DB table

by George Schaathun - Wednesday, May 13, 2020, 6:23 PM

I see patch has been made on MDL-67183. Is it realistic to hope for it to be merged into the stable 3.8 branch before next semester starts in August? And in the new 3.9 release when it comes?

I am seriously considering to use MDL-67183 branch head for my class starting in August, just to get around this problem, but if there are alternatives ...

Average of ratings: -

Re: Correct use of the question_usages DB table

by Tim Hunt - Wednesday, May 13, 2020, 6:51 PM

The code was submitted before the deadline. I have been reassured that everything submitted will get reviewed and integrated before 3.9 is released.

You can track integration progress on the dashboard here: https://tracker.moodle.org/secure/Dashboard.jspa?selectPageId=11350. On deadline day, the integration pile (mountain) was at 100, and it is now down to 70 - and this is with them tackling the biggest, most compliated, and difficult to integrate things first.

Average of ratings: -

Re: Correct use of the question_usages DB table

by Marcus Green - Wednesday, May 13, 2020, 8:26 PM

Nice to see Capquiz getting some love
https://moodle.org/plugins/mod_capquiz
Adaptive quizzing is a very interesting concept.

Average of ratings: -

Re: Correct use of the question_usages DB table

by George Schaathun - Tuesday, November 5, 2019, 2:18 AM

Just a stray thought. You expect one call per STACK question? Or per deployed instance?

Average of ratings: -

Re: Correct use of the question_usages DB table

by Tim Hunt - Friday, November 8, 2019, 4:44 PM

Well I hoped that calling apply_attempt_state on one STACK question required only a single call to Maxima, or possibly a small finite number, if that led to better use of the cache. I thought the code was written to achieve that, but something may have slipped. STACK is a complex beast (for good reasons).

Average of ratings: -