Hi folks,
We're having some serious database issues since upgrading to 1.8.2+ (from 1.6.2+). It seemed fine (if a little slower than usual) after the upgrade on Wednesday PM, but on Thursday morning the site was giving the MySQL 'database overloaded' error and no-one could connect. We restarted MySQL (which took a long time to stop) and everything was OK again until the next morning when exactly the same thing had happened again.
I had the built-in ADOdb performance monitor polling the database stats from Thursday morning onwards, and the log shows that the number of connections had gradually increased from a normal figure at about 4:30PM on Thursday until it finally maxed out at 99 connections at around 1:00AM on Friday morning! I checked the access logs and the site hadn't been busy at all during this time.
Does anyone have any idea what could possibly cause something like this to happen?
Thanks,
Tony
This seems to be related to 'Upload users', or possibly Flat file enrolment - we use these twice a day. First we upload a file containing all users (about 26000+ in total, but usually only <50 new ones each time), and then upload a flatfile enrolment file to the site files directory, again containing all enrolments. With 1.6.2 these processes used to take a few minutes each. What happens since upgrading to 1.8.2 (although not every time) is:
- We upload the users, which takes about 5 minutes.
- We upload the enrolments file.
- Over the next few hours the number of database threads gradually increases to the max of 99 (along with CPU usage) and the site is unusable for several hours. During this time the enrolments file (and presumably other cron tasks) is not processed.
- Eventually the number of threads returns to normal and the enrolments file is processed successfully.
- AFTER the enrolments file is deleted by the cron process, I get a load of emails (sometimes 1, sometimes 100) saying:
"The text file you are using for file-based enrolments
(/srv/moodledata/1/enrolments.txt) can not be deleted by the cron process.
This usually means the permissions are wrong on it. Please fix the permissions
so that Moodle can delete the file, otherwise it might be processed repeatedly."
This has only happened 3 times since we upgraded a week ago, but I just can't predict when it will happen and when it won't! It could be that the enrolments thing is just a coincidence, and not the trigger at all.
One thing that HAS happened consistenly since upgrading though, is that email telling me that the enrolments file can't be deleted - and it always comes (usually several times a minute) about 15 minutes AFTER the file has been successfully deleted!
Tony
- We upload the users, which takes about 5 minutes.
- We upload the enrolments file.
- Over the next few hours the number of database threads gradually increases to the max of 99 (along with CPU usage) and the site is unusable for several hours. During this time the enrolments file (and presumably other cron tasks) is not processed.
- Eventually the number of threads returns to normal and the enrolments file is processed successfully.
- AFTER the enrolments file is deleted by the cron process, I get a load of emails (sometimes 1, sometimes 100) saying:
"The text file you are using for file-based enrolments
(/srv/moodledata/1/enrolments.txt) can not be deleted by the cron process.
This usually means the permissions are wrong on it. Please fix the permissions
so that Moodle can delete the file, otherwise it might be processed repeatedly."
This has only happened 3 times since we upgraded a week ago, but I just can't predict when it will happen and when it won't! It could be that the enrolments thing is just a coincidence, and not the trigger at all.
One thing that HAS happened consistenly since upgrading though, is that email telling me that the enrolments file can't be deleted - and it always comes (usually several times a minute) about 15 minutes AFTER the file has been successfully deleted!
Tony
I've no solutions for you Tony, but thanks for posting this. We plan to upgrade in a few weeks, so I'll be testing upload users and flatfile enrolment, and I'll let you know if anything similar happens here.
In case it might be of use: we do a diff of yesterdays/todays userlist before we run it, which means the uploadusers txtfile is pretty small after registration finishes.
In case it might be of use: we do a diff of yesterdays/todays userlist before we run it, which means the uploadusers txtfile is pretty small after registration finishes.
Thanks for replying Denis. I was thinking of using diffs but I've yet to find a util that will allow me to produce them easily. What do you use? For file comparison I use Winmerge, which is capable of generating patch files, but patch files are of no use for this. Everything else I've looked at can't output much at all!
I'm becoming more and more convinced that this is related to enrolments. I uploaded a flatfile on Friday afternoon while monitoring database activity, and the number of threads started to increase almost immediately. The queries being processed all seem to be related to roles, e.g. (from phpMyAdmin's process list table, so truncated I'm afraid):
SELECT COUNT(DISTINCT prs.userid) FROM (SELECT ra....
SELECT u.*, ul.timeaccess as lastaccess, ra.hidden...
SELECT\n ra.id, ra.roleid, ra.userid...
SELECT\n ra.id, ra.roleid, ra.userid...
SELECT\n ra.id, ra.roleid, ra.userid...
SELECT\n ra.id, ra.roleid, ra.userid...
SELECT rc.capability, c1.id as id1, c1.id as id2, ...
SELECT\n ra.id, ra.roleid, ra.userid...
UPDATE mdl_role_assignments SET hidden = '0', time...
SELECT u.*, ul.timeaccess as lastaccess, ra.hidden...
SELECT u.*, ul.timeaccess as lastaccess, ra.hidden...
SELECT u.*, ul.timeaccess as lastaccess, ra.hidden...
SELECT * FROM mdl_role_assignments WHERE roleid = ...
After about an hour, when the number of threads had reached 24 and I needed to go home, I deleted the enrolments file (which was still sitting in the site files directory), and things gradually started to settle down. And then about 6 hours later I received 12 emails telling me that the enrolments file couldn't be deleted!
The weird thing is, and I know this defies logic, but this only seems to happen in the afternoon! Running enrolments in the morning seems to have no adverse effects and the flatfile gets deleted within about 10 minutes (although I still get an email or 2 about half an hour later to say it can't be deleted). So I'm just wondering if there's some scheduled task that I don't know about, that kicks off about 4pm and maybe does kind of roles cleanup. And maybe giving Moodle a large-ish enrolments file to deal with while this is running throws a spanner in the works.
Just grasping at straws really...
I'm becoming more and more convinced that this is related to enrolments. I uploaded a flatfile on Friday afternoon while monitoring database activity, and the number of threads started to increase almost immediately. The queries being processed all seem to be related to roles, e.g. (from phpMyAdmin's process list table, so truncated I'm afraid):
SELECT COUNT(DISTINCT prs.userid) FROM (SELECT ra....
SELECT u.*, ul.timeaccess as lastaccess, ra.hidden...
SELECT\n ra.id, ra.roleid, ra.userid...
SELECT\n ra.id, ra.roleid, ra.userid...
SELECT\n ra.id, ra.roleid, ra.userid...
SELECT\n ra.id, ra.roleid, ra.userid...
SELECT rc.capability, c1.id as id1, c1.id as id2, ...
SELECT\n ra.id, ra.roleid, ra.userid...
UPDATE mdl_role_assignments SET hidden = '0', time...
SELECT u.*, ul.timeaccess as lastaccess, ra.hidden...
SELECT u.*, ul.timeaccess as lastaccess, ra.hidden...
SELECT u.*, ul.timeaccess as lastaccess, ra.hidden...
SELECT * FROM mdl_role_assignments WHERE roleid = ...
After about an hour, when the number of threads had reached 24 and I needed to go home, I deleted the enrolments file (which was still sitting in the site files directory), and things gradually started to settle down. And then about 6 hours later I received 12 emails telling me that the enrolments file couldn't be deleted!
The weird thing is, and I know this defies logic, but this only seems to happen in the afternoon! Running enrolments in the morning seems to have no adverse effects and the flatfile gets deleted within about 10 minutes (although I still get an email or 2 about half an hour later to say it can't be deleted). So I'm just wondering if there's some scheduled task that I don't know about, that kicks off about 4pm and maybe does kind of roles cleanup. And maybe giving Moodle a large-ish enrolments file to deal with while this is running throws a spanner in the works.
Just grasping at straws really...
I was thinking of using diffs but I've yet to find a util that will allow me to produce them easily
Get 'diff' (http://gnuwin32.sourceforge.net/packages/diffutils.htm), grep (http://gnuwin32.sourceforge.net/packages/grep.htm) and 'sed' (http://gnuwin32.sourceforge.net/packages/sed.htm) for win32, and then you can do something like this:
diff -u uploadusers.old uploadusers.new | grep '^+' | grep -v '^+++ ' | sed -e 's/^+//' > uploadusers.diff
You just need to edit uploadusers.diff to add the header line with the filed names, and you are done.
Saludos. Iñaki.
IA, TB, DC, and whoever else is interested,
I did not respond to the original question about diffs because it was specifically addressed to DC. However, since he has not answered and you (IA) have, I just thought I'd put my two cents in about how I do diffs on my PC: I use the old DOS command FC (File Compare). How quaint. But it works! 
It can do binary as well as ASCII comparisons, case sensitive or insensitive, line numbers or not. Just type FC /? in a command prompt window for all the options. If anyone's interested.
RLE
A very belated thank you to Iñaki for this 
We've been using it every day since the start of term in September and it's successfully keeping the database issues at bay until we can upgrade to 1.9 and hopefully fix the problem for good.
Cheers,
Tony
We've been using it every day since the start of term in September and it's successfully keeping the database issues at bay until we can upgrade to 1.9 and hopefully fix the problem for good.
Cheers,
Tony