Maxing database CPU

Maxing database CPU

by Rachel Martin -
Number of replies: 20

Good morning,

We are seeing an issue where every few days, Moodle seems to be doing some task that goes through every single user on the system. During this time, it maximizes the CPU use and ties up major resources.  

We are running SQL Server 2016.  The webserver itself is fine during this period and there aren't any errors, but the load on the database side is an issue.  We are currently on Moodle 3.5, we will be upgrading to 3.9 in the coming months. 

I cannot find any scheduled task that seems to line up with the time that this occurs. It seems to go through all user accounts on the system including those from years past with no recent activity.

Does anyone have any ideas?

Average of ratings: -
In reply to Rachel Martin

Re: Maxing database CPU

by Ken Task -
Picture of Particularly helpful Moodlers

Authentication being used is LDAP?  There is a command line sync script that would do as described me thinks!

'SoS', Ken

Average of ratings: -
In reply to Ken Task

Re: Maxing database CPU

by Rachel Martin -
No. We have a modified plugin based off of manual authentication that allows sign-in based on a preshared key. The few database calls in it are pretty standard. DB->get_field, DB->get_record_sql are both used, but include parameters. And there is a call to get_complete_user_data('username', $username);
I don't see anything that would have it step through records. I can make sure LDAP is disabled since we aren't using it. We aren't running any command line functions, the only task outside of Moodle is the windows scheduled task to run the cron every 2 minutes.
Average of ratings: -
In reply to Rachel Martin

Re: Maxing database CPU

by Ken Task -
Picture of Particularly helpful Moodlers

Ok, no LDAP ... how about automated backups?

This:

We aren't running any command line functions, the only task outside of Moodle is the windows scheduled task to run the cron every 2 minutes.

Cron/scheduled task are pointed at the moodle ... and it's your Moodle that executes them.

Focus attention there.

'SoS', Ken

Average of ratings: -
In reply to Ken Task

Re: Maxing database CPU

by Rachel Martin -
No automated backups in Moodle.
They do server and database backups on a regular basis, so we do not use any of the automated backup tools that Moodle provides.
From what I have seen nothing is outputted in cron logs regarding any of this. Our cron has been completing normally on each run.
Average of ratings: -
In reply to Rachel Martin

Re: Maxing database CPU

by Ken Task -
Picture of Particularly helpful Moodlers

Ok ... is this an all in one server ... moodle/web (IIS?) on same server as the DB server?

Is this server in a virtualized environment ... like VMWare ... and is it a VMWare server NOT dedicated to just Moodle ... ie, there is some other DB's or an app using DB?

There are some task in scheduled task related to users ... clean up as an example ... assuming that would run through all users to see which users have not logged in ... ever ... etc.

So in the Moodle admin interface, Users ... set filters to look at various things ... like 'last access = never' etc.

BTW, what you see in the Moodle Admin UX isn't 100% accurate ... to really see what's in mdl_user table one needs to resort to a DB client ...  the defaults for a moodle is never to delete users ... so your mdl_user table might actually have more rows than what one can see in Moodle.

Also .... is the maxing out of the DB always related to users?   How are you able to determine that?

'SoS', Ken

Average of ratings: -
In reply to Ken Task

Re: Maxing database CPU

by Rachel Martin -
The following information is from our infrastructure team. I do not control the DB or Server side. I am the primary admin for Moodle itself and manage development of any plugins and Moodle updates. The way we are organized, the servers and databases themselves are handled by our infrastructure team.

Two servers in a VMware virtual Environment.
One server hosting Moodle/IIS and a second hosting SQL DB.

DB server is shared with many other DB's.

During this issue, we see hundreds of thousands of queries sent by Moodle, and looking at the query it seems to be stepping through the student ID's. This maxes out the CPU and takes several hours to complete and sometimes causes deadlocks.

We are determining it's users by looking at the various queries in the DB queue, and we can see it's the same or very similar query with only user/student ID being the different field.
Average of ratings: -
In reply to Rachel Martin

Re: Maxing database CPU

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
See whether deactivating Predictions processor (analytics | predictionsprocessor) and similar mechanisms make a difference.
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Maxing database CPU

