Leveraging MariaDB’s MEMORY Storage Engine: Efficient Caching and Performance Optimization

Redis is mainly used as an application cache or a quick-response database. But wait, you can always create a cache in a relational database as follows:

CREATE TABLE cache(
    ´key´ VARCHAR(64) PRIMARY KEY,
    value VARCHAR(255) NOT NULL,
    last_updated TIMESTAMP
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP
);

Moreover, with MariaDB, you can pick one from the many available storage engines. For example, if you want to store the previous cache table in memory, simply use the MEMORY storage engine:

CREATE TABLE cache(
    ...
) ENGINE=MEMORY;

When you configure the cache table to use the MEMORY storage engine, its data will reside entirely in RAM. This is interestingly similar to how Redis operates, keeping data in memory for low-latency access. This looks great and it definitely has its benefits. However, there are a few nuances that are worth exploring.

Example Use Case

Let’s say you have a web application that needs to track session IDs. Using a MariaDB MEMORY table sounds like a good idea here—there’s potential for reducing the load on your primary databases and improving response times for your users. Here's how you could implement such a cache using MariaDB’s MEMORY storage engine:

CREATE OR REPLACE TABLE users_cache (
	user_name VARCHAR(50) NOT NULL PRIMARY KEY REFERENCES users(id),
	session_id VARCHAR(50),
	last_updated DATETIME NOT NULL
		DEFAULT CURRENT_TIMESTAMP
		ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MEMORY;

Insertion into this table would typically occur whenever a user logs in. But generalizing a bit more, insertion in a cache could happen every time you read data stored on disk-based tables. We’ll use the latter approach and assume that permanently storing session IDs is a business requirement so that we can do more experiments. In any case, you can set up a background job to refresh this cache periodically or invalidate it when the underlying data changes.

Cache Invalidation and Management

There are several ways to handle cache invalidation with MariaDB. For example, you can set a limited row-based lifespan (through a column for expiration time) and use event schedulers to clear or update cached data at fixed intervals. Here’s an example:

CREATE OR REPLACE EVENT ev_remove_stale_user_cache_entries
    ON SCHEDULE EVERY 20 MINUTE DO
		DELETE FROM users_cache
		WHERE NOW() > last_updated + INTERVAL 2 HOUR;

For testing, you can use different intervals. For example EVERY 1 SECOND and INTERVAL 20 SECOND. Also, remember to enable MariaDB’s event scheduler by setting the event_scheduler configuration property or, for testing, by running:

SET GLOBAL event_scheduler = ON;

If you want to try this out, you can find a complete example on GitHub. Or watch the short video demoing the example in action.

Pros and cons

Although using the MEMORY storage engine can speed up data retrieval times, as always, this depends on the exact use case—you should test this configuration with your applications before making decisions. In particular, it’s important to be aware that MEMORY performs table-wide locks. This means that it might not be well-suited when you need to update the cache more frequently than you read it. Or in other words, using the MEMORY storage engine is a good option for data that needs to be accessed frequently and updated infrequently.

A key advantage of using the MEMORY engine is when your app needs to mix cache data with data in tables of a relational database, for example, during a single HTTP request. Imagine an app that processes user information updates. Each update might involve writing to a cache and simultaneously updating a relational record. This would require two different accesses to two different databases. With MariaDB, you can handle this in a single database using SQL. This eliminates the overhead and complexity of managing separate data stores and coordinating between them. Here’s a simplified example of how such an operation could look:

SET @data = "other data";
UPDATE users SET data = @data WHERE id = 123;
REPLACE INTO users_cache (user_id, data) VALUES (123, @data);

In this example, the user's data columns in both the users and the user_cache tables are updated in a single call to the database. Keep in mind that the MEMORY storage engine is not transactional, which is less important when you compare it with a cache that lives in a completely different database technology than your operational database anyway.

An additional and obvious but important advantage of using the MEMORY engine is that you can remove persistence-polyglot logic from your app. If your team is already familiar with SQL, MariaDB provides a seamless experience without the need to learn new syntax or juggle another technology stack.

How Does It Stack Up Against Redis?

While Redis is a powerful tool for handling simple data structures like strings, hashes, lists, sets, and sorted sets directly in-memory, MariaDB's MEMORY engine handles complex queries more naturally because it supports the full power of SQL and relational database systems. This means you can perform joins, subqueries, and even complex transactions, which are not as straightforward in Redis.

Now, there’s also the question of scalability. Especially horizontal scalability. This involves adding more nodes to a system to distribute load and increase capacity without interrupting service. Both Redis and MariaDB offer robust solutions, but their mechanisms are different.

Redis achieves horizontal scalability primarily through sharding, where data is partitioned across multiple Redis instances. This can be configured manually or managed via Redis Cluster, which handles sharding and provides high availability through failover and replication processes. Redis Cluster supports up to 1000 nodes, which allows it to scale massively. This model is particularly effective for applications requiring ultra-fast operations and high throughput on simple data structures.

MariaDB offers a somewhat similar approach to scalability. And you probably guessed it—this is achieved through a storage engine. The Spider engine partitions table data across multiple MariaDB nodes, treating them as one logical entity. This enables querying and updating data across various physical servers seamlessly as if they were on a single local server. The Spider engine supports SQL and transactional data operations so you can run complex queries when you need. It’s useful for large database environments where data distribution is essential for performance and management to meet the demands of large-scale applications.

Durability and Persistence

One difference to consider is that the MEMORY storage engine in MariaDB does not offer data persistence after the server restarts. Data stored in MEMORY tables is volatile; it's cleared when the database restarts, much like Redis in its default configuration. If persistence is crucial, you might consider using MariaDB's Aria or InnoDB engines for caching. In fact, InnoDB has excellent performance thanks to its cache mechanism, which reduces the load on primary nodes.

Final Thoughts

Switching from Redis to MariaDB for caching might not suit every project, but it's a viable option for those looking to streamline their technology stack or leverage their existing SQL expertise. It provides an easy way to implement caching solutions with tools you already know and reduces the overhead of managing additional systems. Plus, for those looking for a middle ground, MariaDB can also serve as a complementary caching layer alongside Redis, taking advantage of both systems' strengths. Moreover, you can leverage Redis as a cache for MariaDB MaxScale.

Try MariaDB and set up your own in-memory cache with the MEMORY storage engine. Experience how it fits with your existing SQL knowledge. I have created a simple plain-text file with detailed instructions and code that you can run to see a cache in action using only SQL! So all you need is to connect to your MariaDB server (spin one up quickly using Docker if you don’t have one running already) and run the commands in any SQL client compatible with MariaDB (most of them are). Here you can see the demo in action:

If you have questions or want to share your experience, don’t hesitate to join the MariaDB Community Slack and let us know!