Intermittent unavailability and logging of database unavailability

Intermittent unavailability and logging of database unavailability

by Jonathan Rabson -
Number of replies: 3

We have a problem where Moodle is sometimes unavailable for several seconds, or sometimes a few minutes.  It's rare and unpredictable enough to usually be dismissed as a "fluke," but we need to solve it somehow.  I wonder if others have had similar experiences.

In particular, it seems that these "unavailable" moments tend to center around the MySQL database; test PHP pages that just echo text seem to work in such cases.  I was thinking of putting up a test page, running as a cron job, to see what times in the day the database becomes unavailable, with what error (if there is an error), and how frequent the problem really is.

However, I thought I should first check if there's anything already built into Moodle I can use, instead of reinventing the wheel.  When I search the site about logs or logging, in Moodle that seems to refer to activity reports.  Is there any place in Moodle where the application will log to a file if it's unable to access the database, or if it takes too long to access it?

Average of ratings: -
In reply to Jonathan Rabson

Re: Intermittent unavailability and logging of database unavailability

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

You could turn on the http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html in MySQL.

If you want a guess, consider this: Are the time when Moodle slows down the same as when the cron.php script is running? Or is it just some cron runs, like when Moodle decides to compute the statistics?

Average of ratings: Useful (1)
In reply to Jonathan Rabson

Re: Intermittent unavailability and logging of database unavailability

by Ken Task -
Picture of Particularly helpful Moodlers

While Tim's advice might be all you need/desire, thought I'd share:

For number of connections to Apache:
connections script:

echo $(date +%Y%m%d%-H%M) >> /var/log/connections.log;
netstat -plan|grep :80|awk {'print $5'}|cut -d: -f 1|sort|uniq -c|sort -nk 1 >> /var/log/connections.log;

Run a cron job to execute it however often you like.

One could watch the log in realtime:
tail -f /var/log/connections.log

For MySQL: http://mtop.sourceforge.net/
mtop (MySQL top) monitors a MySQL server showing the queries which are taking the most amount of time to complete. Features include 'zooming' in on a process to show the complete query, 'explaining' the query optimizer information for a query and 'killing' queries. In addition, server performance statistics, configuration information, and tuning tips are provided.

'spirit of sharing', Ken

Average of ratings: Useful (1)
In reply to Ken Task

Re: Intermittent unavailability and logging of database unavailability

by Visvanath Ratnaweera -
Picture of Particularly helpful Moodlers Picture of Translators
echo $(date +%Y%m%d%-H%M) >> /var/log/connections.log;
netstat -plan | grep :80 | awk {'print $5'} | cut -d: -f 1 | sort|uniq -c | sort -nk 1 >> /var/log/connections.log;
Any more offers?
smile

I wonder how many clicks one needs for the same in a GUI operating system.
Average of ratings: -