Moodle in English: Backup overloads database

Backup overloads database

by Rob Woof - Wednesday, April 17, 2019, 12:24 PM

I administer 10+ Moodle instances that share a single Ubuntu server. The server is hosted in Microsoft's Azure, and has 4 CPUs and 7GB RAM. The OS is Ubuntu Server 16.04.6 LTS, with PHP 7.0.33 and MySQL 5.7.25 on Apache.

One of the Moodles (currently running Moodle 3.5.3+) is causing load issues when it is running backups. This Moodle has a large number of users (8,000+), although only about half of them would be current (i.e. actually enrolled in a course) at any one time. The main user activity is quizzes - users are encouraged to attempt the quizzes in their subject multiple times before attempting the exam.

The issue we see is that when this Moodle is running backups, MySQL starts taking all of one CPU. We have verified that it is this Moodle's backups that cause this issue, because when we deactivated them one night, that night the issue did not occur. We also can see that each course backup takes more than half an hour - this is obvious from the backup file names, which have a timestamp.

This load on the database causes all of the Moodles to have degraded performance. When the problem Moodle's backups were running from 03:30 to 13:00 or 14:00, once they finished we saw an improvement in responsiveness across the board. Now the problem Moodle's backups are taking 16 or 17 hours every day.

It seems as though MySQL is not multi-core-aware. Either that, or it needs to be configured to use multiple cores.

Alternatively, are there settings within the Moodle that might affect the backup load like this? A full MySQL dump of this Moodle database is over 5GB - is it just that there is so much to back up that we're seeing this issue?

Any other suggestions for dealing with this?

Re: Backup overloads database

by Alain Raap - Thursday, April 18, 2019, 5:08 PM

Hi Rob,

I'm not familiar with Moodle on Windows Azure, but running the (automated) backup is a processor intensive task indeed.
We are running our Moodle instance on Redhat Linux + MariaDB database+data on a separate server with 8 CPU's and now
96GB memory for the database. We also had problems to tune this process and with a lot of courses, it takes a lot of time.
What is the number of courses backup-ed?
Things to look at are the temporary backup folder (fast enough FS?), load balancing and caching (do you use a caching server
on your Ubuntu OS?), just to name a few.
Ken Task wrote some good articles about the backup proces here in the forums, just have a look at them I can only say.
Maybe this thread can give you some insight, I had problems myself with the automated backup:
https://moodle.org/mod/forum/discuss.php?d=383012

Average of ratings: -

Re: Backup overloads database

by Ken Task - Thursday, April 18, 2019, 11:16 PM

The one moodle instance primarily used as a quiz machine is heavy processing ... at any time ... students taking or even during backups. So you might look into how those quizzes are setup ... all questions on one page? 5 questions per page? Could be you have some quiz related tables that are growing large. Larger tables = longer processing.

