Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Melanie Hoag -
Number of replies: 26

Two days ago [Wednesday] in mid morning when classes were in session, we had a sudden huge jump in %CPU which then rendered Moodle not accessible as the web service could not respond. We added more CPU's - from 6 to 20 - which helped but still had high usage. We noticed a repeating mysql query of:

SELECT DISTINCT eu1_u.id

              FROM mdl_user eu1_u

            JOIN mdl_user_enrolments ej1_

eu1_u and ej1 may be different

In addition our database grew in about 24 hours from a little over 7GB to 45GB.

I did some research and discovered there is/was an issue with Completion tracking and disabled accounts with this same repeating query along with the same increase in %CPU usage by mariadb. We don't have Completion tracking turned on but we do have some disabled accounts. Yesterday day [Thursday] in the morning I altered two of the disabled accounts as they had duplicate username and email addresses of two active accounts. Shortly after that, the SQL query occurrence dropped and within a few hours stopped.

Today [Friday] in mid morning the same thing has started again. And I don't know if what I did the previous day was related at all.

I have deleted all but one disabled accounts ... and the CPU% has dropped by about 1/2 to 1/4 of the highest amount we saw but is not back to normal for sure!

As this happened twice around the same time frame - Wednesday and Friday and not Thursday could there be some event/activity causing this behavior from a Monday, Wednesday, Friday class?? Our classes are typically a MWF or a TTh pattern.

We have been running Moodle on the same number of processors - 6 - over the past three years. This past summer we moved from a CentOS to a Ubuntu server and moved the Moodle app directory and export/imported the database. The moodledata is on a separate storage system and is an NFS mount point. We've made no changes to settings or plugins and so on and we don't do changes during a semester unless there is something urgent. For example, we had to add more storage when Moodle's use increased a lot when the pandemic began to impact the USA in mid March 202.

Thank you for any help, ideas and thoughts - Melanie

Average of ratings: -
In reply to Melanie Hoag

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Moving to Hardware and performance...
Average of ratings: -
In reply to Melanie Hoag

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Which version of Moodle - and everything else?

