Moodle in English: Backup & Server Performance Major Issue

Hello all... hope someone can help us out!

We have started running into some troubles with the automated system backup each night about three days ago. The system sat there in the process running the mysql cpu resources up to 50% and 85mb of RAM. Our site slowed to a crawl making login nearly impossible. After shutting the system down and a restart, all seemed to be well, except for when we attempted a manual system backup. The whole process started over with the backup never completing. We shut the automated backup off and have attempted manual full backups three times over the past few days and still experience the same trouble. Does anybody have any ideas? Listed below are our system specs:

Moodle 1.5.2
1286 users
94 courses
Dell 2.8ghz Zeon
1 GB RAM
EasyPHP 1.8

Thanks for the help!

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Charlie Lindahl - Monday, 29 August 2005, 3:00 AM

You did not specify what version of MySQL you're using. I *assume* you're on a Windows platform 'cuz you mentioned "easyPHP".

I'm about to convert to MySQL 4.1 from MySQL 3.23 because there are a number of references in this forum that say that 4.1 is MUCH more efficient than 3.23 with respect to SQL joins in particular.

Also, I know as an administrator that 4.1 has a lot more tweaks that can be done for performance issues.

Please post whatever you find out back to this list / forum; I'll do the same.

Charlie

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Scott Grogg - Monday, 29 August 2005, 4:10 AM

Charlie,

We are using the following specs:

Apache 1.3.3
PHP 4.3.10
MySQL 4.1.9

Hope this helps!

Scott

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Penny Leach - Monday, 29 August 2005, 4:19 AM

It could possibly be related to this bug:
http://moodle.org/bugs/bug.php?op=show&bugid=3977&pos=5

Try running this in mysql

EXPLAIN SELECT DISTINCT u.id, u.id FROM mdl_user as u, mdl_message as m, mdl_message_read as mr, mdl_message_contacts as mc WHERE m.useridfrom = u.id OR m.useridto = u.id OR mr.useridfrom = u.id OR mr.useridto = u.id OR mc.userid = u.id OR mc.contactid = u.id

and pasting the output here.

This is part of backup/restore and we saw this query killing us after upgrading to 1.5.2, but in postgres.

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Scott Grogg - Monday, 29 August 2005, 6:58 AM

Penny,

Listed below is the printer version of the output. Any ideas?

Host: localhost
Database: moodle
Generation Time: Aug 28, 2005 at 06:56 PM
Generated by: phpMyAdmin 2.6.1 / MySQL 4.1.9-max
SQL-query: EXPLAIN SELECT DISTINCT u.id, u.id FROM mdl_user as u, mdl_message as m, mdl_message_read as mr, mdl_message_contacts as mc WHERE m.useridfrom = u.id OR m.useridto = u.id OR mr.useridfrom = u.id OR mr.useridto = u.id OR mc.userid = u.id OR mc.contactid = u.id;
Rows: 4

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	u	index	PRIMARY,id	PRIMARY	4	NULL	1328	Using index; Using temporary
1	SIMPLE	m	ALL	useridfrom,useridto	NULL	NULL	NULL	71	Distinct
1	SIMPLE	mr	ALL	useridfrom,useridto	NULL	NULL	NULL	780	Distinct
1	SIMPLE	mc	index	usercontact	usercontact	8	NULL	93	Using where; Using index; Distinct

Scott

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Scott Grogg - Monday, 29 August 2005, 8:08 AM

Another interesting tibit... we can do backup and restore of individual courses without any type of issue. Also, the mysql CPU usage problem actually starts before the backup begins... it happens when the course backup name and backup details are to show up.

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Penny Leach - Monday, 29 August 2005, 8:51 AM

I believe this problem only happens during the backup of the SITE course. Maybe try backing that up manually and including messages and see if it takes a long time and eats the CPU.

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Norbert Berger - Monday, 29 August 2005, 6:20 PM

Hi

