Quiz report query very slow

Quiz report query very slow

에 Ram Aditya 씀
Number of replies: 12

This quiz has over 700,000 attempts, and the report page for this quiz always takes 10 to 15 minutes to load. I am just wondering if this is normal due to the large number of attempts, or if there is something wrong. The query time is so long that we are essentially just waiting for it to finish.

Screenshot%202024-12-23%20191034.png

image%20%281%29.png

Whenever someone tries to load that page, the CPU and RAM resource usage does not increase at all. However, some quiz select query is definitely running, when observing in MySQL using the command SHOW PROCESSLIST.

The settings I set up in /etc/my.cnf:
skip-log-bin
transaction_write_set_extraction=OFF
innodb_buffer_pool_size = 8GB

Environment is just standard VPS:
CPU 8 core
RAM 16GB
PHP 8.1.29
MySQL 8.0.19
Moodle 4.1.5
Centos 7

My question is, Is this long load time normal? I don't have any other Moodle instances with this many quizzes attempt, so any input would be appreciated.

평균 등급 : -
In reply to Ram Aditya

Re: Quiz report query very slow

에 AL Rachels 씀
Core developers 사진 Particularly helpful Moodlers 사진 Plugin developers 사진 Testers 사진
I would say from past experience and for that many quiz attempts, that YES, that amount of time is normal. Note: That up until this past summer, I was using/running almost the exact same environment.
평균 등급 : -
In reply to AL Rachels

Re: Quiz report query very slow

에 Ram Aditya 씀
So this is normal before something changed in your server? Could I know what changes has been made?
평균 등급 : -
In reply to Ram Aditya

Re: Quiz report query very slow

에 AL Rachels 씀
Core developers 사진 Particularly helpful Moodlers 사진 Plugin developers 사진 Testers 사진

Could I know what changes has been made?

Since Centos 7 was at it's end of life, I migrated to Rocky 9. There are just too many break in attempts going on every day, that you cannot afford to ignore updates, especially ones listed for being a security update. This goes not only for the operating system, but also for Moodle and any plugins being used.

평균 등급 : -
In reply to Ram Aditya

Re: Quiz report query very slow

에 Ken Task 씀
Particularly helpful Moodlers 사진

Red Flags

CentOS 7 reached its end of life (EOL) on June 30, 2024
Unless you are on extended support for it.

Not only
innodb_buffer_pool_size
but buffer pool instances ... 1 per Gig.

innodb_buffer_pool_size = 8GB is 1/2 the total memory of the box!   That would affect apache/web services ... which you don't mention ... mod or php-fpm or fast-cgi or ?

Suggest installing MySQLTuner.pl ... a pearl script ... run with superuser creds. It might have some suggestions as to tweaks that will help. Pay attention fragmented tables and to InnoDB metrics.

And one assumes traffic headed to your server is legit ... 'black hats' probably know you are running CentOS 7 and are poking and probing ... all the time.

Your moodle version, while on an LTS version, isn't the highest/most secure ... which is 1.4.15+ now

https://moodledev.io/general/releases/4.1

'SoS', Ken

평균 등급 : -
In reply to Ken Task

Re: Quiz report query very slow

에 Ram Aditya 씀
Thank you for the security reminder. I will reach out to the owner to see if we can implement the necessary updates. We currently have a firewall and Cloudflare in place, which should help ensure that our traffic remains secure. However, I've noticed that the load time for the quiz report remains consistently the same, regardless of whether traffic is high or low, even at midnight.

I’ve been hesitant to use MySQLTuner, as I find its recommendations often unhelpful or even 'harmful'. For instance, it suggested increasing the InnoDB buffer pool size to 64 GB, despite our system only having 16 GB of RAM. Fortunately, I have some knowledge about the buffer pool size, so I opted not to follow that advice, and I remain skeptical of its other suggestions.

I found that an 8 GB buffer pool size appears to be the optimal setting for our instance. This configuration prevents the use of swap space, and during peak traffic, the server utilizes around 15 GB of RAM with less than 100 MB of swap. Increasing the buffer pool size beyond 8 GB resulted in increased swap usage, which is why I settled on this figure.

