"Question" table growing very very fast since upgrade to Moodle 3.5.7

"Question" table growing very very fast since upgrade to Moodle 3.5.7

by Service Usagers du Numérique -
Number of replies: 3

Hi everybody,

We upgraded our moodle platform this summer, from version 3.1 to 3.5.7. Everything seems to work fine after this upgrade.

But, during last days, some of our users have complained of slow backups and restorations of their (heavy) courses. Theses courses includes one or several tests with a lot of random questions ...

We ran a lot of benchmarks around our server and different services (apache, postgresql notably) and we did not see any performance issues (our server has 24 cores cpu, 64 Gbytes of RAM, SSD disks, ...).

After further research, we noticed that the 'question' table was growing very fast : 300 000 questions on the 3.1 moodle version (before migration) and over 3 600 000 questions now on the new version (3.5.7). Apparently, each duplication or restoration of a course including a quiz with random questions has a bad impact on this table and increases considerably the number of rows ...

We have already tried to launch as a cli command the scheduled task 'qtype_random\\task\\remove_unused_questions' (cf bug tracking mdl-63260 - https://tracker.moodle.org/browse/MDL-63260) with no effect or success ...

Is there another solution or workaround to solve this problem ... Our moodle platform is becoming less and less reactive and we don't know how to fix it !!!

Thank you by advance for your help or your advices ...

Best regards


Average of ratings: -
In reply to Service Usagers du Numérique

Re: "Question" table growing very very fast since upgrade to Moodle 3.5.7

by Service Usagers du Numérique -

We have found the solution.

The problem is due to restoration of courses including quizz with random questions. Here is the commands we used to solve this problem :

  • https://moodle_server/admin/tool/health
  • UPDATE mdl_question SET parent = id WHERE qtype = 'random' and parent <> id; (in the moodle database)
  • disabling manualy the moodle cron task in /etc/crontab (under unix environment)
  • php admin/tool/task/cli/schedule_task.php --execute=\\qtype_random\\task\\remove_unused_questions (under unix environment)
    This command suppress 10000 rows and needs to be run several times ...
  • enabling manualy the moodle cron task in /etc/crontab (under unix environment)

We think that this database corruption is due to migration process from moodle 3.1.18 to moodle 3.5.7.

Average of ratings: -
In reply to Service Usagers du Numérique

Re: "Question" table growing very very fast since upgrade to Moodle 3.5.7

by Mathew Gancarz -
Picture of Core developers
We're on Moodle 3.5.7+ (Build 20190712) and ran into this issue earlier this year when we were on an earlier 3.5 version, but the fix that was applied in https://tracker.moodle.org/browse/MDL-63260 in combination with letting that remove_unused_questions task resolved the issue.

Once you apply the version with the fix included, the questions should stop multiplying themselves on each restore and the remove_unused_questions task will slowly pair the table back down to a reasonable size.
Average of ratings: -
In reply to Mathew Gancarz

Re: "Question" table growing very very fast since upgrade to Moodle 3.5.7

by Service Usagers du Numérique -

Thank you for your answer.

We applied this fix too and effectively, it has solved our problem.

Average of ratings: -