Performance and a new module

By Markus Sandy at 06/02/2007 - 09:00
Tags:

I have been manually cleaning out the sessions table for some time now. We get about 8000 records per hour and so it does not take long to reach several hundred thousand records. Things start to bog down then. The system becomes very slow, especially if we are displaying the "Who's online" block on the home page, which is something we like to do. Also, the query to delete old session records can take quite a while to perform (up to 18 minutes that I have seen).

Now there is a setting in the sites/default/settings.php to control cleanup of session records, but this setting applies to all sessions and we like to leave the ones tied to authorized (logged in) users.

There are various Drupal.org threads on this. For example,

http://lists.drupal.org/pipermail/support/2006-July/003048.html

and

http://drupal.org/node/16217

I had been thinking about setting up a hook_cron method to do this. Then I read this page:

http://drupal.org/node/16217

and I decided to go for it. I created a new module called ourmedia for site specific tasks like this and added the following method:

<?php
function ourmedia_help($section) {
  switch ($section) {
    case "admin/modules#description":
      return t("Ourmedia site specific features");
  }
}

// clean out sessions table on a regular basis
function ourmedia_cron() {
    $now = time();
   
    $pi = variable_get("ourmedia_cron_sessions_purge_interval", 60*60);
    if (!($pi >0))
        $pi = 60 * 60;
       
    $lpt = variable_get("ourmedia_cron_sessions_last_purge_time", $now-$pi);
    if (!($lpt >0))
        $lpt = $now-$pi;
       
    watchdog("cron", "ourmedia: last purge was " . format_date($lpt, "long"));

    if (($now-$lpt) >= $pi) {
        variable_set("ourmedia_cron_sessions_last_purge_time", $now);
        db_query("DELETE FROM sessions WHERE uid=0 AND timestamp < " . ($now - $pi));
        watchdog("cron", "ourmedia: sessions purged");
    } else
        watchdog("cron", "ourmedia: waiting " . ($pi-($now-$lpt)) . " seconds (" . ($pi-($now-$lpt))/60 . " minutes)");
}
?>

To do: tie the value to the setting for the "Who's online" block or at least provide access to the defaults inside of the Drupal admin settings.

Links:

Comments and discussion: