Best MySQL storage engine to use for PHP session storage

Question

I want to use MySQL to store session variables. From what I understand this means that on every page request there will be one read and one write to the table.

Which MySQL storage engine is best suited for this task? MyISAM, InnoDB , MariaDB (which I don't see in PHPMyAdmin), Memory, or something else entirely?

Evetta

Answer 1

"Best" means nothing. You need to express your constraints: do you need consistency? Durability? High-availability? Performance? A combination of all these properties? Can you afford to loose your sessions? Can they fit in memory? Do you need to support concurrent accesses to the same data?

Without more context, I would choose InnoDB which is the most balanced storage engine. It provides correct performance for OLTP applications, ACID transactions, good reliability, and sensible concurrency management. Session variables access will likely be done using primary keys, and this operation is very efficient with InnoDB.

Now if performance is really a constraint, I would rather use a NoSQL engine (i.e. not MySQL). To store session data, Redis usually does a very good job, and is easy enough to integrate and deploy.

Rodarte

Answer 2

Memory storage engine sounds to be the best option. Keep in mind that this is good for temporary sessions.

http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html

Proserpina

Won't this be a problem if I have a load balancer as the engine doesn't write the data to disk and the servers don't share memory? Also, If the server crashes or is restarted will I lose all of my data? – Evetta

Answer 3

It depends on how you evaluate "betterness":

MyISAM is the most common (many shared hosting packages only let you use MyISAM). plus it is rather limited in the relationship control aspect, so you set it up really fast and easy. if you want portability and fast implementation across multiple hosting scenarios, MYISAM IS BEST.

InnoDB allows you to create relationships and saveguard data integrity by linking keys in different tables, which means more work but much more professional db design. many shared hosting packages do not implement InnoDB, therefore when exporting table structure from one environment to another, you might have some extra work to do. if you want realationship management and control, INNODB IS BEST.

As far as data portability is concerned, an InnoDB database will be completely accepted by MyISAM (because MyISAM does not check data integrity: "is there a user number 4 in the user database when i insert a new record in user_car, for example"). If you start out with MyISAM, exporting to a full-fledged InnoDB database will be a nightmare, even if your data has all keys, table data must be imported in the correct order (user and car, before user_car).

MariaDB? never, simply because less people use it, therefore you will have less support, as compared to MyISAM and InnoDB.

Bottom line clincher: INNODB.

Ninety

If you strongly discourage Maria, it might be worth saying why - otherwise that sentence isn't very useful! Would you edit your post and expand on that point a little? – Confectionery

@quertymk and halfer, followed your requests and edited. thanx! :) – Ninety

Answer 4

If you do not wan't the overhead from a SQL connection consider using MemCached session sorage. See http://php.net/manual/en/memcached.sessions.php

Corr