Recently, we deleted 2.5 million quiz attempts using the plugin found here: https://moodle.org/plugins/local_deleteoldquizattempts. However, this action only slightly improved the load time by a few seconds. According to phpMyAdmin, we still have around 20 million total rows in the question attempts table, as shown in the screenshot. I have 'optimized' the table after that (OPTIMIZE TABLE `database`.`mdl_table_name`;), so there are no fragmented table reported right now.

I have added buffer pool instances as per your suggestion, setting the value to 8, but unfortunately, this did not result in any improvement in load times.

Initially, I suspected that the issue might be related to PHP. I noticed that PHP was running in prefork mode, so I switched it to event mode and implemented PHP-FPM, following the performance recommendations ( https://docs.moodle.org/405/en/Performance_recommendations). However, this change did not yield any benefits. Ultimately, I observed that the query execution time is excessively long.
평균 등급 :Useful (1)
In reply to Ram Aditya

Re: Quiz report query very slow

에 Visvanath Ratnaweera 씀
Particularly helpful Moodlers 사진 Translators 사진
I notice that you speak of *one* quiz. Is this some kind of an aptitude test which is taken by maybe every student and the quiz stays - in a "forever" course? And these numbers like the 700k attempts, are they genuine, in the sense considering the number of students who have taken the test and the number of attempts per student, do they match with the operation you run? How many of the attempts are "historical"? You spoke of deleting 2.5M attempts!

Also, did you go through the question categories and the number of questions? Are the numbers resonable? There have been a surge of cases of multiplying question categories (and questions) in this forum.

When you say 15 GB of 16 GB is utilized, that sounds like more than a coincidence. The Linux kernel is known to fill the unused memory with all sorts of caches and buffers. Do you have a breakdown of those 15 GB?

Finally, isn't those 30 GB in the logstore_standard concerning? What is the retention time for logs?
 
Is it possible to disconnect Cloudflare and compare? Maybe the Cloudflare can not cache requests of this size.
평균 등급 : -
In reply to Ram Aditya

Re: Quiz report query very slow

에 Ken Task 씀
Particularly helpful Moodlers 사진

"Thank you for the security reminder. I will reach out to the owner to see if we can implement the necessary updates."

Would think that would be priority #1.   For 2 cent advice, see * below.

"We currently have a firewall and Cloudflare in place, which should help ensure that our traffic remains secure."

If site began it's life behind CloudFlare then great.   If it didn't, black hats already know it's IP address and they tend not to use FQDN.

MySQLTuner - offers suggestions ... not absolutes.  At the top of MySQLTuner what does it say for

Maximum reached memory usage: (% of installed RAM)
Maximum possible memory usage: (% installed RAM)
Overall possible memory usage with other process is compatible with memory available
Slow queries:
Highest usage of available connections

Default connections is 151 and it is one of the variables tweaked upwards.

And yes, one can get diminishing returns on tweaks to DB config.  

Is there a better tool or better adivce?   Maybe Percona?

https://www.percona.com/blog/how-to-monitor-online-innodb-buffer-pool-resizing/
Deeper dive:
https://forums.percona.com/t/innodb-buffer-pool-size/4427/2

I did notice, also, that there was a table larger then the quiz tables ... that of mdl_log_store_standard_log.   That's the "who done it" table and is involved even in quizzes as it records all users actions - might want to add that to the bottleneck investigations.

Mr. V has also pointed out the quizzes themselves.

Will say this ... over the years, have observed Moodle is a memory hog - period. And it's primarily DB.  I realize that computer sci/server folks don't like to hear it, but 'better to have more than enough and not enough' when it comes to memory.

* So since the server is to be migrated from CentOS 7, the place and time to increase that memory is on new server is now.   I wouldn't do an upgrade in place ... there is a tool for that offered by AlmaLinux folks but it's only to AlmaLinux 8 ... and there is an AlmaLinux 9 out now to keep pace with RH.

Customers also don't like to hear more memory either as that tends to mean more cost.

My 2 cents!

'SoS', Ken

평균 등급 : -
In reply to Ram Aditya

Re: Quiz report query very slow

에 Tim Hunt 씀
Core developers 사진 Documentation writers 사진 Particularly helpful Moodlers 사진 Peer reviewers 사진 Plugin developers 사진
700,000 attempts at one quiz is quite extreme.

If you can work out which query is being slow (e.g. is your database set to log slow queries) then you could try to do an EXPLAIN on the query to see if it is obvious why it is slow.
평균 등급 : -
In reply to Ram Aditya

Re: Quiz report query very slow

에 Ken Task 씀
Particularly helpful Moodlers 사진

Script might help in finding out usage of a site.  example from a sandbox server.

getweek

a moosh bash shell script that reports concurrent users per day of the week.  
because it's a moosh script, must be run from inside moodle code directory.
admin/cli/ is a good location with ownerships/permisisons of:
root -rwx------.

Contains:

start=$(date --date="7 days ago" +"%Y-%m-%d");
finish=$(date +"%Y-%m-%d");
echo 'start = '$start;
echo 'finish = '$finish;
moosh -n report-concurrency --from $start --to $finish

output looks like:

start = 2024-12-17
finish = 2024-12-24
Name: url to site
Active Users: 51
Max Concurrent Users: 5
        on Tuesday, 2024-12-17 18:35:00
Average concurrent users per day of the week
        Monday : 0.02
        Tuesday : 0.73
        Wednesday : 0.3
        Thursday : 0.31
        Friday : 0.24
        Saturday : 0.08
        Sunday : 0.05
Global average concurrent users: 0.18
Average concurrent users considering working days & hours: 0.22

'SoS', Ken

평균 등급 : -
In reply to Ken Task

Re: Quiz report query very slow

에 AL Rachels 씀
Core developers 사진 Particularly helpful Moodlers 사진 Plugin developers 사진 Testers 사진
I've put it off a long time, but finally took the plunge and installed Moosh. But, for the life of me, every variation of trying to run: moosh getweek, just gives me, No command provided, possible commands in current context:, and then gives me the help list of commands. What am I doing wrong?
평균 등급 : -
In reply to AL Rachels

Re: Quiz report query very slow

에 Ken Task 씀
Particularly helpful Moodlers 사진

moosh is 'particular' ...

might be exposing too much here but ....

moosh has versions and one needs to run moosh commands inside moodle code - uses config.php
for variables.   Server where I was using has moosh version 1.21 (PHP 8.1.31) and the 2 instances of moodle - a '4.1.15+ (Build: 20241219) and a 4.5.1+ (Build: 20241213) and right now my IP is one of few that can reach it from outside private network where they reside.

I run moosh as a symlink /usr/local/bin/moosh -> /root/moosh/moosh.php

https://moodle.org/plugins/pluginversions.php?id=522

I put such scripts in moodlecode/admin/cli/ and set ownerships/persions to root:root
no group or guest (-rwx).

In all such scripts I use -n and I run them as root.

If you run moosh without a known option/switch and outside of moodle code you'll get a reduced set of commands.

Here's what that looks like:

moosh -v
moosh version 1.21
No command provided, possible commands in current context:
        apache-parse-extendedlog
        apache-parse-missing-files
        apache-parse-perflog
        backup-info
        base-path
        code-check
        download-moodle
        fontawesome-refreshcache
        framework-import
        generate-cfg
        generate-moosh
        lang-compare
        lang-stats
        languages-update
        log-analyse
        nginx-parse-extendedlog
        perf-analyse
        plugin-download
        plugin-install
        plugin-list
        plugin-uninstall
        report-concurrency
        top

While the report-concurrency is in the list above, getweek won't run ... can't find moodle config.php.

Example:

/path/2/moodlecode/admin/cli/getweek
start = 2024-12-18
finish = 2024-12-25
config.php not found.

Hope that helps!

'SoS', Ken

평균 등급 : -