reported the same problem recently, and have not had a fix yet. It seems to be related to the zipping up of courses: the backup process runs at full CPU for each individual course including the site course for an extremely long time, with mysql as the only process running the load. I am hoping somebody can cure this problem.

Norbert

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Penny Leach - Monday, 29 August 2005, 7:19 PM

If it's the zipping up courses, unzip or the php process would show up. Since it's the mysql process showing up eating the CPU, it's more likely to be the previously mentioned query.

If you would like to help, run the query I posted above. Try changing explain to explain analyze (sorry I should have said this before)

Another question - is this particular to 1.5 series or have you seen it with 1.4 series?

Average of ratings: Useful (1)

Re: Backup & Server Performance Major Issue

by Scott Grogg - Tuesday, 30 August 2005, 6:33 AM

Penny,

Indeed, the issue only happens during the backup of the site course. I've done it with & without message and with & without logs with the same results. We really do not want to backup each course individually since there are 95 of them now and we really want to keep a daily backup of the entire site.

Scott

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Penny Leach - Tuesday, 30 August 2005, 7:27 AM

Hi!

can you run again the query before with EXPLAIN ANALYZE rather than EXPLAIN ?

Also: it may be useful to head over to
http://moodle.org/bugs/bug.php?op=show&bugid=3977&pos=5

and vote for it.

Also, can you try running the query I posed earlier with UNION - you'll need to add in the correct db prefix.

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Pieterjan Heyse - Monday, 29 August 2005, 10:18 PM

Penny, this is odd. I am following this thread out of curiosity, but when running the query, I get an error.

I ran :
EXPLAIN SELECT DISTINCT u.id, u.id FROM moodle_user as u, moodle_message as m, moodle_message_read as mr, moodle_message_contacts as mc WHERE m.useridfrom = u.id OR m.useridto = u.id OR mr.useridfrom = u.id OR mr.useridto = u.id OR mc.userid = u.id OR mc.contactid = u.id

I get :
Impossible WHERE noticed after reading const tables

Using :
Mysql : 4.0.24_Debian-10-log
PHP : 4.3.10

Is this normal behaviour ?

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Penny Leach - Tuesday, 30 August 2005, 4:48 AM

Wow... no, I don't know what the answer to that is..

Can you try running this query and telling me if it works?

SELECT useridfrom,useridfrom FROM moodle_message
UNION SELECT useridto,useridto FROM moodle_message
UNION SELECT useridfrom,useridfrom FROM moodle_message_read
UNION SELECT useridto,useridto FROM moodle_message_read
UNION SELECT userid,userid FROM moodle_message_contacts
UNION SELECT contactid,contactid from moodle_message_contacts

This is what I want to replace the bad query with, but I'm not sure of it's compatibility. I *know* it works in postgres, not sure about mysql 4.x series, definitely not 3.23.. I'll probably end up wrapping it in a

if (database is not silly)
do it the smart way
else
take forever

AHEM. I mean, if database == postgres or mysql >=4

- Penny (only just on first cup of coffee)

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Pieterjan Heyse - Tuesday, 30 August 2005, 3:23 PM

Works like a charm ...

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Penny Leach - Wednesday, 31 August 2005, 5:30 AM

Thanks for that!

You got a weird error running the EXPLAIN ANALYZE query - but can you run the query itself and see what that does? If it works, can you get a feel for the relative speed of the one with UNION and the original one?

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Pieterjan Heyse - Thursday, 1 September 2005, 2:27 PM

STATS !

Your SQL query has been executed successfully (Query took 0.0009 sec) SQL query:
SELECT DISTINCT u.id, u.id
FROM moodle_user AS u, moodle_message AS m, moodle_message_read AS mr, moodle_message_contacts AS mc
WHERE m.useridfrom = u.id
OR m.useridto = u.id
OR mr.useridfrom = u.id
OR mr.useridto = u.id
OR mc.userid = u.id
OR mc.contactid = u.id
LIMIT 0 , 30

