Storing too much in question attempt?

Storing too much in question attempt?

by Itamar Tzadok -
Number of replies: 10

Each question attempt (at least in MC questions) stores in the database (mdl_question_attempts) full text of the question summary, right answer and response summary.

This seems fairly expensive as question/answer text may be long, and these bits of question attempt info could be constructed on demand from the question definition and attempt data (which seems to be unnecessarily repeated over and over again in mdl_question_attempt_step_data for each attempt of the same question instance).

Is there a good reason I'm missing for this design? smile

Average of ratings: -
In reply to Itamar Tzadok

Re: Storing too much in question attempt?

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

Well, it could be reconstructed, but actually to do so would be really expensive, what with all the shuffling and everything, which would make something like the quiz responses report a terrible performance hog. Also, it shows you what the question was at the time the student saw it, even if it was later edited.

Finally, this amout of text is a trivial amout of hard disc space compared to just uploading one video, or the mdl_log table, so I don't think it is a big deal.

Average of ratings: -
In reply to Tim Hunt

Re: Storing too much in question attempt?

by Itamar Tzadok -

Here are some (rounded) numbers representing about 70 users taking quizzes. We can assume that there are no other activities but the log does contain also records of users/guest access to some resources.

 

Table Rows Size
log 56,000 21 M
question_attempts 15,000 42 M
question_attempt_steps 36,000 11 M
question_attempt_steps_data 116,000 14 M

 

Surely this is not trivial.

it shows you what the question was at the time the student saw it, even if it was later edited.

I've thought about that and agree that this is sometimes desirable, but not always, and considering the above numbers I can live without it. I suppose this could be redesigned as a setting which would allow turning this history on/off and when on it would be recorded in a separate table.

which would make something like the quiz responses report a terrible performance hog

By 'quiz responses report' do you refer to something generated frequently, say, per submission?

Average of ratings: -
In reply to Itamar Tzadok

Re: Storing too much in question attempt?

by Pierre Pichet -

However we are living in a tera size world when you need to handle 10,000 students emails.

Just look at their "free" storage space on google mail.

One main concern we have at UQAM for the Moodle 2 migration is how to handle the "personal files" on Moodle.

 

Pierre  

P.S. and I am coming from a world with 4k main computer memory size wink  but that was a long time ago.

Average of ratings: -
In reply to Itamar Tzadok

Re: Storing too much in question attempt?

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

By 'quiz responses report' I mean mod/quiz/report/responses.

If that is data from 70 students, then you have assiduous studens, becuase they have answered more than 200 questions each on average.

Anwyay we seem to be averaging about 5kb per question answered (not counting logs). 

What I find particularly surprising is that question_attempt_steps_data comes out at 128 bytes per row. Why is that so high? Which database are you using?

But, overall, while these amouts of data may seem a bit inefficient, they are not really very big comparted to modern hard drives. I'm not worried yet.

By the way, here are the row counts from our live system.

question usagesquestion attemptsquestion attempt stepsquestion attempt step data
1,091,035 10,261,211 27,729,555 46,827,052
Average of ratings: -
In reply to Tim Hunt

Re: Storing too much in question attempt?

by Itamar Tzadok -

The point was not the 70 students or the actual sizes of the tables but rather the relative size of question attempt stored data (I'm using mysql for this one) . I may also add that these numbers represent a period of 6 weeks. Multiply the number of students by 500 (to a full size university) and take a longer period, this could be inefficient even with modern tera size hard drive.

It is the approach that makes these students assiduous to the effect that they keep trying until they get it right. There are weekly quizzes, 10 questions on average, 5-10 choices each (a few questions with 20-40 choices), mostly all-or-nothing, mostly no correctness info while quiz is open,  building multiple attempts.

Some students may study the materials first and then make a few attempts to complete the quiz, others may prefer to just bang their heads against quiz wall. Whatever helps them learning. I just make sure to give them the opportunity. smile

Average of ratings: -
In reply to Itamar Tzadok

Re: Storing too much in question attempt?

by Jean-Michel Védrine -

a few questions with 20-40 choices

I don't really know if the difficulties people with dsylexia have answering to MCQ increase or not with the number of choices, because if it's the case, such questions would be a nightmare for them !

Average of ratings: -
In reply to Itamar Tzadok

Re: Storing too much in question attempt?

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

(It looks like you are making good use of quizzes, which is always nice to see.)

If you care about this, then it is (probably) quite safe to do

UPDATE mdl_question_attempts SET questionsummary = '', rightanswer = '', responsesummary = ''

If you can be bothered, restore a backup of your database somewhere, do the above query, and then

OPTIMIZE TABLE mdl_question_attempts

and then see what size it comes down to. The only think this will break is the quiz responses report (I think, you had better check it). Those column will get re-populated if you regrade the quiz.

If then turns out to be safe, and to save a significant amount of disc space, then you could always set up a cron job to run that query once per day.

Average of ratings: Useful (2)
In reply to Tim Hunt

Re: Storing too much in question attempt?

by Itamar Tzadok -

Finally got to trying that. Reduced the table size by 30%!

Thanks. smile

Average of ratings: -
In reply to Tim Hunt

Re: Storing too much in question attempt?

by Juergen Zimmer -

Dear Tim,

I know this thread is rather old, but I've just received an e-mail from our system administrator asking whether we could reduce the size of the question_attempts table somehow.

Currently, we've got 22Mio question_attempts which take approx. 16GB of space. Our attempt_step_data is equally big. But I suppose the only clean way to free some space would be to delete old question attempts (old quizzes)... We might consider emptying the questionsummaries if it really doesn't hurt.

Cheers

  Juergen

Average of ratings: -