Here is a query I found (didn't create) that shows largest tables:

SELECT CONCAT(table_schema, '.', table_name), CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows, CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA, CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size, ROUND(index_length / data_length, 2) idxfrac FROM information_schema.TABLES ORDER BY data_length + index_length DESC LIMIT 10;

On a K12 site 'smaller' than yours but does a lot of quizzes throughout the site:

moodle2.mdl_grade_grades_history	2.31M	0.21G	0.34G	0.55G	1.66
moodle2.mdl_question_attempts	0.46M	0.16G	0.04G	0.19G	0.23

moodle2.mdl_question_attempt_step_data	1.91M	0.06G	0.02G	0.09G	0.40
moodle2.mdl_question_attempts	0.46M	0.16G	0.04G	0.19G	0.23

Also, when was the last time the DB for that instance was checked with some tool like MySQLTuner?

You didn't mention if the DB server is dedicated or does each of your servers 'do it all' ... code + DB + files?

What does the 'top' of 'top' look like?

'spirit of sharing', Ken

Average of ratings: -

Re: Backup overloads database

by Séverin Terrier - Friday, April 19, 2019, 3:50 PM

Thanks Ken for this interesting query. It can be useful in many uses to know big tables, and estimate total size occupied.

Séverin

Average of ratings: -

Re: Backup overloads database

by Ken Task - Friday, April 19, 2019, 8:31 PM

Wish I could find where I found it ... credit where credit is due. Uhhh .... it wasn't you was it? (red in face). :|

Now wouldn't that be an interesting admin addon?

'spirit of sharing', Ken

Average of ratings: -

Re: Backup overloads database

by Séverin Terrier - Friday, April 19, 2019, 9:05 PM

No, not me at all.

It seems you first wrote about it here (and several times since then), and certainly (searching with Percona) primilarly found it here.

Talking about me, i've just shared it in french.

Séverin

Average of ratings: -

Re: Backup overloads database

by Ken Task - Friday, April 19, 2019, 9:54 PM

Percona ... duh! Have some bookmarks for them. Good stuff. Maybe that's where I found it!

Average of ratings: -

Re: Backup overloads database

by Alain Raap - Friday, April 19, 2019, 9:33 PM

Do you know this query? To check out the overall size of the DB

SELECT engine, count(*) as TABLES, concat(round(sum(table_rows)/1000000,2),'M') rows, concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA, concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx, concat(round(sum(data_length+index_length)/ (1024*1024*1024),2),'G') total_size, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES WHERE table_schema not in ('mysql', 'performance_schema', 'information_schema') GROUP BY engine ORDER BY sum(data_length+index_length) DESC LIMIT 10;

Average of ratings:Useful (1)

Re: Backup overloads database

by Ken Task - Saturday, April 20, 2019, 5:12 AM

Thanks, Alain! That's a useful query as well.

'spirit of sharing', Ken

Average of ratings: -

Re: Backup overloads database

by Séverin Terrier - Tuesday, April 23, 2019, 4:12 PM

Yes, thanks Alain for this other useful query.

I've added it in the french discussion, after some reworking to ease reading it.

Séverin

Average of ratings: -

Re: Backup overloads database

by Ken Task - Friday, April 19, 2019, 10:03 PM

Back to OP's problem ...

There's a couple of things I've discovered on 'large' course backups (large here doesn't necessarily mean many files ... but 'large' processing ... like with Quizzes):

PHP max_input_vars increased from default ... which I think is 1000 to something larger ... 2 or 3 thousand.

And the unloading of Zend php xcode. See anything related to that in php -m at the bottom for Zend extensions. Not really needed to run Moodle and it had some restrictions that affected Moodle.

'spirit of sharing', Ken

Average of ratings: -

Re: Backup overloads database

by Rob Woof - Wednesday, April 24, 2019, 9:41 AM

Thanks, Ken.

I have found and changed the PHP max_input_vars to 300 as suggested - let's see how that goes.

Could you expand a bit more on the "unloading of Zend php xcode" part of your suggestion? I am not at all clear on what you mean.

Regards,

Rob

Average of ratings: -

Re: Backup overloads database

by Ken Task - Wednesday, April 24, 2019, 10:33 AM

max_input_vars suggestion was not less than it's default ... which I think is 1000 but more ... 3000,4000,or 5000.

Do php -m

The only Zend Extension Moodle will check for is Zend Opcache.

**IF** you see other Zend Extensions ... like xdebug (my bad, I said xcode) ... unload it.

In the php extensions directory ... CentOS 7 thats /etc/php.d/ ... there are ini files. One could do this:

[root@server php.d]# cat xdebug.ini
; Enable xdebug extension module
; zend_extension=/usr/lib64/php/modules/xdebug.so

If one puts a semi-colon in front of the line that loads the so, it's commented out and upon restart of apache won't be loaded.

My php -m then shows only one Zend Extension ... that of Opcache.

[root@server php.d]# php -m |grep xdebug
[root@server php.d]# php -m |grep Zend
Zend OPcache
[Zend Modules]
Zend OPcache

I had some troubles with xdebug wanting higher values/memory and it was the culprit on some issues.

'spirit of sharing', Ken

Average of ratings: -

Re: Backup overloads database

by Rob Woof - Wednesday, April 24, 2019, 11:26 AM

Thanks, Ken!

In my previous reply, "300" should have been "3000" - typo.

The only Zend extension active is opcache. XDebug is not enabled, nor is anything else whose name contains Zend.

I'll monitor it for a day or two and report back on any changes in the situation.

Rob

Average of ratings: -

Backup and restore

Backup overloads database