Your SQL query has been executed successfully (Query took 0.0008 sec) SQL query:
SELECT useridfrom, useridfrom
FROM moodle_message
UNION SELECT useridto, useridto
FROM moodle_message
UNION SELECT useridfrom, useridfrom
FROM moodle_message_read
UNION SELECT useridto, useridto
FROM moodle_message_read
UNION SELECT userid, userid
FROM moodle_message_contacts
UNION SELECT contactid, contactid
FROM moodle_message_contacts
LIMIT 0 , 30

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Jussi Hannunen - Wednesday, 31 August 2005, 7:28 PM

I can report the same problem affecting our production system. Site course (id=1) backup hangs indefinately and seriously affects the database server, basically the site shuts down.

Moodle 1.5.2+ (200560222) (snapshot from the August 15th, I think)
Debian GNU/Linux
Apache/1.3.33
mod_ssl/2.8.22 OpenSSL/0.9.7d
PHP/4.3.10-16
MySQL 4.0.24

I haven't run the queries in this thread; would doing so and reporting help or has a fix been found already? Not running the backup seems to work for us, and we are backing all of our stuff externally anyways.

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Penny Leach - Thursday, 1 September 2005, 5:40 AM

The fix has been found, and it works in both postgres and mysql >= 4.

The problem is that moodle has to support mysql 3.23.

I've been waiting for feedback about the patch, but I think I will just commit a fix that tests for postgres or mysql >= 4 and does it with UNION select, otherwise runs the slow query. As of 1.6, mysql 3.23 isn't going to be supported, so it'll only be for a while anyway.

I'll probably do this today, and commit it to CVS, I will announce it once it's done and you can all update your copies.

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Scott Grogg - Saturday, 3 September 2005, 3:05 AM

Penny,

Have you had a chance to update into CVS yet? Also, what seems to be the overall problem?

Scott

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Penny Leach - Saturday, 3 September 2005, 5:13 AM

Hi Scott,

