Moodle in English: Managing scheduled tasks & php load on server

Managing scheduled tasks & php load on server

by Eric Phetteplace - Saturday, January 23, 2021, 6:39 AM

As the start of our semester nears, we're seeing repeated problems with Moodle's server crashing that appear to be related to scheduled tasks / the admin/cli/cron.php script. We're on Moodle 3.8. I cannot 100% tell what tasks are causing the problem but I'm starting to look through logs and identify ones that take a long time to complete. We have a cron job on the server that runs the cron script every minute (this is recommended practice, no?) but the tasks will take over a minute to complete, causing some weird problems in the logs when the initial "server time" message is recorded but nothing else:

Cron completed at 02:02:07. Memory used 32MB.

Execution took 5.644233 seconds

Server Time: Fri, 22 Jan 2021 02:03:01 -0800

Server Time: Fri, 22 Jan 2021 02:04:01 -0800

Server Time: Fri, 22 Jan 2021 02:05:01 -0800

Skipping processing of scheduled tasks. Concurrency limit reached.

Cron script completed correctly

Cron completed at 02:05:33. Memory used 13.6MB.

Execution took 91.814146

That's from a 2am crash, when traffic on the server is low and we're not running our most intensive tasks either (we run a user synchronization task later in the night). You can see the 2:03-2:04 cron runs have nothing in the logs. Does this mean that the all the scheduled task runners were busy that whole time? I can't figure out what is running out of control but on a hunch I disabled the assignment annotation using ghostscript and the document conversion using unoconv, so calling these external binaries shouldn't overload the server. But I'm not convinced that's solved all the problems. What's odd is we didn't see these problems as often last semester but nothing substantial has changed in between (same server, same Moodle version, no new plugins, etc.).

Assuming we cannot provision any more resources for the server, what are the ways to prevent repeat runs of the cron script from ballooning out of control? When I look at /admin/settings.php?section=taskprocessing it makes it sound like increasing the concurrency limits (we use all the defaults on this page) would make things worse. Would decreasing the concurrency limits to 2 perhaps improve things? Would using something like cpulimit on the php program help or make things worse? I'm really running out of ideas.

Average of ratings: -

Re: Managing scheduled tasks & php load on server

by Howard Miller - Saturday, January 23, 2021, 4:38 PM

What monitoring have you got on your servers? Do you have any idea what is going on when the server fails? High database connections? High system load and so on?

