Menu
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?
807
Storage engines are MySQL components that handle the SQL operations for different table types. InnoDB is the default and most general-purpose storage engine, and Oracle recommends using it for tables except for specialized use cases. (The CREATE TABLE statement in MySQL 8.0 creates InnoDB tables by default.)
There are two types of storage engines in MySQL: transactional and non-transactional. For MySQL 5.5 and later, the default storage engine is InnoDB. The default storage engine for MySQL prior to version 5.5 was MyISAM.
To use transactions, you must use a transaction-safe storage engine. Currently, the transactional engines include InnoDB, NDB, and BDB, and others may become available. To see which of them your MySQL server supports, check the output from the SHOW ENGINES statement: mysql> SHOW ENGINES\G *************************** 1.
InnoDB is a storage engine for MySQL that balances high reliability and high performance. As of MySQL 5.5 and later, it is the default storage engine.
"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.
65
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
35
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
