Viewing the log of a user or an activity is consuming the CPU and hanging the server!

Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Naaman Fallouh -
Number of replies: 35

Hi,

On Moodle 3.10, when trying to view the log of an activity (assignment) or a user's log, the system hangs and the MySql consumes the CPU usage, please see attached snapshot (LMS-TOP.png).

 Then it fails to Server Internal Error page, see attached snap (LMS-Error.png).

My server specs. are:

CPU
Architecture:        x86_64
CPU op-mode(s):      32-bit, 64-bit
Byte Order:          Little Endian
Address sizes:       42 bits physical, 48 bits virtual
CPU(s):              16
On-line CPU(s) list: 0-15
Thread(s) per core:  1
Core(s) per socket:  8
Socket(s):           2
NUMA node(s):        2
Vendor ID:           GenuineIntel
CPU family:          6
Model:               63
Model name:          Intel(R) Xeon(R) CPU E5-2698 v3 @ 2.30GHz
Stepping:            2
CPU MHz:             2297.339
BogoMIPS:            4594.67
Hypervisor vendor:   VMware
Virtualization type: full
L1d cache:           32K
L1i cache:           32K
L2 cache:            256K
L3 cache:            40960K

Memory
Total online memory:      64G

MySql
Server version: 10.3.22-MariaDB-0+deb10u1 Debian 10
Apache 2.4
PHP 7.3 FPM

Is there any special settings for the log features? and also any optimization tricks and hints?

Best regards,

Attachment LMS-Error.png
Attachment LMS-TOP.png
Average of ratings: -
In reply to Naaman Fallouh

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Ken Task -
Picture of Particularly helpful Moodlers

Moodle defaults to keeping all events in logs.  Can be set to lesser time span
which would remove rows in related log tables of DB.

Make sure cron job is running 1 time per minute and that all jobs (including
ad_hoc) are completing - especially the 'clean ups'.

2 tools that may help with config:

apache2buddy ... a perl script
https://www.techrepublic.com/article/how-to-tune-apache-in-seconds-with-apache2buddy-pl/

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

tuner might find some tables in need of optimizing.

It's a balancing act between web service and DB service.

Goal for moodle would be to get as much of the DB in memory as one could and yet still have enough resources available for the web service and other operating system jobs.

'SoS', Ken


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

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Naaman Fallouh -
Hi,
I did set the auto removing of old logs in my system, and it's working.
Also, I keep trying to tune the Apache and the MySql periodically, the last one shows that there are some tables need optimization but not the logs tables, please see below:
 

 Run OPTIMIZE TABLE to defragment tables for better performance
 OPTIMIZE TABLE `moodlenew`.`mdl_notifications`; -- can free 1487 MB
 OPTIMIZE TABLE `moodlenew`.`mdl_user_enrolments`; -- can free 72 MB
 OPTIMIZE TABLE `moodlenew`.`mdl_message_read`; -- can free 4860 MB
 OPTIMIZE TABLE `moodlenew`.`mdl_role_assignments`; -- can free 125 MB
 OPTIMIZE TABLE `moodlenew`.`mdl_message`; -- can free 3
 
Anyway will try to optimize these and see if it helps.

Regards,
Average of ratings: -
In reply to Naaman Fallouh

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Ken Task -
Picture of Particularly helpful Moodlers

Some things to double check ....

At top of tuner output:
-------- Log file Recommendations
Are there any:
/var/log/mysqld.log contains Xnumber  warning(s).
/var/log/mysqld.log contains Xnumber errors

In -------- Performance Metrics
Maximum reached memory usage
Maximum possible memory usage
Overall possible memory usage with other process is compatible with memory available
Slow queries
Highest usage of available connections
Aborted connections

In -------- InnoDB Metrics
InnoDB buffer pool / data size
InnoDB buffer pool instances

to name a few.

'SoS', Ken


Average of ratings: -
In reply to Ken Task

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Ken Task -
Picture of Particularly helpful Moodlers

Know we are looking everywhere ... but could you please provide info on the clips from MySQLTuner?

