Clearing out Rails Sessions 19 Nov 09

As I’m sure you’re aware, Rails can be told to use ActiveRecord (and hence the MySQL database) to store session data. (New Rails apps use the cookie store by default—See the rails sessions guide for more info on both.)

However, this session data is never deleted, which means your session table continues to grow and grow forevermore. Your old sessions are left stored in the database, and although the table is indexed to help with finding sessions, it will eventually fill the disk up.

At this point you might be thinking the solution is obvious, just empty the sessions table after so long and let it fill up again. Rails even provides a rake task that does this for us, rake db:sessions:clear. The problem with taking this approach is that any active sessions get lost as well, which could be people with items in their baskets, currently logged in users, etc.

There is another solution, which is to only delete sessions that we consider to no longer be active. The updated_at column in the sessions table has an index on it, and thus looks designed for this type of query to be run. On the Brightbox control panel, we’ve decided this is session data that hasn’t been updated for over 24 hours. (A side effect of this is customers that haven’t visited the control panel in the last 24 hours are logged out. We’ve decided this is ok, but YMMV of course.)

We use the following rake task that clears out sessions 24 hours or older, which is run via cron at 3am every morning. You can change the threshold by editing "1 DAY" in the query, see the MySQL DATE_ADD() docs for valid values.

desc "Clear expired sessions"
task :clear_expired_sessions => :environment do
    sql = 'DELETE FROM sessions WHERE updated_at < DATE_SUB(NOW(), INTERVAL 1 DAY);'
    ActiveRecord::Base.connection.execute(sql)
end

As you have deleted quite a large number of rows, the sessions table may now be fragmented and affect performance. Fragmentation also means the table may hold a piece of the disk larger than it should for the number of rows it holds.

De-fragmenting the table does introduce locks, so we suggest running this when your site is quiet. Though, it is most likely the command below will only take 1 or 2 seconds to complete.

To de-fragment your table login to your Brightbox, and use the MySQL client to connect to your database. You can find out your database server, and password from the left hand menu item “MySQL” in your Control Panel.

mysql -h mysqlserver -u username -p

Select the database which holds the sessions table.

use database_name;

Then run the optimize command to de-fragment the table.

optimize table sessions;

You should now have a smaller, and faster sessions table.