If the answer is "nothing" then "munin" is for free (you don't say what operating system you are running) and simple to set up

Average of ratings: -

Re: Managing scheduled tasks & php load on server

by Eric Phetteplace - Sunday, January 24, 2021, 5:25 AM

Thanks for the response. We have zabbix monitoring internally and Uptime Robot for external pings, but neither really shows what's happening when a crash goes on. I'll look into munin. We're on Ubuntu server.

I've been on the servers while crashes are happening though and it's PHP processes running the cron script that are out of control. They monopolize CPU so badly that eventually Apache processes are blocked and the website become nonfunctional. Previously, I also noticed that ghostscript (gs) was consuming a ton of CPU too but I simply disabled that (deleted the "pathtogs" system setting and the annotate feedback feature, I forget what exactly the setting is) so that shouldn't be a problem going forward. But obviously we'd prefer to be able to use these features, it's just bad that they run out of control & crash the website.

Average of ratings: -

Re: Managing scheduled tasks & php load on server

by Visvanath Ratnaweera - Sunday, January 24, 2021, 6:15 AM

Do your teachers and students communicate through "annotated" PDFs or other Office documents, for example in Assignment activities? The internal conversions are known performance killers. The cron tackles them in batches and can get stuck.

Average of ratings: -

Re: Managing scheduled tasks & php load on server

by Eric Phetteplace - Monday, January 25, 2021, 6:05 AM

Yes, they do, and as I said I disabled those features so it shouldn't be the problem anymore. But it seems like we're still seeing slowness even without the ghostscript processes.

Average of ratings: -

Re: Managing scheduled tasks & php load on server

by Ken Task - Sunday, January 24, 2021, 7:10 AM

Suggest sharing info about your entire setup as an .edu is normally larger than the 'average bear' and does require some different approaches. Example: load balanced setup with 5 nodes ... node 1 which is not used by students/teachers on a daily basis but is used as staging for updates/upgrades and is used to run cron (or specific task) as well as automated backups.

My 2 cents.

'SoS', Ken

Average of ratings:Useful (1)

Re: Managing scheduled tasks & php load on server

by Eric Phetteplace - Monday, January 25, 2021, 6:06 AM

We have just one production server that does everything. It's possible to run cron on a separate server? I kind of doubt we can move to a clustered setup during the semester but this gives us something to aim for at least. We don't run automated backups with Moodle at all. We have snapshots of the server, data directory, and database made outside of the software.

Average of ratings: -

Re: Managing scheduled tasks & php load on server

by Ken Task - Monday, January 25, 2021, 10:59 AM

All in one server ... balancing act between web service and DB server. Your present issue cron and ad-hoc task/task

One hint: Skipping processing of scheduled tasks. Concurrency limit reached.

Question you asked ...

concurrency limits (we use all the defaults on this page) would make things worse. Would decreasing the concurrency limits to 2 perhaps improve things?

Would think opposite ... increasing rather than decreasing ... but that then goes back what that setting is now and .... and ... to server specs.

Suggestions by those responding are good ... and probably should be done.

You've already disabled some 'heavy hitters' (processing) ... I take it your teachers have found an acceptable alternative to each or both?

Prediction: when your sites policy of access to courses by students for 2 years after completing the course and you decide it's time for a clean up of courses (deletions)
you will be visiting the issue of cron/adhoc task/task list again ... if still using current setup ... errrr ... am reminded of a saying ... 'can't get blood out of a turnip'.

Symptom ...

Course cannot be deleted ... shows 'deletion in progress' ... doesn't ever seem to complete and is jamming your server up. Cause ... a module in a course requires some heavy processing ... solution: command line running of a cli script with 'nohup'.

By then, another question will popup ... recyclebin ... but that's another future posting.

In all in one server, am venturing an educated guess (given info so far) it's underpowered.

Since you are preparing to begin another semester, think I'd try to get things resolved ... even if it means breaking apart services ... web on one and dedicated DB server - that's easily doable and not as complicated as load balanced etc.

But ... that's my 2 cents!

Best of luck!

'SoS', Ken

Average of ratings: -

Re: Managing scheduled tasks & php load on server

by Andrew Lyons - Monday, January 25, 2021, 12:02 PM

Hi Ken,

> One hint: Skipping processing of scheduled tasks. Concurrency limit reached.

This does not mean anything on it's own. This is a feature I built in to prevent fork-bombing the box. Sometimes you will see this and it's quite natural to do so because some tasks take longer than others.

On its own it is _not_ a sign of a problem.

> Would think opposite ... increasing rather than decreasing ... but that then goes back what that setting is now and .... and ... to server specs.

It really does depend on the nature of the load, and the location and cause of the bottleneck. It's a balance. If you increase the concurrency you make more things happen at once, which puts the server under more load. If you decrease the concurrency things take longer to run and you start to run cron during business hours when real users are doing things and you negatively affect performance that way.

> In all in one server, am venturing an educated guess (given info so far) it's underpowered.

That's pure conjecture.You just don't know. Prematurely splitting the infrastructure can have a detremental effect on performance too - it depends on the way in which it is split, the quality of the network, latency, etc. We need to _stop_ giving the advice to blindly separate things out into multiple servers before the problem is understood. Having a DB server on the same node can be extremely fast because sockets are utilised (if the server has appropriate capacity), whereas a poor quality network with high latency could lead to a performance degredation. It's all part of a bigger picture.

Andrew

Average of ratings:Useful (2)

Re: Managing scheduled tasks & php load on server

by Ken Task - Monday, January 25, 2021, 12:57 PM

"pure conjecture" ... it sure is! No doubt about it. And, even though you've given a more in-depth technical explanation, you're really saying the same thing.

"We need to _stop_ giving the advice to blindly" - wasn't given 'blindly' ... haven't seen any specs yet, have you? More an attempt to encourage sharing that info .... in the 'hardware and performance' forum one would expect to see such info.

Again ... my 2 cents!

'SoS', Ken

Average of ratings: -

Re: Managing scheduled tasks & php load on server

by Andrew Lyons - Monday, January 25, 2021, 12:10 PM

Hi Eric,

> It's possible to run cron on a separate server?

Yes, it is possible and is often beneficial where you have a load characteristic which has heavy reliance upon cron. For example if you make heavy use of automated backups, PDF annotation, or have extremely active forums.

> We don't run automated backups with Moodle at all. We have snapshots of the server, data directory, and database made outside of the software.

Just as a hunch (and historical experiences), what time do your snapshotting procedures occur, and how do they run?

I've seen times in the past where system processes like backups, snapshots, scheduled software updates, virus scans, and even log rotation (because of zipping logs as they rotate) can cause massive load spikes which affect the application. Some things like database backups can even lock tables during the backup, which just about kills use of the application.

As your usage increases (Thanks C-word) you're more likely to see some of these things because there's more to backup or snapshot, the database is larger and is suddenly kicked out of memory by other processes, there are more logs so gzipping them takes longer, etc.

Just something to consider...

Andrew

Average of ratings:Useful (2)

Re: Managing scheduled tasks & php load on server

by Eric Phetteplace - Tuesday, January 26, 2021, 2:49 AM

We should look into running cron on a separate server but I don't think any of the issues brought up here (PDF annotation, automated backups) are the real problem because I've disabled them all and yet issues continue. We've never used automated backups. The crashes don't happen during the times when server snapshots are running. It definitely has to do with the cron script running out of control and not those. We do encourage a lot of forums in courses but I don't know if they'd be considered heavy use or not.

My newest theory is the one major change we made before this semester — we switched disk drives because we needed more space. Unfortunately, it seems like the new drive might be slower, thus all these problems. Perhaps the problem surfaces during cron because of various bulk file operations (cleaning up old files, the tasks that I've disabled like PDF annotation).

Average of ratings: -

Re: Managing scheduled tasks & php load on server

by Howard Miller - Tuesday, January 26, 2021, 4:57 PM

Running cron on a separate server effectively means that you are going down the road of a 'clustered' Moodle setup. The only difference being that the cron front-end is not publicly accessible.

This comes at the cost of a lot of extra complication and a bunch of other things that can go wrong.

However, sometimes you find yourself out of ideas and just have to do something.

Average of ratings:Useful (1)

Re: Managing scheduled tasks & php load on server

by Alex Rowe - Monday, January 25, 2021, 6:18 AM

Try to log each cron run for a day or so to see which scheduled tasks (or ad hoc tasks) are causing cron to take so long

Something like this saved and called from a crontab

#!/bin/sh
date=`date +%Y%m%d-%H:%M:%S`
sudo -u www-data /usr/bin/php -f [PATH TO YOUR MOODLE]/admin/cli/cron.php > [PATH TO LOG SUCH AS /VAR/LOG/MOODLECRON/]/${date}-cron.txt 2>&1

Ideally it should be all done in 20 seconds or less, depending on the size of your site. When you see a file that is larger or took longer, investigate the file to see which specific task caused it.

We had to do this last year and found Turnitin (plagiarism plugin) and Customcert (activity module) were doing tens to hundreds of thousands of DB calls each time.

Average of ratings:Useful (4)

Re: Managing scheduled tasks & php load on server

by Eric Phetteplace - Tuesday, January 26, 2021, 2:50 AM

Thanks, we do this already but it occurs to me we're only redirecting stdout and not stderr. Maybe I'm missing some information so I'll redirect both to the log files.

Average of ratings: -

Re: Managing scheduled tasks & php load on server

by Andrew Lyons - Monday, January 25, 2021, 11:57 AM

Hi Eric,

You can use the Task Logs in the Moodle User Interface to see what cron tasks recently ran, how long they took, and some additional metrics like the number of database queries that they required to run.

Generally speaking there are two types of cron task - scheduled tasks, and adhoc tasks. Scheduled tasks take place at regular times, whilst Adhoc tasks take place as required and can be kicked off arbitrarily. For example a cousre backup will be run as an adhoc task and is queued as soon as a course is deleted or a backup requested. The next task processor to come along will pick the job up regardless of schedule. It will be useful to find out exactly which tasks are happening and queueing up.

In terms of concurrency it's a balance you have to find. We set it to an initially low value to prevent hammering the server - especailly useful where the same server does everything, but less useful where you have a dedicated cron server. You may find it useful to decrease the concurrency to two, but it really depends on what resources are constrained. It is premature to limit CPU - especially given you aren't aware which resources are constrained or why they are.

In addition to the standard admin/cli/cron.php script there are scripts for running specific types of task - admin/cli/scheduled_task.php and admin/cli/adhoc_task.php (these may be in admin/tool/task/cli/. They were moved in a recent version but I forget which). These may be helpful in troubleshooting.

Really the first thing you need to do is to find out what task is running that is eating your resources, and determine which resources are starved. Until you determine that you can't do any more sensibly.

I would avoid calls to scale until you have this information clearly identified and you understand the cause in more detail.

As Alex suggests, you can easily have a separate per-run log to see what task is running when things lock up. This information is also typically in the Task Logs in Site administration -> Server -> Task logs, but usually it is not filled until the task completes.

Best wishes,

Andrew

Average of ratings:Useful (3)

Re: Managing scheduled tasks & php load on server

by Andrew Lyons - Monday, January 25, 2021, 12:19 PM

> As the start of our semester nears, we're seeing repeated problems with Moodle's server crashing

Has there been a recent change in the usage? Your semester is not in session yet, but presumably teachers are preparing for it?

> that appear to be related to scheduled tasks / the admin/cli/cron.php script.

This _could_ be a symptom rather than a cause. Just keep your mind open to that idea.

> We're on Moodle 3.8.

Just to remind you that 3.8 went out of support for bug fixes in November, and security support will be removed on the 10th May. You may want to factor this in to your planning.

> I cannot 100% tell what tasks are causing the problem but I'm starting to look through logs and identify ones that take a long time to complete.

> We have a cron job on the server that runs the cron script every minute (this is recommended practice, no?)

Yes - that is the recommended practice.

> but the tasks will take over a minute to complete, causing some weird problems in the logs when the initial "server time" message is recorded but nothing else:

Many tasks can take over a minute to complete and that's quite normal. Certain jobs are inherently slow because of the nature of their workload. To give an example the backup tasks can be particularly slow, especially on courses with a large number of questions in the question bank. Similarly if you are using PDF conversion and use the built-in unoconv feature, that can have a huge performance impact because Open Office is a big piece of complex software. In that instance I'd strongly recommend you look at one of the other conversion tools (i.e. Google Drive, Office 365, or one of the paid options available).
The message isn't a weird problem in itself. It's just an informative message to let you know that it is rate limiting the jobs to prevent a fork-bomb (where a large number of processes are spawned concurrently leading them to fight one another for resources and degrading performance). This is built in to _prevent_ problems of this nature.

Average of ratings:Useful (3)

Re: Managing scheduled tasks & php load on server

by Eric Phetteplace - Tuesday, January 26, 2021, 3:00 AM

Thanks for these answers, it's helpful to know that some of these things aren't out of the ordinary. I've already disabled the PDF annotation and unoconv conversion because those were easier to spot as major resource consumers, but we're still seeing issues so I'm not sure what else to do. We don't do automated backups.

I have been looking at the scheduled task logs the ones that take a really long time tend to be of two types: our user enrollment sync (happens once in the middle of the night) and ad hoc backups. I'm not sure we can get by without backups but does it make sense to simply disallow them until our problem is sorted out? I worry that that breaks a lot of functionality. For instance, do imports work without backups? And I know our template plugin (local_course_template) relies on them.

Would running the cronjob less frequently help? My tendency is to think it wouldn't (the same amount of work needs to be done, it's just initiated less often now).

I noted this elsewhere but just adding here as well: I'm beginning to suspect that our new data drive, the only major change this semester (we didn't have these problems last semester), is slower and the source of the problem.

