Shrink Your Drupal Sessions Table

While figuring out the problems we’ve been having at the site we noticed how huge the sessions table was getting, averaging over 35,000 rows at any given time.   The size of the sessions table is due to the Drupal default settings in SETTINGS.PHP, which looks (in part) something like this:

ini_set('session.cache_expire',     200000);
ini_set('session.cache_limiter',    'none');
ini_set('session.cookie_lifetime',  2000000);
ini_set('session.gc_maxlifetime',   200000);

With these default settings, your sessions wont expire for many, many minutes (I tried to calculate it, but I only have 10 fingers).  UNexpired sessions means two things:

  1.  Your sessions table will get fat
  2.  Users will remain logged-in for days after exiting from their browser

Having a fat sessions table isn’t necessarily a bad thing, but in my case we’ve been having troubles with DUPLICATE ENTRY INSERT INTO SESSIONS table errors that were crippling the site.  It also can be a bit of a security issue for our users because it means they remain logged in after leaving the site.  This can be especially troublesome if they are using a shared or public computer.

How we shrank our SESSIONS table:

It’s actually pretty easy.  Find your SETTINGS.PHP file – probably located in your \sites\default directory and change those “2000000” (shown in our example above) to a smaller value (after you back it up!)  This is how ours looked after we changed it:

ini_set('session.cache_expire', 2700);
ini_set('session.cache_limiter', 'none');
ini_set('session.cookie_lifetime', 0);
ini_set('session.gc_maxlifetime', 2700);

Save the file back to your server, and the changes take effect immediately.

Now, users are logged out immediately when they close their browser and sessions are  only saved for 45 minutes.  After making this change, our sessions table went from having over 30,000 rows to only a few hundred.

Shrink Your Drupal Sessions Table by

5 thoughts on “Shrink Your Drupal Sessions Table

  1. [quote comment=”2072″]Hi,

    I tried your solution, but after a while (maybe two days), session table grows again…

    No it’s more than 9000 rows.
    [/quote]

    That’s strange Alessandro; are you sure you made the session expire-time short enough?
    The changes above worked for me – i’m wondering if maybe your settings are being overridden somwhere like in your HTACCESS file or elswhere.

  2. Hello Randy,
    Great tips. Our session table is also a runaway. Heavy hitter like the Google spiders do the most damage. The two of them are responsible for over 100,000 records. With each hit, they spawn a new session and rack them up with lightning speed.

    Great site, BTW, Randy.

    – Mike

  3. [quote comment=”2182″]Hello Randy,
    Great tips. Our session table is also a runaway. Heavy hitter like the Google spiders do the most damage. The two of them are responsible for over 100,000 records. With each hit, they spawn a new session and rack them up with lightning speed.

    Great site, BTW, Randy.

    – Mike[/quote]

    After some more digging, we found our core problem: Debian/Ubuntu doesn’t have garbage collection set-up by default in the apt-get version of PHP5. We changed it (see below) and voila– session management.


    session.gc_probability = 1
    session.gc_divisor = 100

  4. Above you write:
    ini_set(‘session.cache_expire’, 2700); // this is in minutes and so = just under 2 days
    ini_set(‘session.gc_maxlifetime’, 2700); // this is in seconds and so = 45 mins
    And then you write: sessions are only saved for 45 minutes. Thought readers should realize the differences between ‘expire’ and ‘gc_maxlifetime’

Leave a Reply

Your email address will not be published.