MySQL Session Table Approach

Question

Hi all,

I am developing a multi-tenant web application using LAMP. All my user session data is currently stored in mysql with table type InnoDB.

Is there any way that I can use MEMORY (used to be HEAP) table type to store the current sessions and use the garbage collector function of the session handler to move sessions between the InnoDB (regular table) and the (in) MEMORY table ?

Also will this configuration affects in any way when i want clustering & master-slave configuration at a later stage?

Thanks in Advance, ocptime

Answer 1

Writing a custom session handler is surprisingly easy, but I think there are probably better ways to store session data than MEMORY tables.

A schema something like (lifted with changes from a previous question)

CREATE TABLE IF NOT EXISTS `session` (
  `id` char(32) NOT NULL,
  `data` varchar(20000) NOT NULL,
  `time_created` timestamp NOT NULL default '0000-00-00 00:00:00',
  `time_updated` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `time_created` (`time_created`),
  KEY `time_updated` (`time_updated`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

then you'd just have to define your session handler functions, as outlined in the link above or in this tutorial. If you wanted to save your session info during garbage collection you would just have to create a table identical to the one above using the INNODB engine and add a bit at the end of the gc() function that copies the row from the MEMORY to INNODB tables.

However MEMORY tables are have some pretty significant limits. They can't use BLOB or TEXT columns--that's why I have that ugly varchar(20000) above. They have a 16 MB maximum size. If you have a lot of users, keep a lot of state, or have problems with garbage collection you can hit that limit and crash.

A better idea is to use the memcache session handler, especially if you don't need to store session info into the distant future. I'm pretty sure memcached is faster than any RDBMS would be (even with MEMORY tables) and it scales well by design. Plus you won't have to write your own session handler functions.