Moodle 4.0.1: Very slow access to the question bank.

Moodle 4.0.1: Very slow access to the question bank.

by Thibaut Meunier -
Number of replies: 24
Picture of Particularly helpful Moodlers

Hello,

On version 4.0.1 access to the question bank from the course is very slow from the moment there have been attempts by users.

To highlight the problems, I have done the following:

Measuring the access time to the bank on the platform in 3.11.6: 0.77s


Cloning of the platform then update to 4.0.1 (without error)

Measuring the time it takes to access the bank on the 4.0.1 platform: 24.53s


On the latter, there are a lot of requests to the database.

I have tried with different courses and also by importing a course on a blank database and I still have the same problem.

The problem seems to come from the new indicators: Needs checking, Facility index, Discriminative efficiency, I have the impression that they are recalculated every time I open the question bank.


Average of ratings: Useful (1)
In reply to Thibaut Meunier

Re: Moodle 4.0.1: Very slow access to the question bank.

by Thibaut Meunier -
Picture of Particularly helpful Moodlers
Hello,
I took advantage of the release of bitnami/moodle in 4.0.1 to test my question bank issue on a platform that was not upgraded.
I imported my course with the users and their attempts.
The problem is identical. The access time to the question bank is very long from the course and there are always a lot of read and write requests.
If anyone has an idea or is willing to test, I can share one of my courses.
Average of ratings: -
In reply to Thibaut Meunier

Re: Moodle 4.0.1: Very slow access to the question bank.

by Ken Task -
Picture of Particularly helpful Moodlers

Prior to vr 4 there was a tool called 'healthcheck'.  On a K12 site that is used primarily as a testing server, running that frequently would find solutions to issues related to quizbank.

In 4, one can install that healthcheck as a plugin - see below.

Another suggestion ... install MySQLTuner (a perl script) - not a moodle plugin - run it with super user creds for DB server.    It might see and have some suggestions for tweaks to DB config or even condition of tables in need of optimizing (not unusual to see tables related to quiz).

https://github.com/major/MySQLTuner-perl

Here's one tutorial on using:

https://www.linode.com/docs/guides/how-to-optimize-mysql-performance-using-mysqltuner/

https://moodle.org/plugins/tool_health

'SoS', Ken

Average of ratings: Useful (1)
In reply to Ken Task

Re: Moodle 4.0.1: Very slow access to the question bank.

by Thibaut Meunier -
Picture of Particularly helpful Moodlers

Thanks for the help. The healthcheck plugin did not find any errors.

The mysqltuner report is not easy to interpret for a non specialist like me. If someone sees a problem in this report, I'm interested.

Average of ratings: -
In reply to Thibaut Meunier

Re: Moodle 4.0.1: Very slow access to the question bank.

by Ken Task -
Picture of Particularly helpful Moodlers

Yeah, that's a 'hat' no one really anticipates wearing running a DIY Moodle - until one needs to put it on! :|  

Good that Health Check didn't find anything with Quizzes but as far as speed don't think it ever did. :|

Since I, also, am not a certified DB admin person, but suggested you install it, guess I'll give 'er a go at interpretations.

In the Recommendations ... last of the report ...

MySQL was started within the last 24 hours - recommendations may be inaccurate

So before tweaking anything, might want to wait a day and run tuner again.   I run it about once a week and have had to tune again depending upon how system is being used.   When I first install a site, more frequently.

However, this one probably needed to be addressed ASAP:

1. [!!] Successfully authenticated with no password - SECURITY RISK!

Think I said to run it with superuser credentials (login/pass) for DB server .. which appears to be 'root' with whatever password given.   But, you could run it using the DB user/DB pasword you have in your config.php of moodle.

Overall, not bad ... the key things which are all OK ...:

In Performance Metrics

[OK] Maximum reached memory usage: 492.6M (12.48% of installed RAM)
[OK] Maximum possible memory usage: 3.2G (82.92% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/64K)
[OK] Highest usage of available connections: 2% (4/151)

[OK] Aborted connections: 0.00%  (0/135)

In InnoDB Metrics

