mindfieldz

Inside the mind of a geeky learning technologist

Fixing your Moodle statistics

| 0 comments

Moodle statistics are great. Personally, every October, I scream like a schoolgirl and wave the most recent statistics graph at my colleagues whilst shouting at how all the numbers are bigger. However, it seems that on busier servers, especially one like ours with 700,000 actions in one month, Moodle can easily fall behind.

For the past three months, since October 2011, our Moodle serve hasn’t been compiling statistics.

I can’t fully demonstrate the symptoms here, as I only decided to write this blog post after I had fixed things. But if you look at the graph below, you’ll see an earlier month where statistics also failed. 1st April 2011 has no actions logged.

Moodle statistics

Example Moodle statistics

This fix works on Moodle 1.9.3+, it should also work on Moodle 2.0 but some of the lines might be slightly different. YMMV

So… how to fix it… well…

First off, log in to your Moodle server and look at the Server > Statistics settings. Ensure that the following are set:

  • Enable statistics – checked (duh!)
  • Maximim processing interval – all
    (This is the earliest date for which Moodle will try to compile statistics.)
  • Maximum runtime – until complete
  • Days to process – 2
    (This is how many days worth of statistics will be processed each day, best to keep this >1 and <31
  • Run at – whatever you like, we’ll ignore this later
  • User threshold – 0
  • Maximum parent categories – 1

 

Then, open up your favourite database editor and look at the Moodle database. There are a few rows that you’ll need to adjust, which can best be found by running:

SELECT [id]
      ,[name]
      ,[value]
  FROM [moodle].[moodle].[mdl_config]
  WHERE name LIKE '%stats%'

The fields you want to edit, are:

  • statslastdaily = 0
  • statslastweekly = 0
  • statslastmonthly = 0
  • statslastexecution = 0

 

Finally, you’ll want to change the /admin/cron.php so that it only compiles statistics, and it ignores when it was last run. The best way of doing this is to take a copy of the existing cron.php, and rename it to something sensible, eg. cronstats.php. Then, editing our copy of cronstats.php:

  • Keep everything above:
    mtrace("Server Time: ".date('r',$timenow)."\n\n");
  • Keep everything below the line:
    //Unset session variables and destroy it
  • For the rest of the document, remove everything except the following:
        if (!empty($CFG->enablestats) and empty($CFG->disablestatsprocessing)) {
            require_once($CFG->dirroot.'/lib/statslib.php');
            // check we're not before our runtime
            $timetocheck = stats_get_base_daily() + $CFG->statsruntimestarthour*6$
    
            if (time() > $timetocheck) {
                // process configured number of days as max (defaulting to 31)
                $maxdays = empty($CFG->statsruntimedays) ? 31 : abs($CFG->statsrunt$
                if (stats_cron_daily($maxdays)) {
                    if (stats_cron_weekly()) {
                        if (stats_cron_monthly()) {
                            stats_clean_old();
                        }
                    }
                }
                @set_time_limit(0);
            } else {
                mtrace('Next stats run after:'. userdate($timetocheck));
            }
        }
  • Then, comment out some lines so that it reads:
        //if (!empty($CFG->enablestats) and empty($CFG->disablestatsprocessing)) {
            require_once($CFG->dirroot.'/lib/statslib.php');
            // check we're not before our runtime
            //$timetocheck = stats_get_base_daily() + $CFG->statsruntimestarthour*6$
    
            //if (time() > $timetocheck) {
                // process configured number of days as max (defaulting to 31)
                $maxdays = empty($CFG->statsruntimedays) ? 31 : abs($CFG->statsrunt$
                if (stats_cron_daily($maxdays)) {
                    if (stats_cron_weekly()) {
                        if (stats_cron_monthly()) {
                            stats_clean_old();
                        }
                    }
                }
                @set_time_limit(0);
            //} else {
            //    mtrace('Next stats run after:'. userdate($timetocheck));
            //}
        //}

This last step will stop the cronstats.php script from checking that it’s already run that day, meaning that you can run the script several times in succession. This is handy because something must have prevented the statistics from being compiled in the first place, and we want to determine what that is without having to reset the mdl_config table each time.

 

Warning! Running the cronstats.php will use a significant amount of CPU/RAM on your database server. The queries that Moodle uses to compile statistics are quite complex. It’s fine to run on a live server, just bear in mind that things will slow down.

Now, if you run the crontstats.php from the command line (or directly via the web, if you prefer). It should go off and compile the statistics for a few days (whatever you set ‘Days to process’ to, above). If you’re lucky, the script will start to list hours of the day, and then eventually complete with the date that it’s just processed. eg. 0:1,1:20,2:30 … January 1 2012.

If you’re unlucky, like me, your script will get to the 0:1,1:20,2:30 part, then fail miserably with a SQL error. Which is probably what caused the script to fail in the first place, 3 months ago. But, at least we can now run this script repeatedly, and see what’s going on behind the scenes.

 

Warning! Until now, we’ve just been altering some configuration settings and timestamps, we’ve not changed anything serious. These next steps will vary wildly, depending on your database platform (we use MSSQL), and your experiences. You have been warned.

Part of the reason the cronstats.php script is not completing is because its taking too long. I can’t prove this, but the 100% CPU usage on our SQL server, and the 3 minutes it took the script to run before it gave up, are good indicators. For MSSQL we can run the profiler and database tuning wizards to decide whether additional indexes would improve things. Again, YMMV, but in our case the SQL server suggested a 30% improvement if we added some indexes, mainly to the mdl_context table.

After these index improvements, cronstats.php runs successfully, and manages to build statistics. In our case there was one last error where the SQL server timed out whilst trying to run some DELETE statements on the mdl_stats_… tables, but fortunately the cronstats.php script will list the statements that have failed, and you can run these by hand.

 

Final big warning! This generates a very large number of transactions in your database. Processing 3 months worth of statistics created a MSSQL transaction log in the region of 20GB. Don’t be a numpty like me and ignore this. I’ve spent most of this morning cleaning up transaction logs and disk space on our database server. It might be better to spread this entire process over a few days, or change the transaction logging method on your database whilst you run this.

Finally, a few observations from this:

  • MSSQL Profiler and Database Tuning Wizard are excellent tools. The Moodle database isn’t the most efficient, especially on big installations, and inefficient indexes can cripple performance.
  • Make sure you have enough disk space on your database servers, no, really!
  • Always set your “Days to process” to more than 1 day. Since the statistics are only compiled once per day, you’ll want to have this set somewhere between 2 and 7; any less and we could start to see a backlog.
  • Keep a regular eye on the statistics. It’s very quick and easy to recover a week or two of statistics, but any mocan be problematic (see my server snafu, above).

That’s it. I am sure there will be more Moodle adventures soon.

Leave a Reply

%d bloggers like this: