We are having major problems with quizzes where instructors are randomizing questions. We have almost made up our minds to move to InnoDB but if that doesn't solve the problem, we'll need to start digging through the code to determine were we can optimize. That said, can someone give me a brief overview of how the question randomization process works on the database side? I just need to know what queries or stored procs it employs.
Most of the data associated with randomization is stored in the mdl_question_states and mdl_quiz_attempts tables. No stored procs are involved. For details, one would need to know which question types you are using: Multiple choice? Calculated? Randomized short-answer?
However, it would be very interesting to know which SQL statements are causing performance problems for you. This should be visible on the DB side. After that, one can find out which part of the code is at the heart of the problem.
That said, I doubt that randomization as such is causing performance problems to the database. It's more that for quizzes, users need to make a large number of page requests in a short time period (independent of randomization).
Thanks for the advice.
It may not be the actual randomization process that's causing the problem but something associated with it. For instance, in MyISAM the entire table is locked...is the randomizer throwing errors because it can't access the table to randomize the block of questions? Is there more writing going on during the randomization process than during quizzes without randomization?
We've been running Moodle for over a year and this is the first time we've had the problem. It's only happening for quizzes with randomization. Additionally, if we return 5 questions per page, the errors aren't as prevalent. Returning 1 question per page causes massive failures.
We are also seeing minimal performance hits on our front-end servers and our database server is just fine during most of the process.
The table locking is an inherent problem of MyISAM, independent of the quizzes. Any write access will lock the entire table. Switching to the InnoDB engine may reduce these problems, since InnoDB works with row-level locking.
That problems are less frequent with 5 questions per page seems very plausible - with 1 question per page, students need to access 5 times as many pages, hence the increased load. Also, the more often the student clicks "Submit", the more write operations occur on the database.
For all I'm aware, the randomization is done in the moment where the student clicks "Attempt quiz now", not later in the process. So most of the write operations are unrelated to randomization.
There are various ways in which randomisation adds to server load.
First, when the attempt is started, Moodle has to pick actual questions for each random question. That is done by code in question/type/random/questiontype.php in the create_session_and_responses method. Note that it hurts performance if you have the 'Display questions from sub-categories too' option turned on - and that is on be default.
Second, on every page of the quiz, there is more slow-down becuase the question system has to load two question definitions. The definition of the random question, and then the definition of the question that was actually picked. It would be hard to optimise that. (Development:Question_Engine_2, due for inclusion in Moodle 2.1, fixes that. I don't think there is any way to make the same saving in the current 1.9 or 2.0 code.)
Third, the save_session_and_responses function in the random quetsion type has to do two database queries per question processed because of the way this work. That is bloody horrible (and also fixed in Question Engine 2) but again I can't think of an easy way to fix that in the 1.9 code.
I am suprised that 1 question per page makes things worse. If that is what you are seeing, it is an interesting observation.
Thanks Tim and Henning. This is great info and gives us an indication that switching to InnoDB might alleviate our problems. Additionally, the error messages our students receive relate to not being able to get the questionID. We've done some digging in the code and it seems that table locking could be the culprit here. We're also about to run a load test against an InnoDB converted database and compare it to our baseline from the MyISAM version.
We tried unchecking 'Display questions from sub-categories too' but when we go back into the quiz it's checked again. Is there someplace, other than the quiz itself, that we should uncheck this?
Yes. Click the edit icon next to the random question. (Or, uncheck the quiz option before adding the random question to the quiz. I can't help thinking this UI needs to be improved. Oh, we did that in Moodle 2.0. I remember now.)
We ran our test against our converted InnoDB database and the table is still locking and causing errors. We are working with a consultant who is recommending we make changes to the code. Essentially, he’s saying the entire table is still locking (even in InnoDB) because the session update query is using a binary field (sesskey) in the WHERE clause.
Does this sound plausible and what are the implications of changing the code so it doesn’t cast this field as binary?
Hm. Are you storing your session data in the database?
In this case, see here:
Thanks Henning!! That is exactly what we just tried in out test environment (InnoDB) and it significantly improved performance! We aren't getting any "questionid" errors even when ramping up to 250 users.
Just don't know if there are any other implications to making this change.
My recommendation would be that you store your session data on the file system, not in the database. In this case, there are no side effect that I know of. (I've switched my own installation to "file system sessions" as well.)
Storing sessions in the DB would really be worthwhile only if you run a configuration with several web servers (load balanced) and you need failover between them. Otherwise I'd rather avoid it, because it's described as being potentially unstable. That said, I ran the ADODB patch described in that discussion thread for a while, without noticing problems (but it was not on a very busy site).
Actually, the loadbalanced front-ends that you describe is our exact configuration. We have 5 front-ends loadbalanced by an F5 with one database server.
In that case, can you get your load-balancer to use sticky sessions (so all the requests from one user go to the same web server). Then you can use sessions on the server's hard disc.