MySQL Session Table Approach
innodb, memory, mysql, php, session
Solution
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.
Problem
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