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.

Configuration:

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;

Use cases:

  1. Non-Critical Read-Only and Read-Mostly Data
  2. Caching
  3. Session

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.

Limitations:

There are many limitations in Memory engine, most of these are okay in case of listed use cases above.

  1. No row-level locking
  2. No FKs
  3. No Transactions
  4. Clustering (No Scalability)
  5. Geospatial Data or Geospatial indexes
Choosing MySQL Memory Engine for Session/Caching

10 thoughts on “Choosing MySQL Memory Engine for Session/Caching

  1. This strategy is fine for always running frameworks like Rails but it sucks balls with Laravel. I end up creating multiple commands to kill old cache.

    1. Yes – without any issues. In fact, it was used in a bidding platform where information is stored in memory for a few hours – just 10 mins from bidding point and binding only lasts for about 12 hours.

Leave a Reply to S Samuel Cancel reply

Your email address will not be published. Required fields are marked *

Scroll to top