InnoDB or MyISAM for sessions?

2018-06-18 12:35:35

I'm using myisam tables for a website that i'm building, since it's going to be mostly read-only. And that is why i think myisam will be better. But i'm storing user sessions in a database ... so that means a select + update to the sessions table on every single page request. So with sessions i now have 1/1 reads/writes on the table and that could go higher on the writes if i need to update something on the session. Should i use innodb for such a table? Or is 1/1 read/write ratio still something that myisam has no problem with? The application will not have high traffic (although i'm not even sure what defines high traffic for this situation)

MyISAM is often faster than InnoDB in terms of raw performance (mostly because it is not ACID). Therefore accessing MyISAM consumes less resources than InnoDB.

On the other hand, MyISAM only supports table-level locking: in a highly concurrent environment, latency increases. A few dozen simple queries shouldn't cause too much trouble, however (assuming most of your queries would be a straightforward SELECT session_data FROM session_table WHERE session_id = <some_id> ).

Conversely, InnoDB offers more robustness: it is virtually impossible for an InnoDB table to get corrupted, and the difference in terms of performance tends to get less and less significant (eg. see this benchmark). Some would even argue that there is little reason to keep using MyISAM nowadays (InnoDB became the default storage engine in v5.5).

I am sorry for not providing a more definitive answer as to which is "faster". As always when it comes to performance optimisation, real-life tests must be carried out. Keeping in mind that you can switch engines very easily ( ALTER TABLE t ENGINE=[MyISAM | InnoDB] ), I suggest to try and see.

But considering your expected traffic, using the one or the other shouldn't make too big a difference.

You had many options and Mysql isn't the best one:

Save on disc its good options if you had hardware Raid, not good for replication.

Mysql - yes and no i used previously Mysql - Myisam and on 50 users table crash. So if you need you can use "MEMORY (HEAP)" table

Some language like PHP+extension had own Session storage eq WinCache Session Handler - Manual