by Rachel Martin -
Looking into this setting, we are set for process execution only at command line, so that is not being used.
Average of ratings: -
In reply to Rachel Martin

Re: Maxing database CPU

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
> we see hundreds of thousands of queries sent by Moodle, and looking at the query it seems to be stepping through the student ID's.

Can you post some samples?
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Maxing database CPU

by Rachel Martin -
They give me the following as examples.. and say it increments through what seems to be every user. I have looked up a couple and when I looked it was at older accounts that haven't had activity in years. I wasn't sure that this was the same issue as that, but those are the queries they are giving me. Those queries look to be for grades.. but I don't of anything that would step through all users in the database in regard to grades.

UPDATE mdl_grade_grades
SET aggregationstatus = 'novalue',
aggregationweight = 0
WHERE itemid = '10206' AND userid = '516050'

SELECT gi.id, gg.aggregationstatus, gg.aggregationweight FROM mdl_grade_grades gg
JOIN mdl_grade_items gi ON (gg.itemid = gi.id)
WHERE gg.userid = '312344' AND (gi.categoryid = '40' OR gi.id IN ('9977','9975','9976','9974','9390','9589','9991','9992','9993','9657','9656','9057','9056','9054','9055','9456','9040','9039','9038','9041','9591','9592','9058','9188','9943','9994','9990','9459','9457','9458','6299','6300','6213','6214','6215','6216','6217','6230','6231','6232','6233','6247','6248','6249','6250','6263','6264','6265','6266','6267','6280','6281','6282','6283','6297','6298','10193','10194','7985','7987','7988','7986','7853','7852','7851','7854','9720','7991','7989','7984','7859','7857','7850','7855','9036','9045','9043','9652','9593','9595','9654','9050','9052','9979','9452','9454','9588','9586','10206','10204','7680','7682','7675','7673','6237','6210','6212','6219','6221','6226','6228','6235','6244','6246','6251','6253','6260','6262','6269','6271','6276','6278','6285','6287','6294','6296','6301','6528','6544','6546','9936','9445','9461','9650','9649','9651','9046','9047','9447','9448','9449','9450','10168','10016','9716','9048','9049','7677','7676','7679','6272','6273','6274','6275','6289','6290','6291','6292','6539','6540','6541','6542','6208','6222','6223','6224','6225','6238','6239','6240','6241','6242','6255','6256','6257','6258','6692','7995','7860','7678','7994','7993','9584','7992','7990','7858','7856','7849','7683','7681','9653','9655','9051','9053','9944','9212','9037','9035','9042','9044','9978','9451','9453','9455','9594','9596','9973','9446','9721','9585','9587','9719','9460','10205','10203','7674','6209','6211','6218','6220','6227','6229','6234','6236','6243','6245','6252','6254','6259','6261','6268','6270','6277','6279','6284','6286','6288','6293','6295','6543','6545','6547','9989'))
Average of ratings: -
In reply to Rachel Martin

Re: Maxing database CPU

by Ken Task -
Picture of Particularly helpful Moodlers

Think the default for most things in moodle are to keep all ...

What is your entities records retention policy?   In Tx public schools (k-12) think it's 5 years.  So a student that graduates or leaves the district/school, the district has to retain any/all records for a period of 5 years - after which, any/all records for that students can be removed/erased.

I see: userid = '516050' in the query ... user rows in mdl_user table are added ... none are every 'refilled' with new user info ... if user id 516050 no longer attends/takes classes etc, when row ID 516050 (ie, that student) is removed from mdl_users table, then nothing uses that ID number in the future ...

Now, if you're entity is a college or uni, student information system (SiS) enters the mix ... is your mdl_user table actually populated by an SiS? 

Just out of curiosity, how many rows are in your mdl_user table?

'SoS', Ken

Average of ratings: -
In reply to Ken Task

Re: Maxing database CPU

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
> I see: userid = '516050' in the query ...

So over half a million users, present and past. Not an everyday site!

To the OP's problem, somebody from the grade book sub-system might recognize through what process this task goes through.
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Maxing database CPU

by Ken Task -
Picture of Particularly helpful Moodlers

