MySQL CPU - Moodle

MySQL CPU - Moodle

by A J -
Number of replies: 14
Hello!


We're having some CPU consumption issues with our Moodle server running on Ubuntu.  Specifically the mysqld process.  After running mysqladmin -i1 processlist it reveals the following queries:


SELECT * FROM mdl_assign_submission WHERE assignment = '#' AND userid = '#' AND groupid = '0' 

SELECT * FROM mdl_assign_grades WHERE assignment = '#' AND userid = '#' AND attemptnumber = '0'


Assuming this is a check for assignment submissions, but I'm wondering if this can be tweaked to reduce load on the system during peak hours?

Moodle 3.1.2+ (Build: 20160923)

Thanks in advance!

Average of ratings: -
In reply to A J

Re: MySQL CPU - Moodle

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

What makes you think those queries are causing system load issues?

To take a big step back - what problem are you trying to fix?

Average of ratings: -
In reply to Howard Miller

Re: MySQL CPU - Moodle

by A J -

Those seem to be the only queries running at the time of load as far as I can tell.

I'm trying to fix the CPU spikes that are occurring on the Moodle server to ensure user experience is not affected.  According to my monitoring software I can note specific times of the CPU getting hit.  Trying to ensure the server is running as efficiently as possible.  I've attached the CPU graph of our Moodle CPU utilization since 12:15pm today.

Attachment Moodle CPU.PNG
Average of ratings: -
In reply to A J

Re: MySQL CPU - Moodle

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Unless there are things you have not mentioned, I think it's a bit of a stretch to assume this is a database issue. If your site is "old" and has been through a number of upgrades it is possible that you have missing indexes that are causing slow queries but it wouldn't be at the top of my list. It won't hurt to do a schema check to eliminate that though.

Other than that, when the load starts to go "off the chart" it's difficult to say if the database caused this or is due to this. You need to monitor and check a lot more parameters on your server. 

It might also help to give us some more information about your setup and the specification of your server(s). Also, what sort of activity and number of users are we talking about. It's much more likely that your servers are under-specified and/or the database and web server are not properly tuned (or have never been tuned at all).

Average of ratings: -
In reply to Howard Miller

Re: MySQL CPU - Moodle

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

To follow up on the Missing indexes line of thought, you can go to Admin -> Development -> XMLDB, and click some of the [Check ...] links at the top. That will verify some things.

Average of ratings: Useful (2)
In reply to Tim Hunt

Re: MySQL CPU - Moodle

by A J -

Thanks for the suggestion!  I will give this a try.  Any impact to running these checks?

Average of ratings: -
In reply to Howard Miller

Re: MySQL CPU - Moodle

by A J -

My apologies I should have been more specific.  

Viewing the top results on the server reveals the mysqld process using the CPU cycles the most.  Which is why I'm looking at the DB queries at the time of load.  

Yes the site is old.  Thanks for the schema check suggestion, I'll look into that.  I've only recently taken on more of a role with the Moodle support, I'm primarily an infrastructure guy so pardon my ignorance.

The server is a VMware virtual machine.  Running 2x vCPU, 8GB RAM, Ubuntu 16.04.1 LTS.  The disk underlying is a hybrid array of SSD/SAS running over NFS 10 Gbps.  Number of users varies at any given time, but I'd say no more than 200 at once but the server is accessed by maybe 1000+ overall.

The my.cnf file has been tweaked a bit prior.  I've attached a screenshot of the file output.

Attachment mysqlconf.PNG
Average of ratings: -
In reply to A J

Re: MySQL CPU - Moodle

by Ken Task -
Picture of Particularly helpful Moodlers

Screen shot helps.

In addtition to what others have said/suggested ...

One thing I see in cnf that 'stands out' ... max_connections = 150 is 1 below the default if not set in cnf file.   "200 at once" could push the max connections (depending upon what users doing) beyond that limit.   Error logs of apache might show 'server has gone away'.  Users wouldn't see that but not might see anything causing user to click again ... repeatedly?

Would also install and run MySQLTuner http://mysqltuner.com/

The config file is pretty sparse.  No InnoDB?

In the graph ... seems around midnight is when it spikes.   Have no idea about the users habits in accessing, but that sorta looks like a cron job or task or something related to DB that kicks in and in combination with the traffic ... leads to what you see.

Running automated backups?

'spirit of sharing', Ken


Average of ratings: Useful (1)
In reply to A J

Re: MySQL CPU - Moodle

by Chris Fryer -

You need to set innodb_buffer_pool_size. By default it's 128MB

https://dev.mysql.com/doc/refman/5.6/en/innodb-buffer-pool.html

I'd be interested to see how long your CPU is spending in io wait. Am I right in understanding the database files are stored on an NFS volume? If so, I'd move them to local storage.

Average of ratings: Useful (2)
In reply to Chris Fryer

Re: MySQL CPU - Moodle

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Just to add, NFS is not your friend with Moodle. If your 'moodledata' files area is on NFS and your caching is default then you will probably see very poor page load times due to unacceptable cache performance. Make sure critical stuff is on 'fast' (preferably SSD these days) local disk. 


Check these additional settings and apply them to your config.php file if it makes sense...

https://github.com/moodle/moodle/blob/MOODLE_32_STABLE/config-dist.php#L400

Average of ratings: -
In reply to Chris Fryer

Re: MySQL CPU - Moodle

by A J -

Sorry I should have been more clear.  

The VM is running off of a NFS datastore inside VMware.  So all disks are technically running over NFS even though they're local to the VM.  We have a primary NAS unit and it is sitting on SSD fronted SAS shelf (NetApp storage).  I get sub 1 ms latency to disk and the Moodle IO isn't actually all that crazy for the amount of spindles/SSD's it's sitting on.

You guys have all been crazy helpful.  I'm noting these suggestions and doing some research.  I'll look to apply these 1 at a time to see if it has any improvement on performance.  I'll report back here as soon as I've done so with the results!

Average of ratings: -
In reply to A J

Re: MySQL CPU - Moodle

by A J -

Alright progress!  Here's what I've done.

Disabled the 15min increment scheduled tasks within Moodle one by one to find the culprit.  Seems the NED Marking Manager plugin is the culprit for the 15min spikes.  The larger spikes there's a cron script to sync user accounts with another system.  I'm going to look to provision a script host VM to offload the CPU cycles to a different VM/host for this script.

Thanks to your suggestions I've added the InnoDB buffer to 1GB as a starting point and am going to monitor.  Not sure why this was missing from the config file.  I also increased max connections to 200 though I don't see any "exceed" errors in the log, it's just to be safe.  I've also run the DB checks as recommended and there is only 1 missing index, 32 FK violations, and 9 Default Value check errors.  I've sent this up the line for review.

I believe we can close this thread out.  Thanks all for suggestions and your help!

Average of ratings: Useful (2)
In reply to A J

Re: MySQL CPU - Moodle

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Thanks for the update.

I'm not familiar with that plugin, but you might want to link the devs to this thread just in case there's something wrong in that plugin. 

Average of ratings: -