Repeated here:

At top of tuner output:
-------- Log file Recommendations
Are there any:
/var/log/mysqld.log contains Xnumber  warning(s).
/var/log/mysqld.log contains Xnumber errors

In -------- Performance Metrics
Maximum reached memory usage
Maximum possible memory usage
Overall possible memory usage with other process is compatible with memory available
Slow queries
Highest usage of available connections
Aborted connections

In ------- InnoDB Metrics
InnoDB buffer pool / data size
InnoDB buffer pool instances

Also, this is a VMWare instance on a VMWare server that has other Guest OS's ... moodle is only one of those.   Know it's possible to set one of those other Guest OS's to grab and consume whatever memory it needs to do whatever it's doing and your OS/Moodle won't show that at all.   But it could suffer from that - causing cron jobs/task to suffer and have to 'catch up' on next run, etc..

That's aside from HHD vs SSD issues.

'SoS', Ken




Average of ratings: -
In reply to Naaman Fallouh

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Something central is broken. The fault could be anywhere, the flooded mysqld process itself could just be a symptom.

Since you've started with hardware, yours is a very powerful machine. I would say more than enough for the vast majority of sites I encounter here. You haven't indicated how big your site is. So that statement is more "statistical". One component you've omitted though: the storage. What technology, how interfaced? File systems? How mounted?

Have you bench marked the disk I/O?

A simple test, not an alternative to low level bench marks, is the Moodle Bench https://moodle.org/plugins/report_benchmark.

That reference to virtualization: Is your Moodle server a virtual machine inside this hardware? Or a collection of VMs in some sort of parallel/cluster operation?

The series of kworker processes in the list is not normal. Each of them means, the kernel is doing some undisclosed work, but it won't tell what exactly. You have to in to the system journal for clues. Often incompatible hardware or broken drivers are the culprits.

Coming to software, you haven't provided any details of your platform other than Debian 10 and MySQL 10.3 and PHP 7.3 FPM. Do you keep an installation journal?

Since you can reproduce the issue, let the database write a protocol of the SQL commands it gets. What does the DBA say? They do all sorts of health checks.

Also try to expose any "hidden" errors by raising the debug level to its maximum, to DEVELOPER. Moodle is sensitive to all sorts of DB parameters starting from the character set to collations to missing data.

Generally, did you go through the https://docs.moodle.org/en/Performance_recommendations, which is one of the forum documentations?

Average of ratings: Useful (1)
In reply to Visvanath Ratnaweera

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Naaman Fallouh -

Hi,

Attached snap (LMS-BM.png) shows the result of Moodle benchmark result.

  • That reference to virtualization: Is your Moodle server a virtual machine inside this hardware? Or a collection of VMs in some sort of parallel/cluster operation?
    • This VM is hosting only Moodle and it's Database.

 

  • The series of kworker processes in the list is not normal. Each of them means, the kernel is doing some undisclosed work, but it won't tell what exactly. You have to in to the system journal for clues. Often incompatible hardware or broken drivers are the culprits.
    • I'll dig into this issue and return back

About the hard disk, the attached snap (LMS-HD.png) is an output of the lsblk, what other kind of details do you need?

