Unusual constant database load & strange queries

Unusual constant database load & strange queries

by Nick Mika -
Number of replies: 4

Hi all,

We run an automatically scaling Moodle setup. We noticed recently that each instance running was generating a very large amount of CPU load on the database - even when the given instance was removed from the load balancer.
I used wireshark to compare the queries sent to the database from that instance to the queries sent from an instance without this issue. I noticed a disproportionately large (500 in 2 minutes) amount of queries of the structure:

SELECT id FROM mdl_user WHERE deleted = '0' AND mnethostid = '1' AND username = '~md5 hash~' LIMIT 0, 1
Mostly with MD5 hashes, some with usernames. Most of the queries return nothing, and our usernames aren't stored as md5 hashes in the db.

1. Is it likely that this is the cause for the excess CPU load?

2. Where are these queries coming from and why?

Searching the codebase, a few possible sources of these queries are:
user/externallib.php
function update_users($users)
Line 556

cohort/externallib.php
function add_cohort_members($members)
Line 594
function delete_cohort_members($members)
Line 689

group/externallib.php
function add_group_members($members)
Line 466
function delete_group_members($members)
Line 544

Regards,
Nick

Average of ratings: -
In reply to Nick Mika

Re: Unusual constant database load & strange queries

by sam marshall -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers
So your load balancer setup is the type where it would be impossible for anyone to connect directly to the instances, because they have to go through the front-end server, and the instance was removed from it?

In that case just a thought - are you running scheduled tasks (cron) on all of the instances? Those would still potentially run even if removed from the load balancer. If you are running cron, hopefully you put the logs somewhere, so you can check those to see if any task is running / going crazy.

--sam
Average of ratings: Useful (1)
In reply to sam marshall

Re: Unusual constant database load & strange queries

by Nick Mika -

Thanks Sam,

You're right - I've narrowed the source down to the enrol/database/cli/sync.php file which is being run by cron.

Average of ratings: -
In reply to Nick Mika

Re: Unusual constant database load & strange queries

by Andrew Lyons -
Picture of Core developers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Testers

Hi Niick,

That's the cause of your issue, but it also suggests a potentially dangerous mis-configuration.

That particular sync is designed to run as a single thread, and not concurrently in any way - e.g. simultaneously across multiple nodes in a cluster.

You mention that you're seeing a high load on all nodes in your cluster, even when removed from the pool and I'd guess that you're therefore calling this particular cron script from all nodes.

We actually replaced this script with a locking scheduled task in Moodle 3.3 (MDL-57913) and I'd highly recommend using this in preference to the old script which will be going away in Moodle 3.6.

To switch to the new task you need to enable the job in Admin -> Server -> Scheduled Tasks. This will ensure that the job is only run periodically, and is not run concurrently. I'd also recommend removing the call to the old sync script from your crontab.

Generally I would  recommend that you consider running a dedicated cron node rather than running cron on all of your web nodes. Doing this means that system resources are not 'stolen' by the cron jobs in this manner - which would theoretically lead to a better experience for users. You can also choose how you scale your cron processing nodes independently of how you scale your web nodes which could well lead to cheaper hosting costs as you should no longer be scaling as much with the underlying cron load.

Hope this helps,

Andrew

Average of ratings: Useful (2)
In reply to Andrew Lyons

Re: Unusual constant database load & strange queries

by Nick Mika -

Thanks Andrew,

Yes, normally our cron jobs only run on the original instance but our configuration script seems to be misbehaving.
Thanks for the suggestion with the scheduled tasks.

Nick

Average of ratings: -