[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/13.4M

In the Recommendations:

performance_schema=ON

which needs to be added to your my.cnf file (main config file for DB server) and DB service restarted ... wait a day for it to collect info.

The following related to the security warning above:

General recommendations:
    Restrict Host for 'bn_moodle'@'%' to 'bn_moodle'@LimitedIPRangeOrLocalhost
    RENAME USER 'bn_moodle'@'%' TO 'bn_moodle'@LimitedIPRangeOrLocalhost;
    Restrict Host for 'root'@'%' to 'root'@LimitedIPRangeOrLocalhost
    RENAME USER 'root'@'%' TO 'root'@LimitedIPRangeOrLocalhost;

I run MySQL, not MariaDB, but when either installed, initially, there was a command to run to secure the installation ... having to do with host and users.

IF DB server is on the same server as your code, config.php should have 'localhost' for DB server.   And, to make sure DB is secure, localhost is the only way to access it ... + user moodle and user root should be the only users to use localhost.   MySQL/MariaDB protects itself using DB's and tables of it's own.   From DB client: show databases; one would see those if user auth allows (root should).

Now before doing anyting with DB, good idea to make a backup (sql dump) of your Moodle DB.  Have never regretted the time it took to do that! smile

Please see:

https://mariadb.com/kb/en/mysql_secure_installation/

for shoring up security (host/user).

If MariaDB works like MySQL - supposed to be a plugin replacement to MySQL - without the 'skipnetworking' turned on ... which tells DB server NOT to do a DNS lookup for each request ... that could be one reason for slowness.   localhost supposed to be using a 'socket connection' anyway so if restricted to localhost no need to look that up as it's always gonna be 127.0.0.1 or ::1!

https://mariadb.com/kb/en/connecting-to-mariadb/

'SoS', Ken


Average of ratings: Useful (1)
In reply to Ken Task

Re: Moodle 4.0.1: Very slow access to the question bank.

by Thibaut Meunier -
Picture of Particularly helpful Moodlers
As I continued testing, I found that disabling "question statistics" in Manage question bank plugins removes the slowness problem.
Average of ratings: Useful (7)
In reply to Thibaut Meunier

Re: Moodle 4.0.1: Very slow access to the question bank.

by Ken Task -
Picture of Particularly helpful Moodlers

That's usually the way ... dig some more and one finds.  Congrats!

Cron running frequently enough?

In admin/tool/task/scheduledtasks.php
There is a clean up for quiz stats.   That doesn't have a 'run now' link?!!!!

Have learned that DB tweaking ... keeping it 'lean and mean' ... and getting as much as one can to get the DB in memory as opposed to disk IO ... are keys to a 'fast running' moodle! smile

'SoS', Ken


Average of ratings: -
In reply to Ken Task

Re: Moodle 4.0.1: Very slow access to the question bank.

by Thibaut Meunier -
Picture of Particularly helpful Moodlers

Thank you for your time. I will look further into the recommendations on the database. For now I need to work on the content of my courses.😃

There is indeed this task "\quiz_statistics" that runs regularly as does the cron. However, I think this task is more about quiz statistics than question statistics, especially since this task existed in previous versions.

I still have a doubt about the fact that the problem comes from my database...

The first test I did was on a clone of my production platform that had been updated. But now the tests I am doing are on a brand new installation with only one imported course.

Average of ratings: -
In reply to Ken Task

Re: Moodle 4.0.1: Very slow access to the question bank.

by Thibaut Meunier -
Picture of Particularly helpful Moodlers

Perhaps there are very few 4.0.1 platforms in production with courses where there are statistics on questions and people try to access their question bank? 😀😃

Average of ratings: -
In reply to Thibaut Meunier

Re: Moodle 4.0.1: Very slow access to the question bank.

by Ken Task -
Picture of Particularly helpful Moodlers

Ya know, I never understood folks who want 'latest and greatest' without knowing why?

A .0 is new - and a .0.1 isn't very far away from a .0.  If you installed it via git, then no problem ... easy to get fixes to code.   If you didn't install via git, then yes, I understand the pain one anticipates.

I, personally, would shoot for the next LTS - which is 4.1 .. and wouldn't upgrade until there is at least a point release for that (4.1.1) - but that's me.

Your Tuner did show no slow queries - but then again, your DB config didn't have 'performance metrics' - think you'd want to turn that on - considering the reported problem.   Plus it really hasn't been used by students/participants, yet.

Security issues should be addressed now (IMHO) ... mainly because we have a tendency to forget about those we intended to do as soon as we get what we are focused upon right now.

Years ago, a new server just setup for internet and accessed by anyone would get it's first poke/probes within 4 minutes ... today .... that's within the 1st minute.

At least you have MySQL tuner installed ... and, hopefully, bookmarked/favorited the docs for MariaDB admin.

Now am not saying that 4.0.x doesn't have some issues in this area ... you could file with Moodle's tracker to report it and hopefully get true Moodle HQ programmers to look at it.   Typically, however, you'd have to provide a way for those folks to duplicate the issue - and that's kinda a problem ... your environment may not be the same environment they would use to test the issue.

It's your server ... you can certainly run it any way you see fit! smile

'SoS', Ken


Average of ratings: Useful (2)
In reply to Thibaut Meunier

Re: Moodle 4.0.1: Very slow access to the question bank.

by Helen Foster -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers Picture of Translators

Hi Thibaut,

Good to hear that you were able to solve your question bank slowness problem by disabling question statistics.

I'm going to move your discussion to the Quiz forum in case any of the question bank experts there have anything to add.

Average of ratings: -
In reply to Helen Foster

Re: Moodle 4.0.1: Very slow access to the 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
Thank you very much for reporting this issue, and working out that it is to do with the statistics plugins. I created MDL-74762 so that it could be worked on. If you are interested in this issue, you might like to watch that issue.
Average of ratings: Useful (3)
In reply to Thibaut Meunier

Re: Moodle 4.0.1: Very slow access to the question bank.

by Dámaso Velázquez -
Thank you to both. I resolved this problem (with Moodle 4.0.1) disabling quiz stats.

Best Regards.
Average of ratings: -
In reply to Thibaut Meunier

Re: Moodle 4.0.1: Very slow access to the question bank.

by Barney Treadway -
I'm seeing that even in 4.1.1 this is still the case. Few hundred questions in the question bank and taking 30+ seconds to list questions, turn off statistics and down to 3 seconds. No real hit to CPU or Mem though, just slow.
Average of ratings: -
In reply to Thibaut Meunier

Re: Moodle 4.0.1: Very slow access to the question bank.

by Ali Arekat -
Dears,
I also faced the same issue and solve it by disabling the questions statistics.
From another hand, I have another issue that I wish to help in that:
The loading time for the my courses page (Course Overview Block) for any user other than the administrator is very slow, it takes around 16 seconds. I used the health check and every thing is OK.
I faced this issue just when I upgraded the system to moodle 4, I also tried to upgrade the system to moodle 4.1+ and still facing the same issue.
ps: I faced this slowness on the Dashboard page too but when I removed the Course Overview block the problem was solved at that page.
I'd appreciate it if anyone can help in that.
Regards,
Ali,
Average of ratings: -
In reply to Ali Arekat

Re: Moodle 4.0.1: Very slow access to the 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 other slowness, you are more likely to get useful help by posting in the 'Hardware and Performance' forum. https://moodle.org/mod/forum/view.php?id=596. The two documentation links at the top there are worth reading.
Average of ratings: -
In reply to Tim Hunt

Re: Moodle 4.0.1: Very slow access to the question bank.

by Ali Arekat -
Thanks for your reply.
I posted here as I am facing a slowness in loading question banks, I tried to disable the Questions Statistics to solve that but unfortunately I faced another issue when I disabled it.
The following error appeared after disabling the Statistics

Exception - Call to a member function get_column_name() on int
more info in this link
https://docs.moodle.org/401/en/error/moodle/generalexceptionmessage
Average of ratings: Useful (1)
In reply to Thibaut Meunier

Re: Moodle 4.0.1: Very slow access to the 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

While I am posting in this thread. Let me say that the Open University has now moved to Moodle 4.0, and then rapidly had to disable the question_statistics plugin sad

So, the chances are, I will soon be coding a fix for MDL-75576 (and possibly MDL-75197).

While thing about this, I had a question, which first requires some explanation: At the moment the way it works is this:

  1. Since MDL-74762, there is a scheduled task which churns away in the background and computes the statistics that need to be shown in the question bank and stores them in hte database.
  2. When a user goes to the question bank, it tries to load the stored statistics. If it finds them, that is fine, and things are reasonable fast.
  3. But, if the statistics are not available yet, it imediately tries to compute them, and this can be very slow.

It seems to me that 3. is not really a good idea. I am thinking that, if the statistics are not currently available, then it would be better to just display a message (e.g. 'Not available') in place of the statistics, possibly with a help icon to explain that the statistics will become available once the background task has computed them.

At least, I am thinking that is better behaviour than just having the question bank be unusably slow. The reason I am posting here is to ask if you agree that change would be an improvement?

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

Re: Moodle 4.0.1: Very slow access to the question bank.

by James Roberts -
Tim,

In this, I am a simple teacher who uses Moodle.
Our Admin upgraded to 4.01 (I think) and the Question Bank slowed down. Admin disabled the "question statistics" and things returned to normal speed. Then, we upgraded again to 4.05 (?) - the stat columns re-appeared in the question bank - and everything slowed again. (To give you an idea, i have about 500 questions in a single category and it takes about 4 minutes before I can re-access a question. I timed it.)
=====
1. The stats thingee is great but as a simple teacher, I don't need to see the stats every time I use the question bank. Indeed, I would prefer a separate button to generate a stats-report - for example at the end of the semester.
2. I note that the stats are returned to blanks/zero once I edit a question, even merely to change its name/category.
3. The stat columns take up alot of horizontal pixels on the already crowded question bank.
====
I like your idea of a link/message to "stats will be available".
For example, when I save a file in Windows or any OS, I don't need to know immediately how quickly all the other files are affected.
And Tim, thanks. As a simple teacher, I really appreciate your work.
Average of ratings: -
In reply to Tim Hunt

Re: Moodle 4.0.1: Very slow access to the question bank.

by Ewout ter Haar -

I think a message "Not available", with a button "Compute now" would be ideal.

I'm thinking about the behavior of the grading reports, that have a message "activity deletion in progress ". But in this, and other asynchronous tasks that depend on scheduled tasks, a manual execution would be a good backup plan. We had a situation once where there was a problem with the scheduled tasks queue. Thankfully Moodle kept working (badly), but the ability to do at least some tasks manually (synchronously ) would have helped at that time.

Average of ratings: -
In reply to Tim Hunt

Re: Moodle 4.0.1: Very slow access to the question bank.

by Christopher Sangwin -
Picture of Particularly helpful Moodlers Picture of Plugin developers
Thanks Tim,
This is a very helpful thread. I've just disabled the question statistics on Moodle 4.1.4+ (Build: 20230707) in Edinburgh for the same reasons.
Chris
Average of ratings: -
In reply to Christopher Sangwin

Re: Moodle 4.0.1: Very slow access to the 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
Note that more work on the statistics has just been integrated: MDL-79254 (which includes a fix for MDL-78588) and MDL-78580. So, those should be in Moodle 4.1.6 & 4.2.3 & 4.3, which should be released on Monday. So, hopefully, that finally deals with all the stats issues and they are finally usable. Sorry it has taken so long.

Thanks to Mark Johnson for developing those latest fixes, and to University College London for funding that development.
Average of ratings: Useful (2)
In reply to Tim Hunt

Re: Moodle 4.0.1: Very slow access to the question bank.

by Brett Dalton -
Picture of Moodle HQ Picture of Particularly helpful Moodlers
Tim the work done by yourself and Mark is greatly appreciated in moving these issues forward. They are definetly not trivial to improve or resolve. For everyone else it would be great to get as much feedback from the community when they start testing these new releases or upgrade in production as to the improvement in performance.
Average of ratings: Useful (1)