Yes, I've updated CVS - both the stable branch and HEAD now have the fix. There's a time delay between anonymous CVS and developer CVS of about 8 hours, I think (why I didn't post immediately), so it should be good to go.

Basically, when you present a database with a query, it decides how it's going to execute it, based on what indexes there are, etc. What you see when you do EXPLAIN or EXPLAIN ANALYZE is the output of what the query planner plans to do - how it's going to run the query to get the results for you.

Now, a problem like this can be approached a number of different ways. Perhaps it would help if I explain what the query does. It wants to get a list of particpants in the messaging system. So, there are 3 relevant tables (and the user table, which isn't actually relevant for the problem, but needed for the glue of the query). The tables are: message, message_read and message_contact. Message is the 'queue' of incoming messages, message_read is the history of messages, and message_contact is the contact list. So each of those tables has two relevant fields, userto and userfrom, and for the contact list, user (owner of contact list) and contact (person on contact list). That makes 6 possible fields where any given userid can be, and we want to get a (distinct) list of them all.

The way the query was written joined all the tables on to user, joining each field (userfrom, userto) onto the user.id field. Which works, and gives us the answer, but is an approach to the problem that basically caused the query planner to explode. It's probably fine with small databases, but not all of us are so lucky as to support small databases

The rewrite does a UNION select - that is, it gets a list of userids from each of the 6 fields, and returns a distinct list of them. It's an approach that I know makes postgres much happier, and from everyone's posting, mysql as well

The problem is mysql 3.23. Originally, I didn't know how to deal with that, so I left it as it was, hoping to at least alleviate the problem for the mysql 4 and postgres users, but Eloy has a good idea over in that bug report, which is basically to replicate the UNION part of the query in php. So we do 6 queries, and then use php to do the merging and distinct part. I guess I should probably write that and check it in to CVS as well, but it's Saturday morning and I'm off to my jewellery workshop.

Hope that helps to clear this all up a bit!

Penny

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Scott Grogg - Monday, 5 September 2005, 11:48 AM

Penny,

Thank you for making the update. I have implemented the modified files and the backups are working without the slowdown. But, after the automated backups were turned on, we ran into another issue... the backups are not happening at all. It does not appear to occur at any level. Any ideas?

Scott

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Penny Leach - Monday, 5 September 2005, 12:34 PM

Wait - manual backups are working without the slowdown but automated backups not at all?

Did you update via CVS or a build?

If CVS, how did you update? What files etc?

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Scott Grogg - Monday, 5 September 2005, 11:18 PM

Penny,

Well, the issue is getting more interesting...

First of all, manual backups work without any issues and the strain on the server does not exist any longer. For that, we are very happy.

Second, we have scheduled backups at 3am every night. But, since the upgrade, we have noticed that the automated backups are not taking place. We have verified that moodlecron is working, that it points to the correct location, etc. Everything seems fine that way.

Third, we made the upgrade the first time through cvs. After causing a few bumps in the road, we upgraded with the Moodle Latest file... which worked like a charm.

Fourth, we believe we may have noticed cron stopping half way through due to a mail server problem we have been having. An error message shows up and the cron does not complete its job. Could this be the reason for the automated backups not working?

Thanks for the help!

Scott

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Penny Leach - Tuesday, 6 September 2005, 3:39 AM

Yes, it's possible that the mail server problem could kill the cronjob.. what exactly is the problem with the mail server?

Do you log the output of cron? Do you run it from wget or curl? If you do, there may be interesting information in the apache error log. If you run it from php cli there won't be though.

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Scott Grogg - Thursday, 8 September 2005, 6:31 AM

Penny and everyone that helped thus far:

We figured out the issue with the automated backups not taking place. It seems that our network guru that houses our co-located server did not make all of the necessary changes when he moved several of our machines over a week ago. One of the changes specifically was updating the IP address for the cron job to process on our Moodle server. He updated the one on our test server... just not the production one. So, after a few changes, reboot of the machine, and few tense moments, the cron process completed including the automated backup.

The process completed worked through 85 courses in 27 minutes. There was no strain on mysql or apache during the process. So, all appears to be running well again. Thanks everybody for the help!

Scott

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Penny Leach - Thursday, 8 September 2005, 7:16 AM

That's excellent news!

Average of ratings: -

Where do I find the fix?

by Norbert Berger - Thursday, 8 September 2005, 4:42 PM

Hi Penny

I have been looking for the fix you talked about but find no mention in the daily latest version. Since I do not use CVS, how do I get it? Please advise.

Thanks for your efforts.

Norbert

Average of ratings: -

Re: Where do I find the fix?

by Penny Leach - Friday, 9 September 2005, 5:28 AM

It should be in the latest daily build. I'm not sure what you mean about looking for the fix but finding no mention... the daily builds themselves don't have release notes, only the actual releases do. You just need the latest copy of 1.5.2+

- Penny

Average of ratings: -

Re: Where do I find the fix?

by Norbert Berger - Friday, 9 September 2005, 4:16 PM

Thanks for replying.

I am not referring to release notes but the change log linked to on the download page, which is http://download.moodle.org/stable15/CHANGES
This seems to specify all the recent changes incorporated. As this seemed quite uptodate mentioning other modifications, I was under the impression the fix for backup had not been included yet.

Will try and let you know how things are now.

Gratefully

Norbert

Average of ratings: -

Re: Where do I find the fix?

by Penny Leach - Friday, 9 September 2005, 5:58 PM

Huh! I didn't know about that. Apologies.

Ok. My sf username is mjollnir_, and if you search for me, you find:

 

2005-09-07 Wednesday 12:43  mjollnir_



	* message/lib.php: 

	

	Better fix to the messaging problem. 

        Will make mysql <4 users happy

	too. Credit for php workaround:Eloy



and



2005-09-01 Thursday 07:57  mjollnir_



	* lib/datalib.php, message/lib.php: 

	

	Fixing bug 3977: sort of. Check for mysql >= 4 

        or postgres, do a smarter query with a UNION. 

        Otherwise, do the same old query. This

	is not very good for mysql 3.23 users with 

        large datasets, but not much else I can do.

Average of ratings: -

Re: Where do I find the fix?

by Norbert Berger - Friday, 9 September 2005, 9:52 PM

Many thanks. I should have recognised the wording as you had posted that here before.

Much appreciated

Norbert

Average of ratings: -

fixed

by Norbert Berger - Saturday, 10 September 2005, 5:02 AM

Dear Penny

Thanks for the fix which I downloaded and installed by upgrading to this morning's 1.5.2+ version.
I monitored the backup which took an hour rather than the 11 hours 30 minutes. Besides, mysqld CPU usage was down to between 0 and 40% and Apache made up for most of the rest. However, access to the site was not noticeably slower than usual.

So many thanks for the fix which works a real treat.

Norbert

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Pieterjan Heyse - Thursday, 8 September 2005, 2:56 PM

How do you guys backup without the major strain on your servers ? Just being curious, because currently we only have 15 courses, but this will grow.

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Eloy Lafuente (stronk7) - Friday, 9 September 2005, 1:34 AM

Doing FULL (also called SITE) backups is the best method to have all your data saved with the best confidence and the shorter recovery time. Basically you have to backup to a safe place the elements specified in this piece of documentation (point 1).

COURSE backups are more expensive (time, cpu usage...), can cause more problems and the recovery time to have your site running again is really looooong.

I would use COURSE backups only under some environments, to have "fresh" copies of courses to be reused or distributed individually, but NEVER as my primary backup system (unless my host/ing doesn't allow me to execute the preferred SITE backups).

So, you decide...

Ciao

Average of ratings: -

Re: Backup & Server Performance Major Issue

by koen roggemans - Tuesday, 6 September 2005, 1:08 AM

Thank you for the fix Penny.
The automated backup is still a very consuming script. After the fix, I still have a serverload of 10 to 14 during a very long time.

Could there be a way to make it less resource consuming for sites on shared hosts eg by spreading the backup over a longer period? (something like maximum x courses/hour)

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Penny Leach - Tuesday, 6 September 2005, 3:40 AM

Still very consuming? Just to make sure - you are running mysql 4.x or postgres right? The fix I put into the backup currently doesn't do anything for mysql 3.

Average of ratings: -

Re: Backup & Server Performance Major Issue

by koen roggemans - Tuesday, 6 September 2005, 4:30 AM

It's a 4.0.25

The backup ran for 35 minutes for 109 courses/2200 users on a one processor machine (I'm afraid I don't have more specs).
Should this be considered reasonable?

My hosting company sugested to take a look at the indexes.

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Penny Leach - Tuesday, 6 September 2005, 5:23 AM

hmm. not sure really how long it should take, sorry,

I did a lot of work adding indexes in moodle 1.4 - that shouldn't be a problem really.

Other than the fix I did to the query in messaging, I'm out of ideas.

Average of ratings: -

Re: Backup & Server Performance Major Issue

by koen roggemans - Tuesday, 6 September 2005, 5:43 AM

I have discussed with my hosting provider and it is ok to run them at night around 3. I'm very glad with that, because in the backup period, the server doesn't respond anymore. I'm saved. Thanks a lot

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Martin Dougiamas - Tuesday, 6 September 2005, 2:15 PM

35 minutes for 109 courses could be reasonable depending on how the machine was set up.

Backups are very processor and RAM intensive, especially for larger sites ... this is because they are necessarily complex ... a lot of work is done to move all the complex Moodle data structures (optimised for runtime speed) into a single XML file per course.

I don't allow Moodle backups on my moodle.com servers (because I backup the database for the whole site directly instead). If you can't do this or if the hosting provider can't cope with it even at night then it might be time to upgrade to a bigger or dedicated server ...

Average of ratings: -

Re: Backup & Server Performance Major Issue

by koen roggemans - Tuesday, 6 September 2005, 3:56 PM

Yes, it's true.
The single file backup is magic and it is logical that it takes so much effort from the server.
I am considering to put on a server for my own - the database backup will be the way to go then. Just waiting for budget ...

Average of ratings: -

Re: Backup & Server Performance Major Issue

by N Hansen - Tuesday, 6 September 2005, 5:44 PM

Martin-Maybe a way around it being so server intensive would be to allow for more customized automatic backups. For example, some courses may not be so active and don't need daily backups, but rather weekly ones. Some types of data may not change that often (eg resources) or be so important (eg logs) and may not need to be backed up as often. Being able to customize the individual automated course backups would help. I can understand why you might not want teachers rather than admins having such control because they might screw things up, but I think it might help a lot to be able to do this. Or even have a way to stagger the timing of the various course backups, rather than have them all run sequentially. Just a thought. My host backs up my entire site nightly to another location but it is reassuring to be able to back it up myself and to download it to my own computer when necessary.

Average of ratings: -

Svar: Re: Backup & Server Performance Major Issue

by Joar Herseth - Tuesday, 20 September 2005, 7:27 AM

hi
We do a direct backup with cron - mysqldump and tar to another server -150 courses and 2000 users. The whole process takes about 9 minutes on a single p4-3GHz server.

Average of ratings: -

Re: Svar: Re: Backup & Server Performance Major Issue

by Anil Sharma - Saturday, 8 October 2005, 4:59 PM

Joar

can you please explain this process in more detail ?

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Penny Leach - Wednesday, 7 September 2005, 12:57 PM

Update to mysql 3.23 compatibility for this fix:

I've put in a workaround in the latest stable, so you should all be able to see backups running happier.

Remember there's a delay between developer CVS and everyone else so it'll probably be ready in 12 hours or something to get from either anonymous CVS or the nightly build of 1.5.2+

Average of ratings: -

Re: Backup & Server Performance Major Issue

by N Hansen - Sunday, 4 September 2005, 10:17 PM

I've been having the same problems too. My host seemed to figure out the problem was being caused by automatic backups and so I shut that down last week and they advised me to upgrade but since I see that people are having the problem still with the latest version I'm not quite sure what to do. I've had no problem with manual backups. My site just went down again this morning, database problems again, and I am awaiting an answer from my host to see what the problem is now. It seems to happen on Sunday mornings for some reason, even though the auto backups were supposed to run every day. But since I turned off backups, there has to be another cause, and I should note I don't have messaging enabled on my site so it can't be that either. I'm running 1.5 plus the following

PHP version	4.4.0
MySQL version	4.0.25-standard

Average of ratings: -

Re: Backup & Server Performance Major Issue

by N Hansen - Sunday, 4 September 2005, 11:15 PM

The problem I am having seems to be related to the logs in some way. The table gets corrupted, and they have been fixing it, but it keeps happening again. I've been advised by my host to clear my logs and start over again.

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Penny Leach - Monday, 5 September 2005, 12:35 PM

Hmm. You could try making the log life time shorter? Just a thought - don't know if it would help at all.

Average of ratings: -

Re: Backup & Server Performance Major Issue

by N Hansen - Monday, 5 September 2005, 12:48 PM

I've posted elsewhere about this now (in the backup and restore forum), but it seems the table had become corrupted. But I'm wondering whether there is a way to archive log activity offline, even as an Excel file or something. Because logs can get quite large but it would be nice to keep them and view them from time to time.

Average of ratings: -

Re: Backup & Server Performance Major Issue

by Penny Leach - Monday, 5 September 2005, 1:32 PM

hrmm. in 1.6 you will have STATS which will run through the log table and store higher level information.. so as the logs get deleted over time you still have an aggregate available. That doesn't help you now though.

Perhaps phpmyadmin has some sort of export table facility?

Average of ratings: -