Optimized Version of Rails MySQL Session Store is Available

The Problem

Original ActiveRecord sessions store is slow. It is fine for some low traffic sites, but it is too slow to use it for something larger. First of all, it is slow because ActiveRecord itself is slow. It is powerful ORM framework, but it is obviously overkill for such a simple task as a sessions management.

There are other possible solutions like cookie session store (session size is limited, no sensible data in sessions), memcached (no persistence + harder to implement high-available solutions).

That is why SqlSession store was created. It works with mysql directly with database APIs and works much faster than original AR session store. But sometimes it is still slow too because:

• it creates/updates session on each hit – every bot or occasional web serfer create session records in your database so you end up with thousands of thousands of useless records in your sessions table while 99% of hits do not require any session updates.
• it uses 32-characters string as a key for sessions records – all RDBMS work with string keys MUCH slower than with integers, so it would be much better to use integers, but we have these long session IDs and all session stores use them as a table key.
• it uses auto_increment primary key, which causes table-level locks in InnoDB for all MySQL versions prior to 5.1.21. These table-level locks with unnecessary inserts cause really weird problems for large sites.

The Solution

As a solution for mentioned problems FastSessions Rails plugin was born.

So, first of all, we removed id field from the sessions table, so we do not need to use auto-increment locks. Next step was to make lookups faster and we’ve used a following technique: instead of using (session_id) as a lookup key, we’ve started using (CRC32(session_id), session_id) – two-columns key which really helps MySQL to find sessions faster because key cardinality is higher (so, mysql is able to find a record earlier w/o checking a lots of index rows). We’ve benchmarked this approach and it shows 10-15% performance gain on large sessions tables.

And last, but most powerful optimization was to not create database records for empty sessions and to not save sessions data back to the database if this data has not been changed during a request processing. With this change we basically reduce inserts number by 50-90% (depends on application).

So, you must be wondering, how faster your sessions could be with this plugin? The answer is tricky. The result will depend on how do you work with sessions: if you change sessions data rarely (like saving user id on login), then you could possibly get up to 90% performance gain, but if on each hit you write something new to your user’s session (like last_visit_time or something like that), then your performance gain will be 5-15% depending on server load and sessions table size).

All of these changes were implemented and you can use them automatically after a simple plugin installation.

When we were solving AUTO_INC locks problem and removed our auto-increment key, we’ve introduced one problem I’d like to describe here. The problem is following. InnoDB groups all data in tables by primary key. This means that when we use auto increment primary key and insert records to a table, our sessions records are grouped together and saved sequentially on the disk. But if we’ll make pretty random value (like crc32 of a random session id) a primary key, then every session record will be inserted in its own place and it will generate some random I/O which is not so good for I/O bound servers. So, we decided to let the user choose what primary key to use in his deployment of our plugin, so if you’re going to use this module with MySQL 5.1.22+, then you’d like to set

CGI::Session::ActiveRecordStore::FastSessions.use_auto_increment = true

because it will provide you with consecutive data inserts in InnoDB. Another cases when you’d like to use it is when your MySQL server is I/O bound now and you do not want to add random I/O because of randomized primary key.

If you do not like to loose old sessions created with default AR sessions plugin, you could set

CGI::Session::ActiveRecordStore::FastSessions.fallback_to_old_table = true

and then all session reads will fall back to old sessions table if some session_id was not found in default fast sessions table. Old sessions table name could be set using CGI::Session::ActiveRecordStore::FastSessions.old_table_name variable.

This option make sessions slower so I’d recommend to use it during transition to new sessions table only. In this case you’ll save your new sessions to the new table and will be able to drop old sessions table when your session expiration time will be over (we use 2 month period so in 2 months we’ll be able to drop this old table and turn this option off).

Installation

FastSessions plugin installation is pretty simple and described in a few steps below:

1. Install this plugin sources in your vendor/plugins directory (it could be ./script/plugin install, or piston import command – it is up to you) from our SVN reposipory. For example:
   
$ piston import http://rails-fast-sessions.googlecode.com/svn/trunk/ vendor/plugins/fast_sessions

2. Enable ActiveRecord session store in your config/environment.rb file:
   
Rails::Initializer.run do |config|
......
config.action_controller.session_store = :active_record_store
......
end

3. Create migration for your new sessions table:
   
$ ./script/generate fast_session_migration AddFastSessions

4. Open your newly created migration and change table_name and use_auto_increment parameters of the plugin (if you want to).
5. Run your migration:
   
$ rake db:migrate

6. Start your application and try to perform some actions which would definitely save some data to your session. Then check your fast_sessions table (if you did not renamed it) for a records.

Download

Most recent version if this plugin could be found on the project’s site or in SVN repository.This plugin has been created by Alexey Kovyrin (MySQL performance expert in Percona). Development is sponsored by Scribd.com.