Hello
Every day at 1.00pm i get a database overload error. and everything stops working for 5-7minutes.
This is really irritating, what could be the problem?
I recommend capturing the processlist when the slow down occurs. It could be autobackups or a mysql backup as Visvanath mentioned, it could also be statistics is harvesting.
-- this is a query you could use to grab the processlist
select id, user, host, time, info from information_schema.processlist where state != 'Sleep';
Hi Muguel and Visvanath.. Well i checked the course backups, they are scheduled every Sunday, which is like an off. Nobody really uses the platform..
Also tried the query Miguel stated, 
I think im naive to understand what can i gather from the query above. Do i have to look for the process id?
Thanks
Hi Seth
If the obvious culprits (see my prev. mail) are out, you need to monitor the machine before and at 1 p.m. At a minimum 'top' and uptime. More elaborate methods have been discussed here: "How do you monitor your Moodle server?" https://moodle.org/mod/forum/discuss.php?d=192162.
If the obvious culprits (see my prev. mail) are out, you need to monitor the machine before and at 1 p.m. At a minimum 'top' and uptime. More elaborate methods have been discussed here: "How do you monitor your Moodle server?" https://moodle.org/mod/forum/discuss.php?d=192162.
If you're using InnoDB on MySQL then install a tool called innotop
It's effectively the same as 'top' for processes, except it's for sql queries.
Longest will run at the top of the list, and will change colour as they breach the slow query limits, it also shows the time the query has been running for, and whether it's being run from the system or network.
Simply run the tool and monitor it, start it before the known outage time, and wait for slow queries to show up.
The key thing we're looking for here is table and/or row locking that's prohibiting other queries to run, thus pooling those queries in the queue until the connection limit is reached.
Ensure you login with innotop using your root credentials, as the root user is exempt from connection limits, so you won't get turfed.
Get back to us with the results and we'll see where things are going awry.
If you're using MyISAM (cringe) then 'show processlist' will show similar information, but will be a lot less obvious and you'll have to run it multiple times to see the changes.
Matt
A couple other things to check would be
1 - when is the cron script running? Could it be doing its work then?
2 - are there any other scheduled tasks on the server than may be running at that time?
I have a scheduled task that backs up the DBs on the server. Its a vhosted server with multiple moodle instances. I have them running in the early morning hrs.