Will try the other options you mentioned above (debugging the Mysql queries and the Journal and return back with the results.

Best regards,

 

Attachment LMS-BM.png
Attachment LMS-HD.png
Average of ratings: -
In reply to Naaman Fallouh

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
> This VM is hosting only Moodle and it's Database.

I don't understand. The specs you posted was of a dedicated (hardware) machine. And a powerful one: Intel® Xeon® Processor E5-2698 v3 (16 core, 32 threads!), 64 GB RAM. You run VirtualBox on it, and VB manages only a single VM, which is again dedicated to the Moodle server we are discussing? Why? Why is that additional layer, if the whole machine does only one thing?

About the disk performance: There are all kinds of benchmarks, Bonnie++ is the classic.

Looking at your Moodle Bench results 4 and 5, I don't the that the disk performance is the problem. The results of tests 9 and 10 are miserable though. I don't know where exactly it is pointing to, its homepage has not much info. You need to follow the code, the plug-in is a short one.


Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Naaman Fallouh -
May be there was a misunderstanding, the hardware is not for Moodle only. There are many VMs on-top of it and ours is the one with the specs mentioned above.

About the Moodle bench, in our installation the home page is the login page, and we are using external DB login for academic accounts. Anyway I think it's a separated issue I should look after but not the one of the DB performance, isn't it?
Average of ratings: -
In reply to Naaman Fallouh

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Indeed! The server specs in your OP, are of hardware (for me):

Or, are you saying that your virtualization infrastructure (VirtualBox?) can spawn Intel Xeon CPUs? The ones familiar to me spawn vCPUs.

Are you asking me whether the seriously flawed log in mechanism can influence viewing the logs or whether there is a different cause. The answer is, I don't know!

If the log in mechanism is a big object you don't want to touch now, I would too follow the line of some defect in the database and/or the database server, the network/socket connection,..
Average of ratings: -
In reply to Naaman Fallouh

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Ken Task -
Picture of Particularly helpful Moodlers

What's /scorm purpose?

And 2 devices 200G each 67% used ... mounted as /sessions?   What's that about?

'SoS', Ken

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

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Naaman Fallouh -
These are extra partitions to store Videos and SCORM packages.
Average of ratings: -
In reply to Naaman Fallouh

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Is this a new site or an "old" site. Has it been upgraded - possibly multiple times?

When did this start? Has it always done it? If not, what changed when it started (even if it seems unrelated)?
Average of ratings: -
In reply to Howard Miller

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Naaman Fallouh -
Hi,
The site is on since 2013, and has been upgraded many times till 2020. Where Moodle 3.8 was deployed on the same database on this machine we are using know, and gradually upgraded to 3.10 as it's now.

I noticed this problem from about more than 3 months, but that time the auto removal of log records helped a little but didn't solve the problem, as you can see.

Yes, it's keep happening!!
Average of ratings: -
In reply to Naaman Fallouh

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
I would check your database schema is correct (if often isn't after multiple upgrades) and, even more importantly, if you have any missing indexes.

Schema: (from the command line) - php admin/cli/check_database_schema.php
Missing indexes: SiteAdmin->Development>XMLDB editor->Check Indexes
Average of ratings: Useful (2)
In reply to Howard Miller

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Naaman Fallouh -

Looks there isn't any missing indexes!

Attachment LMS-Indexes.png
Attachment LMS-Schema.png
Average of ratings: -
In reply to Naaman Fallouh

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Naaman Fallouh -

Hi,

I followed up the executed query related to the assignment log page, I found this:

SELECT *
FROM mdl_logstore_standard_log
WHERE courseid = '103' AND contextinstanceid = '78825' AND contextlevel = '70'
ORDER BY timecreated DESC, id DESC
LIMIT 0, 100
;
/* Affected rows: 0  Found rows: 100  Warnings: 0  Duration for 1 query: 00:01:20.7 (+ 0.563 sec. network) */

 

The table is mdl_logstore_standard_log, holds 87,665,170 rows and its size is 35.8 GB.

Please note that it took about 1:20 minutes to be executed on the server, is this normal? Or this might be the problem in my case?

Regards,

 

Average of ratings: -
In reply to Naaman Fallouh

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
For 87 million records, 1:20 minutes seems a little long. A lot might depend upon your server's speed, configuration, and database configuration. I am not a database or SQL expert.

As a point of reference, my mdl_logstore_standard table has around 7 million records in Moodle 3.11. A similar query takes me around .003 seconds, as reported by phpMyAdmin. This is on my GoDaddy VPS running MariaDB.
Average of ratings: -
In reply to Rick Jerz

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by AL Rachels -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
No mention has been made as to the type of drive the database is on, which can make a huge difference. Also where it is located. Same box? If it is a spinner, instead of SSD, then I would think the time is probably about right. I can remember on my old Windoze 7 desktop computer, with a spinning HD, that it took seven and a half minutes to boot up. My first, and definitely not cheap SSD, dropped that boot time to just thirty seconds. My current Windows 10 is down to just 19 seconds. A couple of weeks ago, I copied 690GB from a spinning 759GB drive, over to a 2TB SSD and it took somewhere around 24 hours. Not sure of the exact number of hours due to halts for questions it asked while I was sleeping.
Average of ratings: -
In reply to AL Rachels

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
My GD VPS is SSD.
Average of ratings: -
In reply to AL Rachels

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Naaman Fallouh -
Hi,
The HD is not SSD.

The database files are located on: /var/lib/mysql/

The /var is mounted to the sda disk:
 
root@lms:~# lsblk -f /dev/sda
NAME                 FSTYPE      LABEL UUID                                   FSAVAIL FSUSE% MOUNTPOINT
sda
├─sda1               ext2              80e4f3f1-c997-44b8-9860-e2dd6d70dc3a    134.1M    36% /boot
├─sda2
└─sda5               LVM2_member       rhutFt-vgMf-cy4l-dIJv-NV9e-KKbK-cLFth9
  ├─lms--vg0-root_lv ext4              cd9406c8-d4b7-4fdb-ac6c-bd22fb1d6dc8       15G    79% /
  ├─lms--vg0-swap_lv swap              6975a523-b36d-4086-b847-4be8c7f1194d                  [SWAP]
  ├─lms--vg0-home_lv ext4              93aeb5d9-b1e8-4f2a-aa2a-060d5e81fb3f       88G     1% /home
  └─lms--vg0-var_lv  ext4              541daaaa-6170-4f4f-ae26-76e59381caea    167.8G    82% /var
root@lms:~#
 
Regards,
Average of ratings: -
In reply to Naaman Fallouh

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by AL Rachels -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
Pardon me if you know this already:
There are multiple things working against you and making the long, 1:20 access time, entirely plausible.
  • The mysql data is stored in thousands of small files. In Moodle this directly translates into more time to read/write the files needed for each "page."
  • One query can create dozens, of file reads. (just logged into my dev site, and there were 889 DB reads and 2 writes to get to the front page) To see this info for your site, go to Site administration > Development and turn on the Performance info so you can see how many read/writes occur for the simplest of things in Moodle because almost EVERYTHING is in the database.
  • Your drive appears to be using 82% of its allocated space.
  • With this much space in use, new files become fragmented and scattered all over the drive platter due to not enough space to write them out in one contiguous string of sectors.
  • Drive heads have to move more to assemble each file when reading it.
  • Drive heads have to move more when writing files.
  • When the heads move they have to have settling time to stabilize on the correct track.
  • The heads often have to "wait" for the correct data sector to come around so it can read/write the next block of a file.
  • Many times after reading a file, it needs to be re-written to the disk due to new data being added. e.g. The two writes for my login wrote my login time for today to the DB.
  • The drive has to seek empty locations for the file and when the drive is that full, one location is seldom big enough for the whole file, so it gets scattered to wherever there is some empty space.
  • Then the drive has to go back and mark all the old file space as being free for use.

All of the above adds up to extending the time to access your data.
What can you do to improve/lower the access time?
  • Change to an SSD drive - NO MOVING PARTS means no wait times for all those mechanical events you have in a spinning HD.
  • If you cannot change drives, make sure you defragment your HD. NOTE: Defragging is NOT needed for SSD's.
NOTE: Defragging a drive takes a long time, so do it during off peak hours for your site.
Even though your data drive is saying it is 82% in use, that number is misleading due to the minimum allocation size. With the huge size drives available the OS generally allocates 16 or 32 KB for the smallest amount of space to add to a files storage space. I saw one once using 64 KB. In simple terms, this means that even though a data record such as a user name, might only have a dozen letters in it, when it gets written to the HD, it is allocated 16 or 32 KB of space. (I used to change mine to the minimum of 4 KB.)

Sorry for rambling on. I've procrastinated enough and need to go back to work. 🤪
Average of ratings: Useful (1)
In reply to AL Rachels

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Naaman Fallouh -
I'd asked for extending the HD from the sysadmins, and will see the results.
Average of ratings: -
In reply to Naaman Fallouh

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Naaman Fallouh -
Hi again,

After optimizing the database, extending the HD and de-fragmenting the /var directory, I still have huge load on the CPU caused by Mysql, Even when the site is in maintenance mode!!
Average of ratings: -
In reply to Naaman Fallouh

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
> I still have huge load on the CPU caused by Mysql, Even when the site is in maintenance mode!!

Then it is not related to Moodle. Did you log those DB requests?

OK, there is the possibility of a Moodle's scheduled task flooding the database. That will show up in the web servers process list. Either way, worth going through the Moodle's task logs (Site administration > Server > Tasks).
Average of ratings: Useful (1)
In reply to Visvanath Ratnaweera

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Naaman Fallouh -
I noticed the queries log while it was in maintenance, they are all related to Moodle.

> OK, there is the possibility of a Moodle's scheduled task flooding the database.
That's the only possible reason.

> worth going through the Moodle's task logs (Site administration > Server > Tasks).
I was logging only failed tasks, I turned on the log for everything and will monitor it.

Do you think an update to the database engine might help the issue?
Average of ratings: -
In reply to Naaman Fallouh

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
My personal opinion is, hope is a primary energy in the analog world, but a trap in the digital world. In simple terms, hope is the wrong reason for anything technical, let alone in IT, where Murphy's law goes first.

Those Moodle-related DB querries coming from a Moodle instance in maintenance mode will leave me sleepless.
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Naaman Fallouh -
Yesterday, Sysadmin guys made some tuning for the Debian OS, in the sysctl config files.
Today I could request an assignment log without causing the server stop responding, and the DB didn't consume the CPU as much as it was doing before this modification.

Will keep monitoring the situation and hope this helps.
Average of ratings: -
In reply to Naaman Fallouh

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Ken Task -
Picture of Particularly helpful Moodlers

And what were those "tuning for the Debian OS, in the sysctl config files"?

Uhhh .... give back! smile

'SoS', Ken


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

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Naaman Fallouh -
Sure, but Am not familiar with any of them shy
You can put them directly in the /etc/sysctl.conf file or it's better to have you own file in the /etc/sysctl.d/ directory.

 

net.ipv4.tcp_keepalive_time=900
net.ipv4.tcp_fin_timeout=30
net.ipv4.tcp_max_orphans=16384
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_tw_recycle=1
net.ipv4.tcp_rfc1337=1
net.ipv4.tcp_no_metrics_save=1
vm.swappiness=10
vm.dirty_background_ratio=3
vm.dirty_expire_centisecs=500
vm.dirty_writeback_centisecs=100
vm.dirty_ratio=40
net.ipv4.tcp_max_syn_backlog=4096
net.core.netdev_max_backlog=4096
net.core.somaxconn=4096
net.ipv4.tcp_fin_timeout=30
net.ipv4.tcp_max_syn_backlog=8192

To be honest, I don't know what these settings do yet. I'll study about them.

Average of ratings: Useful (1)
In reply to Naaman Fallouh

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Naaman Fallouh -

sadangry Today I scored a new record of CPU, it's 404% while trying to see the logs on a folder in a course !!

Here is the invoked query:

SELECT * FROM mdl_logstore_standard_log WHERE courseid = '1884' AND contextinstanceid = '77812' AND contextlevel = '70' AND crud = 'c' ORDER BY timecreated DESC, id DESC LIMIT 0, 100

Executing this query from a client took over 3 minutes before causing the client to hangout !!!

Is there a way to enhance querying this table?

Regards,

Attachment TOP2.png
Average of ratings: -
In reply to Naaman Fallouh

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Ken Task -
Picture of Particularly helpful Moodlers

VMWare 64G Guest OS (Debian)
3.10 Moodle
64 Gig total memory allocated to the VMWare Guest OS

At the top of tuner:

Maximum reached memory usage: xG (x% of installed RAM)
Maximum possible memory usage: xG (x% of installed RAM)
Overall possible memory usage with other process is compatible with memory available
* Slow queries: 0% (n/nM)
Highest usage of available connections: x% (n/n)
Aborted connections: x%  (n/n)

In config of MariaDB
innodb_buffer_pool_size = 32G
that's 1/2 of total memory available.

IF slow logging is turned on that does drag the server some.
See * above

slow_query_log            | OFF

One can turn on, let it log, then turn it off:

# to turn on:
mysql -e 'set global log = 1' -u root -p'[password]'
mysql -e 'set global log_slow_queries = 1' -u root -p'password'
mysql -e 'set global slow_query_log = 1' -u root -p'password'

If you install multitail one could watch those logs in realtime.

multitail -i /var/log/mysql/mysql-general.log -i /var/log/mysql/mysql-slow.log -i /var/log/httpd/access_log -i /var/log/httpd/error_log;

# to turn off
# set global slow_query_log = 0
# set global log_slow_queries = 0
# set global log = 0;

Once off, those logs can be copied out of /var/log/mysql/ saved as a text file
(they are ascii anyway), download them and inspect with notepad/textedit or
any text editor

Comment: one would have to know SQL to read and determine what issues were
with slow logging.

How many guest OS's are there on this VMWare box?

IF one were to go to the public IP address or use http:// to your site, what
does one see?

Are you getting a lot of traffic on port 80?

'SoS', Ken



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

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Naaman Fallouh -
Applying the slow_query_log leaves a trace in the file when trying to display the log of an activity or course, and for some reason it's now working fine!!

Note: I applied some of the recommendations from post Thorsten Bartel below. Mainly disabling the query caching and raising the open table:

query_cache_type = 0
query_cache_size = 0
query_cache_min_res_unit = 16K
table_open_cache = 900
Average of ratings: Useful (2)
In reply to Naaman Fallouh

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Thorsten Bartel -
Picture of Core developers
To me, this sounds like your DB could use some optimizing. What are the stats ("show global status like '%';" - where % is replaced by the following) on:
- opened_tables
- open_tables
... compared to uptime? (Is "opened_tables" rising fast? Is "open_tables" significantly lower than the number of tables in your moodle database?)

Please also provide your config for ("show global variables like '%';" - again, replace % with the following strings):
- table_open_cache
- innodb_buffer_pool_size
- query_cache_type
- query_cache_size
- tmp_table_size

Depending on your answers, the problem may be rooted in your DB opening too many tables and trying to fit them into memory but not having enough RAM to do so (simplified take). This may lead to thrashing, significantly slowing down your server performance and leading to CPU load scores above 100% (which usually indicates I/O wait or some other kind of latency).

General guidelines:
- Have enough RAM to fit your entire DB in there *plus some* and set "innodb_buffer_pool_size" accordingly.
- Set "table_open_cache" to at least twice your table count for the Moodle DB.
- Disable the Query Cache.
- Set "tmp_table_size" to reasonable levels (this might need some tweaking and is most likely the least impactful option).
Average of ratings: Useful (2)
In reply to Thorsten Bartel

Re: Viewing the log of a user or an activity is consuming the CPU and hanging the server!

by Naaman Fallouh -

Hi,

> opened_tables = 1466
> open_tables = 512

Generally, I followed DB optimization instructions from Moodle Docs. and from the MySqlTuner-perl tool.

Here are our customized settings:

[mariadb]
max_connections = 400

query_cache_size = 256M
query_cache_type = 1
query_cache_min_res_unit = 16K

wait_timeout = 300
interactive_timeout = 300
performance_schema = ON

join_buffer_size = 3G
table_definition_cache = -1
table_open_cache = 512

innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 32
innodb_log_file_size = 4G

tmp_table_size = 32M

general_log_file       = /var/log/mysql/mysql.log
general_log            = 1
log_error = /var/log/mysql/error.log
slow_query_log_file    = /var/log/mysql/mariadb-slow.log
 

Will try to apply the guidelines you mentioned and return back with a feedback.

Best regards,

Average of ratings: Useful (1)