MySQL Session Table Approach

Question

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

Solution 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.

Solution 2

InnoDB: ~40 milliseconds Cons: (Slowest)
MEMORY: ~22 milliseconds Cons: (16MB Max)
MyISAM: ~25 milliseconds Cons: (Table-level locking)

---

I'm using (gs), so I can't do memcache, but that would be best.

I personally was considering using MEMORY, but I didn't know if the performance benefit would outway the cost of size limits etc. So I did what every good programmer should do when optimizing: I profiled it.

My php page in question is cached in smarty, so the only operations happening here are a regex lookup on the url and a sql session grab to check if the user is logged in.

Anyway here are the results: With InnoDB I was getting ~40 milliseconds waiting on a request. (I measured this in chrome with the developer tools.) After switching the table to MEMORY I got ~20 milliseconds per request. wow! 50% improvement! But wait... what about MyISAM? I tried that and I got ~22-23 milliseconds per request. Oh.

This is just me testing it, not a full scale application. A real application would have thousands of people writing to that table every second, and MyISAM does table-level locking, which could be bad (Which MySQL database engine is better for storing sessions and session data: MyISAM or InnoDB?).

So I'm sticking to memory, for now. It's a vast improvement for those cached pages, but I encourage you to profile! If your website is rebuilding every page, the 10 milliseconds might not really matter.

Comments

[Max Tsepkov]

It is possible to increase maximum table size by adjusting max_heap_table_size system variable.

[Lightbulb1]

How would table level locking affect the performance on a larger scale application. Would it effectively block users when someone tries to write to the session like on a login?

[Andy Chase]

@Lightbulb1, every page view has to update the session table on the database to update the last accessed time (to know when to expire the session and when the sessions are free to be garbage collected). If two people get a page at the same exact time then one person has to wait until the other person's write is finished because Mysql blocks until the write is complete (unless you do a LOW_PRIORITY update, which would fix the problem). This is all really just premature optimization though. If your site is that busy consider memcached.

[Lightbulb1]

Thank you for the information. I've not used memcache before so its something that i need to do more research on. It properly will be the solution for the future.