Average of ratings: -

Re: Managing scheduled tasks & php load on server

by Howard Miller - Tuesday, January 26, 2021, 4:52 PM

Are you saying that you have an external (NFS?) data drive for 'moodledata'. If you do... I hope you have done something about caching (e.g. Redis). Moodle is *very* sensitive to cache performance and the default - caching to a folder in moodledata - rarely cuts it on a shared drive. Having said that, the first thing you notice is usually extended page load times.

While you're there, you might also think about session storage - for the same reasons.

Average of ratings:Useful (1)

Re: Managing scheduled tasks & php load on server

by Eric Phetteplace - Wednesday, January 27, 2021, 12:54 AM

Hmm OK we did start to explore using Redis for caching but I believe Moodle's own internal testing (under Administration / Plugins / Caching) didn't show a significant improvement so we decided against it. I'll bring this up again. Thanks!

Average of ratings: -

Re: Managing scheduled tasks & php load on server

by Howard Miller - Wednesday, January 27, 2021, 1:38 AM

That's unlikely... your Redis server must have been broken

They generate quite a lot of network traffic, so make sure everything is plugged into a properly fast switch.

Average of ratings: -

Re: Managing scheduled tasks & php load on server

by Ken Task - Wednesday, January 27, 2021, 12:18 AM