Ok - we need to know about your server architecture. Is all this running on one VM or multiple VMs? What monitoring do you have in place (if you don't you should)? What was the database doing during this period (was it busy, was it full of sleep states, did it run out of connections)? How is your storage connected (same box, shared, NFS...)? How is caching organised on your Moodle (do you have a cache server - you should)?

Could you run 'top' - what process was consuming all the CPU time? What was memory doing (how much do you have)?

An obvious question is "where did all that extra database storage go?". That's the queries I'd be interested in - what was it doing to add 30-something GB to the database? The thing I would check first is always the log table but it might not be. If it is the logs, do you limit the age of your logs (you should consider it)?

EDIT:
The only place I could find an SQL query that looked just like that was in an optional plugin we run... a quiz report called 'downloadsubmissions'. That seems very unlikely, but the same query could turn up in a different optional plugin. Have you added any plugins, are they all up to date? Were they all "sensible" plugins in the first place (well used and from a reputable source)?
Average of ratings: -
In reply to Melanie Hoag

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Ken Task -
Picture of Particularly helpful Moodlers

Under 'ideas and thoughts' ... from what you have described of your system, DB server is on the same server as your Web service.   Thus balancing act between resources allocated for both.

Suggest installing MySQLTuner and run it with superuser creds.

You might find that after the move to Ubuntu, tweaks to DB config needed especially after the site being used for a while and in peak usage times.

'SoS', Ken


Average of ratings: -
In reply to Ken Task

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Melanie Hoag -
Thank you so much for your suggestions. I've passed on your recommendations to our IT folks. We have a "mirror" of our production server that we use to test things on and we'll run MySQLTuner on that to get a feel for it. We don't want to add any extra load on the production server until we need to. We are a small university and rarely have issues with Moodle as it just runs. Our IT folks do a lot of different things but among us mysql experts we are not!
Average of ratings: -
In reply to Melanie Hoag

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Ken Task -
Picture of Particularly helpful Moodlers

Mirror of production isn't used so it's more a snapshot.   Yes, good idea to be cautious and install/test something on a non-prod box first, but Tuner gives you a snapshot view of DB server and DB for the moodle.   So one gets server performance recommendations quickly and maybe some recommendations for the Moodle DB as well.

Beginning of an academic year when teachers are adding to courses or even deleting quizzes/stuff is a good time to run tuner.

If none have ever tweaked DB then it's probably way over due for such! smile

'SoS', Ken


Average of ratings: -
In reply to Melanie Hoag

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
> our database grew in about 24 hours from a little over 7GB to 45GB

Scares me. I will definitely dive deep in to it, but don't know how.
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Melanie Hoag -
Yes - scary - and so far it has not ballooned more and sure hope it doesn't. I don't have the knowledge to tackle what is happening and neither do our IT folks. We are a small university and we don't do much to Moodle on the server side other than patches and security stuff and none of that was done before what began on Wednesday. We've been using Moodle for around 10 years - I don't remember the exact year - and it just runs and does its stuff!

Thank you!
Average of ratings: -
In reply to Melanie Hoag

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Yeh - but the basics will not be Moodle specific. See my above post. You need to be monitoring stuff.

We'll still waiting to hear about your architecture.
Average of ratings: -
In reply to Howard Miller

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Melanie Hoag -
I will get the details from our IT folks on the hardware and setup. I do know they monitor the VM system along with other services but I don't know those details. I'm the Instructional Technologist with some now rusty Linux and mysql skills and I work almost exclusively in the Moodle app front end with faculty, students and staff.

We are running Moodle 3.8.3 with a few extra plugins we've been using for the past three plus years. Other than the move from CentOS to Ubuntu the hardware allocation in VM has been the same over the past three years. moodledata is on a separate storage system and is an NFS mount point on the Moodle server. To help with mariadb peaking the CPU% this past Wednesday, our IT folks increased from 6 CPUs to 20CPUs. That has helped but mariadb is still using a lot of CPUs. Typically mariadb may be to 2% to 6% on 6 CPUs.

The repeating repeating mysql query displayed by SHOW PROCESSLIST:

SELECT DISTINCT eu1_u.id

FROM mdl_user eu1_u

JOIN mdl_user_enrolments ej1_

[eu1_u and ej1 may be different]

is still occurring between sleep and other normal site Moodle events by the faculty and students.

We have not had any unable to connect to database errors/messages and as far as I can tell folks are able to do their work in Moodle - the logs reflect this - but it is slower at times.

The usage of Moodle is as expected throughout the week - Monday - Friday are the busiest.

The day after this started we had very few of the mysql queries and CPU% dropped and after awhile IT changed the number of CPUs back to 6.

All was fine until about the same time frame on Friday [yesterday] when CPU% of mariadb peaked and IT moved again from 6 CPUs to 20.

The system is still very busy even though it is a Saturday and typically Saturday is a slow Moodle usage day.

Thank you!
Average of ratings: -
In reply to Melanie Hoag

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
Hi

You wrote:
> We are running Moodle 3.8.3 with a few extra plugins we've been using for the past three plus years.

If you were answering these questions,
>>> Have you added any plugins, are they all up to date? Were they all "sensible" plugins in the first place (well used and from a reputable source)?
No, it doesn't.

The answer is in Site administration > Plugins > Plugins overview: Additional plugins.
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Melanie Hoag -
The plugins we added were all from moodle.org and were up to date when we made the switch from CentOS to Ubuntu in July 2022. There has been no change in these since that time. The plugins we've installed are:

Attendance [mod and block components]
Sharing Cart

Atto HTML editor / Atto plugins:
Word count
Font family
Font size
Toggle full screen
Horizontal rule
More font background colors
More font colors
Import Word file

Reports:
Dates

Themes:
Adaptable
Boost Campus
Fordson
Moove


Thank you!
Average of ratings: -
In reply to Melanie Hoag

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Jean-François Lemoine -
Did you by any chance switch to Ubuntu 22.04, using MariaDB 10.6 ?
We encountered a big performances regression switching from MariaDB 10.3 to 10.6 in July, forcing us to revert back to our old server.
Average of ratings: Useful (1)
In reply to Jean-François Lemoine

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
With 10.6 and 10.7, things stopped being happy at all with MariaDB. For example, MDL-72131 (and related)

I completely gave up on it at that point - I don't know if things have improved. 

MySQL 8 can also have terrible performance in its default configuration. There's plenty of chat about it on the Internet that I won't repeat here. 
Average of ratings: -
In reply to Jean-François Lemoine

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Melanie Hoag -
Thank you for sharing!! We are running version 10.6.7.

Did you experience the degradation immediately or was it over time?

For us - from July through the first week or August, Moodle was used very little. Then usage ramped up the middle of August and then leveled off around the end of August. So about 5 to 6 weeks of our normal semester usage until we had this issue.
Average of ratings: -
In reply to Melanie Hoag

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Jean-François Lemoine -
Every task was taking a bit longer than the previous years. We knew there was a problem but didn’t know where. It took some trial and error to finally blame the database server. We reverted to the old server ten days after the first migration.
Average of ratings: -
In reply to Melanie Hoag

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
If you have NFS - then you *need* to have a proper cache server (I recommend Redis). If you don't, then you're trying to cache to NFS and that's not good at all.
Average of ratings: -
In reply to Melanie Hoag

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Somebody needs to mention that 3.8.3 is very out of date. In fact, completely unsupported.

Even if you updated to the latest release on the 3.8 branch - 3.8.9 - it wouldn't be great but still a lot better. I shudder to think how many bug fixes and security fixes you are missing.
Average of ratings: -
In reply to Howard Miller

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Melanie Hoag -
Yes - we know we need to upgrade and attempted this past summer but were only able to complete the moving to Ubuntu at the time.
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Melanie Hoag -
The database did NOT suddenly grow in size!!!! In my haste I recalled a size from my memory which was the size of the compressed DB dump done in the summer!!!!

So a little bit of happiness here ... and thank you for your concern.
Average of ratings: -
In reply to Melanie Hoag

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Ok - that would have been very weird.
Average of ratings: -
In reply to Melanie Hoag

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Melanie Hoag -
Noticed this on the MariaDB Foundation main page dated 2202-09-09: https://mariadb.org/

Regressions in recent MariaDB Server releases

Our most recent MariaDB Server release introduced some regressions starting with the 10.6 series, affecting 10.7 - 10.9 as well. This blog post is here to explain the problems in hopes that the impact is minimized. We are likely going to release a new version of MariaDB correcting these problems soon. InnoDB Full Text Index on tables causes an assertion error (MDEV-29342) There was a bug in the InnoDB Storage Engine where the full text index could go out of sync with the actual table data. This would happen when only one new row was inserted between the last InnoDB sync (which happens asynchronously) and a server shutdown.…

We are running maridab 10.6.7 - there is a fix on GitHub. However, we don't have a DBA expert on staff or know of one to do all that is needed.

One of the folks on this thread mentioned they reverted back to 10.3 when they had issues with 10.6

We need a DBA and Moodle server side expert ASAP and if any of you have recommendations, please let me know. hoagm@southwestern.edu

Thank you!
Average of ratings: -
In reply to Melanie Hoag

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Ken Task -
Picture of Particularly helpful Moodlers

Not that anyone is following this thread, but ... OP shared the output of MySQL Tuner.

Important clips from that ...

Server has a total of Physical Memory     : 15.6G

InnoDB buffer pool / data size: 12.0G / 54.6G

There are 4 tables in need of optimizing ... the 'save space' info are all in double digit values XXM

Advice was given to:

1. optimize those tables.

2. run tuner again - to see what the new InnoDB buffer pool recommendations would be then ... *IF* RAM could handle it, increase buffer pool accordingly.

Server is in VMWare ... easy to allocate more memory to a guest OS ... how much more from 16?   My guess ... 24Gig ... unknown is apache setup and how much memory it tpically uses.   *But if server admins were going to allow tables to get in that shape and not optimize, then at least 60Gig (price one pays for 0 maintenance of DB, me thinks)

Anyone see anything wrong with that advice?

SoS', Ken


Average of ratings: -
In reply to Ken Task

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
I am skeptical whether tuning the database and/or adding more resources is the right approach. From all what I can see, the real issue, the cause of the drop in performance, is not established. Is it a trashed database, if so what did break it? Does MariaDB 10.6 has bugs which are exposed in Moodle 3.8 or in one of the additional plug-ins? is this drop genuine, just a result of high usage, happened at the start of the new academic year?

These are not easy questions and for professionals having full access to the whole infrastructure. The owner seems to be such a place with their own "IT folks". But those folks don't leave their castle and send the OP on errands. (I know, I know, these are "Moodle problems".) Admire the wizards here beaming all their psychic powers to save the poor "Moodle". I fear if the IT folks don't move their fingers, the prospects are meager.

Just a simple example to underline my point. The exercise is going on an unsupported Moodle, version 3.8, even there on 3.8.3 - not its final release. For the information of the IT folks: The successor, 3.9 (LTS), underwent a series of performance improvements in its early releases. It is still supported (till Nov 2023). Apparently MariaDB has been upgraded but not Moodle. From what I have gathered in discussions here the max. version of MariaDB for Moodle 3.8 is 10.5. See http://www.syndrega.ch/blog/#php-and-dbms-compatibility-of-major-moodle-releases.
Average of ratings: Useful (1)
In reply to Visvanath Ratnaweera

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Ken Task -
Picture of Particularly helpful Moodlers

Knew I could count on you to look at a justified 'deeper dive'! smile

Kinda amazing to me that an edu wouldn't have an IT person at least 'tagged' as a DBA.  That lack of personnel expected of a K12 entity or a DIY in other worlds.   The one person that would have interest is the Moodle Admin who many times doesn't have the backend experience or access and is the 'middle person' when posting in these forums. sad

In addition to OS version, supporting application versions (Web service, DB, PHP,other), there is, of course, version of Moodle.  Any one of those not kept up to date is suspect when things start to go 'south'.   But, since the application is Moodle, that's where discussions normally begin in these forums ... it's only with asking technical questions and even recommending tools not installed that more targeted info can be shared - 'deeper dives' - that have to be performed by other IT folks.

Anyhoo, heard back from OP ... additional memory did help (as I expected it would) ... dunno if tables were optimized and tweaks made to InnoDB buffer pools or not ... we'll see (maybe).

One thing is certain ... moodle is not, and never has been, install it once and forget it.  Has always required some care and maintenance and, like everything else, should be kept up to date.

'SoS', Ken

Average of ratings: Useful (2)
In reply to Melanie Hoag

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Melanie Hoag -
Through the very helpful advice and guidance from Ken Task, our performance of Moodle has greatly improved so that our faculty and students feel we are back to "normal" or better. We do have more work to do get things straightened out, cleaned up, optimized, and develop a plan going forward.

Our interim items in place now are:
    • memory of Moodle server that runs the web and database service was increased to 64GB - we have a 46GB database
    • increased the innodb_buffer_pool_size to 50GB to follow the 80% rule
    • [before my initial post] number of processors is 20 - we increased this when we first noticed a problem from its normal 6 processors
The increase in memory was done late at night when there were very few apache connections. About 6 hours later via top I noticed the database service CPU% had dropped and then later in the day was stable and low. We also noticed the repeating query was not appearing.

We've been running Moodle since Fall of 2010 and over the course of time we've "moved" to different hardwares and versions of Linux along with upgrading to newer version of Moodle. Since that time we occasionally had to add more memory and storage space for moodledata and when we all made the Great Academic Flip in March of 2020, we had to add more storage space for moodledata and bump up the storage of the Moodle server as the database grew.

So for a little over 12 years Moodle just ran and only "complained" when it ran out of storage space. We are a small liberal arts university and do not have the skills in-house to understand in depth mysql/mariadb database maintenance, what to look for and so on. 

Again, I can not express my gratitude enough for Ken's time, patience and clear explanations to me ...the Instructional Technologist/Designer, Google user side "expert", and Makerspaces "manager" with very rusty Linux skills.

Best - Melanie
Average of ratings: Useful (2)
In reply to Melanie Hoag

Re: Moodle 3.8.3 Sudden Drop in Performance Very High %CPU

by Ken Task -
Picture of Particularly helpful Moodlers

Congrats!  and thanks for your kind words ... uhhh ... a 'saint' I ain't! smile

Yep, that's typical of a moodle that is used ... only grows.

Now for the next phase ... ya didn't think I wouldn't mention this now while I had the chance, did ya? ;)

On your dev server/instance, side loading git versioning to make updating and later upgrading core code much easier. smile

'SoS', Ken

Average of ratings: -