OP's reported issue is DB ... if one were to also consider what is normally the largest table ... mdl_logstore_standard_log ... and the shared query id'd as heavy processing ... I wonder how large the DB is and in connection with tables known to be large/grow larger by the day, could be DB server is working properly, but just struggling to keep up with cron/task list.

Have noticed with any site I have ever admin'd that keep all/forever eventually creates problems ... and to limit, I had to set things to keep for not an entire academic year, but for a shorter time frame.  Performance of DB always ... always ... improved.

Of course before doing such things would check with entities Moodle Admin user and ask ... most of the time, that person would say they never had the time nor inclination to search reports looking for needles in the haystack.  No need to keep 10 years worth of data ... which, BTW, wasn't really there anyway!   (at least nothing useful).

Above of course is just one admins experience ... doesn't apply to all entities/moodle sites.

'SoS', Ken

Average of ratings: -
In reply to Ken Task

Re: Maxing database CPU

by Rachel Martin -
I do think that our DB resources could probably be expanded. We have, of course, had a recent jump in online courses and usage with the COVID situation.

We are on either a 5-7 year retention and I can likely push to cleanup more courses. The LMS is not the not our final records because transcript grades are recorded elsewhere, but we work with different groups and some are more attached to things than others for statistical purposes. Our setup is unique though as we are not semester based. There isn't a nice clear break point on when courses were launched and it varies from course to course.

Going back through and doing a cleanup on old courses again is on the to-do list. My team is small though and we are busier than ever right now.

I appreciate all the help and insights.
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Maxing database CPU

by Rachel Martin -
We have a large quantity of user accounts. Many are old though and have suspended enrollments. And some have never been logged in since we have had issues with students duplicating accounts and signing up for free courses to never take them. I have been wanting to start bulk cleanup but I have met some resistance. Also, I need to go back through and figure out how to add timecreated to the user filters for bulk deletion. Accounts are updated or created when enrollments are sent to the LMS. We have a portal that handles everything else. So I can't just search for never been accessed or modified. A recent enrollment where I student has not yet logged in would meet that criteria.
Average of ratings: -
In reply to Rachel Martin

Re: Maxing database CPU

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
I don't think this is an ad-hoc task, just in case, try setting Site administration > Server > Tasks > Task processing: Ad hoc task concurrency limit (task_adhoc_concurrency_limit) to 0 (Default: 3).
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Maxing database CPU

by Rachel Martin -
I do not see that specific menu selection in our current version of Moodle. I also cannot find that under scheduled tasks. We are on Moodle 3.5, since we stick primarily to the LTS versions. We will be looking at upgrading to 3.9 in the near future.

But, I also do not believe this is related to an ad-hoc task.
Average of ratings: -
In reply to Rachel Martin

Re: Maxing database CPU

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
In case nobody has mentioned, Site administration > Advanced features: Enable outcomes (enableoutcomes)? "If enabled, grade items may be graded using one or more scales tied to outcome statements."
Average of ratings: -
In reply to Visvanath Ratnaweera

Re: Maxing database CPU

by Rachel Martin -
That is enabled, though I can't say 100% that no course has used that. I think I had one group play with that setting in the past, so I don't know if I can disable it.

I have wondered though if some past update affected the gradebook. After hunting through code, I found some old items in the database flagged for 'needsupdate' when they were linked to retired/old courses. After some testing on our sandbox system, I have gone ahead and toggled all of those items back to 0. Perhaps something was going back through gradebooks or someone was attempting to review some old course data and kicking off a task.

I have also disabled some statistics and other tasks. I'm hoping that something might have worked and we won't see this again.
Average of ratings: -
In reply to Rachel Martin

Re: Maxing database CPU

by Ken Task -
Picture of Particularly helpful Moodlers

VMware virtual Environment - DB server is shared with many other DB's

VMWare server admins are notorious for running too many DB's on one VM Server.   It's the total ... so one DB for Moodle but what are other DB's and how are they behaving?

DB for a moodle runs best on a separate server when 80% of it can be put into memory ... no disk IO.  Best I've ever been able to get is 60%, however.

The queries could be notifications to students - messages - forum postings, etc.  Check prefs for notifications.  User ID would be 2 (admin user) and only student/teacher ID's would change.

'SoS', Ken

Average of ratings: -