Well, here's another 'wild' suggestion ... since you began this discussion with not being able to allocate more resources to the all-in-one server ...

Install and run MySQLTuner.pl with superuser credentials for DB server.

Output of that has a section which might shed some light:

Example from an 8 Gig total memory all in one box:

[--] Physical Memory : 7.6G
[--] Max MySQL memory : 2.2G
[--] Other process memory: 279.4M

[OK] Maximum reached memory usage: 2.0G (26.86% of installed RAM)
[OK] Maximum possible memory usage: 2.2G (28.90% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available

Also install and run apache2buddy.pl .... which has similar out put.

On same server, apache2buddy has a section:

    Max potential memory usage:                                   9331 MB
   Percentage of TOTAL RAM allocated to Apache:                  119.41 %
   Percentage of REMAINING RAM allocated to Apache:              143.92 %

All in one server has always been a balancing act between DB server and web service.

Cron task/scheduled task if heavy hitting and could not be completed could be re-scheduled via adhoc_task which is supposed to pick up where it left off.

From CLI:

admin/tool/task/cli/

php adhoc_task.php --execute

BTW a version 3.9.x of Moodle has more/better options for the adhoc_task.php script

-e, --execute             Run all queued adhoc tasks
-k, --keep-alive=N        Keep this script alive for N seconds and poll for new adhoc tasks
-i --ignorelimits        Ignore task_adhoc_concurrency_limit and task_adhoc_max_runtime limits

Am venturing a guess that those switches were added due to issues like you are facing.

'SoS', Ken

Average of ratings:Useful (3)

Re: Managing scheduled tasks & php load on server

by Eric Phetteplace - Wednesday, January 27, 2021, 12:58 AM

We're nowhere near able to upgrade Moodle in the middle of a semester. Maybe months down the line but right now is far too busy. It's good to know about the adhoc_task.php CLI though, I hadn't seen that before. I've used the scheduled tasks CLI (that's how we run our user sync only once during the night) but not that.

To be clear: the two scripts you spoke about just analyze system resources to see how much is available for the database and web server? Would we have a problem while they're running, i.e. should I run them during off hours?

Average of ratings: -

Re: Managing scheduled tasks & php load on server

by Ken Task - Wednesday, January 27, 2021, 2:23 AM

Seems you are determined NOT to share some basic hardware info .... although in another posting we finally see some numbers. ;) And quick answer here ... yes, there are some of those task you really shouldn't disable (not run) as that will make the situation worse (been my observation on other system investigations, at least)

sooooooo ...

The .pl scripts mentioned run outside of Moodle ... not php. It's been my experience running them even during prime time usage of Moodle has not knocked Moodle over. But, then again, I know already memory/cpu's etc. info. Those .pl scripts are looking at config files for MySQL or settings in MySQL then comparing what the internal data MySQL is keeping ... has been keeping all along ... ever since you cranked it up. (restarts do clear data/resets).

The example ... emphasize *example* looks like am running a misconfigured server for an 8 Gig server ... not really ... you'd have to know *how* and *how often* moodle(s) are being used.

So I'll ask one more time ... hardware info? At the very least how much memory does this all-in-one box have?

'SoS', Ken

Average of ratings: -

Re: Managing scheduled tasks & php load on server

by Eric Phetteplace - Wednesday, January 27, 2021, 3:43 AM

I'm not "determined not to share basic hardware info" I just don't think that's helpful, because we cannot change our hardware anytime soon, but here you go. It's on a VM managed by our IT so I really don't know that much about it, I'm a software admin. Here's the output of a bunch of commands: https://gist.github.com/phette23/a2e15f2f5627a7a75f3686b5398368e8 If there's something else that'd be helpful to know, let me know.

Also, I may have been misleading, we do have a database server and a data drive. I simply meant we're not doing a clustered setup for the web server. Cron and the web server are all running on the same machine.

Average of ratings: -

Re: Managing scheduled tasks & php load on server

by Eric Phetteplace - Wednesday, January 27, 2021, 4:01 AM

Actually, I do stand corrected—when I looked at the specs it seemed like more than I remember and, indeed, IT added more memory and CPUs. I think we were at 8gb RAM / 8 CPUs but now it's 12 / 12. That's really helped. I'd still like to know about ways to manage system load within Moodle, since I cannot perpetually request more resources and it usually takes time for those to arrive anyways (or requires a restart, disrupting us during the middle of the day).

Average of ratings: -

Re: Managing scheduled tasks & php load on server

by Ken Task - Wednesday, January 27, 2021, 5:10 AM

12/12 is better than 8/8 ... but 24/24 is even better ... and 32/X *might* be the sweet spot for your site.

I don't know that you actually can 'better manage' without short-changing performance of moodle server ... too many factors you can't really control. And since you say 'takes time ... requires restart ... disruption' am also assuming your VMWare admins don't like it either.

Do know that Moodle does require frequent monitoring/checking and it's best to be pro-active (if you can) rather then re-active.

'SoS', Ken

Average of ratings: -

Re: Managing scheduled tasks & php load on server

by Ken Task - Wednesday, January 27, 2021, 4:44 AM

Well, we've gone 'hard core' with lshw. But now known is your setup is 'split' ... under VMWare, dedicated DB server, one Web server.

I'll stick to mysqltuner for now ... running mysqltuner really should be done on both the dedicated DB server and from the web/moodle code server. Why? Networking is involved.

On the DB server no extra parameters to tuner. Running from Web server extra parameters required - but using same super user credentials. That's if your DB server will allow super user access from Web server.

As far as memory ... what does the 'top' of the 'top' command look like on web server ... and on dedicated DB server?

Will show something like ... but not exactly:

KiB Mem : 8003332 total, 572824 free, 2055048 used, 5375460 buff/cache
KiB Swap: 0 total, 0 free, 0 used. 5118576 avail Mem

But due note: no SWAP space is being used on the all-in-one box am using for example.

Also, VMWare ... is your Moodle the only 'guest OS' on the VMWare server or are there multiple guest OS's? If multiple, have those other Guest OS's been given any higher priority over what your Moodle is doing?

Am familiar with mis-configured or allocated VMWare setup ... not the VMWare end, but the affects upon a Moodle. Quick story ... 'customer' reported couldn't access moodle server for 5 minutes every hour @ exactly 37 minutes after the hour. 'Customer' kept insisting the moodle server was mis-configured ... specifically mentioned cron job. Turns out, another Guest OS on the VMWare box had been given/granted to use all resources to do whatever it was doing ... thus taking away from the Moodle Guest OS ... and for a period of 5 minutes. Took me 2 months to convince them to look else where to resolve the issue. That was years ago when VMWare was fairly new.

Anyhoo ... top on both web and DB server please.

And Tuner from Web server + from DB server please.

'SoS', Ken

Average of ratings: -

Re: Managing scheduled tasks & php load on server

by Eric Phetteplace - Thursday, January 28, 2021, 12:54 AM

The Moodle VM isn't shared with anything, I'm pretty sure. This is top on the web server:

KiB Mem : 12295372 total, 1024664 free, 609084 used, 10661624 buff/cache
KiB Swap: 2018300 total, 2015728 free, 2572 used. 11294200 avail Mem

I don't have SSH access to the db server but I can ask about it. When I run mysqltuner.pl on the web server with the db connection info, it says "[!!] The --forcemem option is required for remote connections". That's the RAM on the db server not the web one I assume? I don't know what that is so I'll have to wait on IT.

Average of ratings: -

Re: Managing scheduled tasks & php load on server

by Ken Task - Thursday, January 28, 2021, 1:43 AM

Top output ... more important for DB server than Web server when it comes to SWAP. For DB server SWAP is not good - would guess the DB server to be dedicated to just DB with no other web service running anything PHP. Not sure why the extra memory requirement if running mysqltuner vs a dedicated db server but as you have discovered is required. Can confirm it is needed. Even if the xtra memory is taken from the client on the web service, it's not very long in duration and I've never had the web service 'crash' as a result of running mysqltuner from web service to dedicated DB server. But good idea to get IT involved.

Tuner will have different recommendations from web server vs tuner on DB server. MariaDB/MySQLDB networking config of DB server is involved.

Anyhoo ... good idea to get IT involved.

And thanks for sharing info on that chart.

'SoS', Ken

Average of ratings: -

Re: Managing scheduled tasks & php load on server

by Eric Phetteplace - Thursday, January 28, 2021, 8:31 AM

Here's the rest of the info.

Top on the DB server:
KiB Mem : 12303516 total, 1111648 free, 600620 used, 10591248 buff/cache
KiB Swap: 1048572 total, 1021232 free, 27340 used. 11351520 avail Mem

mysqltuner.pl on db server:
https://gist.github.com/mgoh/8bd290e621df50cf4d13e6f521bfbf15

mysqltuner.pl on the web server:
https://gist.github.com/phette23/8597364500a83c516992441a481f71e1

So we can look into these recommendations. Do any stand out as particularly problematic?

Average of ratings: -

Re: Managing scheduled tasks & php load on server

by Ken Task - Thursday, January 28, 2021, 10:19 AM

"...stand out ...." If striving for perfection ... 0 [!!] ... full time credentialed DB admin ... got one of those?

So what stands out to me ... the biggies ...

Anything [!!] isn't good

On web server:
Performing tests on vm-mysql-08.cca.edu:3306 is a FQDN

** BIG OOPS! **
Outside ... ie, me, doing dig on the FQDN above gives
;; ANSWER SECTION:
vm-mysql-08.cca.edu. 3600 IN A 10.16.x.x

A 10. private IP which in non-routeable to outside world unless there is a map/boundary FW rules that allow public IP -> 10.
So should never be shown to public DNS.

On Web server config.php ... DB host should be IP address ... not FQDN. Web server OS does need to be able to ping the 10.

Your networking folks need to fix that!

Above leads to this:

[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance

On DB server config ... skip networking setting

[!!] InnoDB buffer pool / data size: 128.0M/4.3G

Buffer Pool should be = or slightly higher than data size ... 4.3G or higher.

See the recommendations also say that.

On DB server

[--] Physical Memory     : 11.7G
[--] Max MySQL memory    : 21.3G

name resolution is active : a reverse name resolution is made for each new connection and can reduce performance

InnoDB Metrics

[!!] InnoDB buffer pool / data size: 128.0M/9.8G

buffer pool needs to be 10G
InnoDB buffer pool instances 10

Run OPTIMIZE TABLE to defragment tables for better performance
      OPTIMIZE TABLE `moodle26`.`mdl_stats_daily`; -- can free 114 MB
      OPTIMIZE TABLE `moodle30_prod`.`mdl_files`; -- can free 123 MB
      OPTIMIZE TABLE `moodle30_prod`.`mdl_logstore_standard_log`; -- can free 272 MB
    Total freed space after theses OPTIMIZE TABLE : 509 Mb

???? moodle26 and moodle30_prod ...

Is moodle26 DB a dev? Is it tied/linked to any other Moodle Web server? How about the DB named 'moodle30_prod'? Is that the DB name in the config.php of the web server?

The above questions probably best not answered in forums ... but do sort those out.

Optimization above needs doing - period.
If you look at the tables ... stats_daily, mdl_files, and mdl_logstore_standard_log
are all hit daily/hourly/every second? .... last one the most.

Highly recommend getting your networking and server folks from IT on board there! Takes a village!

Uhhh ... is the 'stand out' question a quiz? Did I pass?

'SoS', Ken

Average of ratings:Useful (2)

Re: Managing scheduled tasks & php load on server

by Eric Phetteplace - Wednesday, January 27, 2021, 1:02 AM

Also for the people asking for cron log analysis, here it is. I'm not really sure where this leaves me though...what can I disable here? People need all these things. I've decreased the frequency of some less essential things (file_trash_cleanup_task, search_index_task) but I don't see any opportunities to disable them. I am curious why so much ad hoc core\\task\\asynchronous_backup_task what causes those?

Spreadsheet of Moodle tasks and their database usage plus time spent running.

Average of ratings: -

Re: Managing scheduled tasks & php load on server

by Ken Task - Wednesday, January 27, 2021, 4:58 AM

See ... I can also learn from you!! Don't mind showing my ignorance so .... what did you use to produce the chart?

If I am understanding it correctly, yes ... can't disable some ... obviously, the darker the red using more resources.

Async backup of courses ... when making a backup of a course ... either full with users or no user ... teacher has option to put that backup in a background process then go onto other things ... hopefully NOT adding to the very same course. :|

See one heavy hitter ... convert submissions ... which if using unoconv (python), libreoffice in headless mode, ghostscript, listener route ... that's a burst of memory/processing and if multiple teachers on your system doing that at same time ... which they might be doing to grade the final assignment before close of academic session.

Think you are getting closer ...

'SoS', Ken

Average of ratings: -

Re: Managing scheduled tasks & php load on server

by Eric Phetteplace - Thursday, January 28, 2021, 1:01 AM

I queried the task_log table that populates the scheduled task logs under Admin, downloaded the whole thing, added it to a Google sheet, then did some basic COUNTIF() and SUMIF() equations to calculate the totals of database use and time spent running. The red highlighting is conditional formatting—the redder the cell the higher the quantity is relative to the range of values in the column.

I already disabled convert_submissions and yet the problems continued, it was one of the first things I tried. Thus why there were only 4 total runs in the whole data set. I hope these async backup tasks reduce in frequency as instructors get their courses set up. I'm still guessing that these are people doing imports because there's really no reason for people to be creating backups. I wonder if we need to remove that moodle/backup:backupcourse privilege from the editingteacher role.

Average of ratings:Useful (1)