Choosing MySQL Memory Engine for Session/Caching
MySQL memory engine is least popular but most among most effective solution for a performance first application. Most of the ‘node’ application developers are generally spinning a Redis for having a session, while same can be achieved using memory engine of MySQL without any overhead of having a different tech – “Redis” in this case.
More overheads mean more ways to Break
It is often an “overkill” by using Redis for session/cache, in case you already have MySQL in place.
The max_heap_table_size system variable defines the limit of the maximum size of MEMORY tables. As this is dynamic variable, you can set this by following in runtime.
SET max_heap_table_size = 1024*1024;
- Non-Critical Read-Only and Read-Mostly Data
An example of Non-Critical Read-Mostly Data where we used MySQL memory storage engine other than “session” and “caching” was ‘computing” to store intermediate results. To be more specific we used it in pattern recognition of stock pricing.
There are many limitations in Memory engine, most of these are okay in case of listed use cases above.
- No row-level locking
- No FKs
- No Transactions
- Clustering (No Scalability)
- Geospatial Data or Geospatial indexes