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:
- Your sessions table will get fat
- 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.
Like
Hi,
I tried your solution, but after a while (maybe two days), session table grows again…
No it’s more than 9000 rows.
Have you found another solution other than changing settings.php?
Many thanks
Alessandro Bonelli said:
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.
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
Mike DeWolfe said:
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