MEMORY Engine, Ephemeral Data, Limits, and Safer Alternatives

Learning outcomes

A developer proposes a MEMORY table for login tokens because “RAM is faster.” The test passes—until the server restarts and every token row disappears. The table definition still exists, which makes the failure more deceptive: SQL succeeds after restart, but the data is gone by design.

MEMORY is a server-side storage engine whose row data lives in process memory. It is useful for deliberately ephemeral, reconstructable data and some working sets, not as a durability shortcut. It also has table-level locking, engine-specific index choices and size/type constraints, so “in RAM” does not mean “unbounded” or “best for concurrency.”

1. Verify MEMORY capabilities before using it

SELECT ENGINE,TRANSACTIONS,XA,SAVEPOINTSFROM information_schema.ENGINESWHERE ENGINE='MEMORY';SHOW VARIABLES LIKE 'max_heap_table_size';SHOW VARIABLES LIKE 'tmp_table_size';

max_heap_table_size limits a user-created MEMORY table, but it is captured for the table when the table is created or rebuilt; changing the variable later does not retroactively resize every existing MEMORY table. Also do not confuse user-created MEMORY tables with MariaDB’s internal temporary-table strategy: tmp_table_size participates in a different execution-memory boundary.

2. Build an explicitly rebuildable cache

DROP DATABASE IF EXISTS servicehub_engines_lab;CREATE DATABASE servicehub_engines_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE servicehub_engines_lab;CREATE TABLE technicians (  technician_id BIGINT NOT NULL PRIMARY KEY,  display_name VARCHAR(80) NOT NULL,  active BOOLEAN NOT NULL DEFAULT TRUE) ENGINE=InnoDB;INSERT INTO technicians VALUES (1,'Ava Chen',1),(2,'Mina Patel',1),(3,'Noah Smith',1);SET SESSION max_heap_table_size=4*1024*1024;CREATE TABLE technician_presence (  technician_id BIGINT NOT NULL PRIMARY KEY,  last_seen DATETIME(6) NOT NULL,  state ENUM('available','busy','offline') NOT NULL,  KEY ix_state USING BTREE (state,last_seen)) ENGINE=MEMORY;INSERT INTO technician_presence VALUES (1,NOW(6),'available'),(2,NOW(6),'busy'),(3,NOW(6),'offline');SHOW CREATE TABLE technician_presence\GSHOW TABLE STATUS LIKE 'technician_presence'\GSELECT * FROM technician_presence ORDER BY technician_id;

This table is safe only because the canonical technician records remain in InnoDB and presence can be reconstructed from application heartbeats. If ServiceHub instead stored billing adjustments or audit evidence here, the design would be invalid regardless of how fast reads appear.

3. HASH versus BTREE and data-type constraints

MEMORY defaults to HASH indexes for many definitions, which can be efficient for equality lookups but cannot provide the ordered/range behavior of a B-tree. You can request USING BTREE for indexes that need range/order semantics. MEMORY allows variable-length types such as VARCHAR in current MariaDB but does not support BLOB/TEXT columns.

CREATE TABLE memory_index_demo (  k INT NOT NULL,  created_at DATETIME NOT NULL,  payload VARCHAR(80) NOT NULL,  PRIMARY KEY USING HASH (k),  KEY ix_created USING BTREE (created_at)) ENGINE=MEMORY;SHOW INDEX FROM memory_index_demo;

Index choice still needs workload evidence. A hash key is not automatically better for equality queries once the whole application, lock contention and maintenance cost are considered. Use EXPLAIN on real statements and verify observed latency/concurrency.

4. Mandatory restart proof—only on a disposable local server

SELECT COUNT(*) AS rows_before_restart FROM technician_presence;-- expected: 3

# Run in your host shell only if MariaDB is in a disposable container:docker restart mariadb-lab# Reconnect with the mariadb client, then run:USE servicehub_engines_lab;SELECT COUNT(*) AS rows_after_restart FROM technician_presence;-- expected: 0SHOW CREATE TABLE technician_presence\G

Native-service restart commands differ across Windows, systemd distributions and package names, so the course does not prescribe one universal command. The invariant is the same: after a server restart, MariaDB recreates the MEMORY table from its persistent definition but the in-memory rows are gone. If you cannot safely restart your local instance, read the sequence and reproduce it later on a dedicated container.

5. Replication does not magically make MEMORY durable

MEMORY tables have special replication behavior because restart empties them locally. MariaDB documents that the first use of a MEMORY table on a primary after restart causes a delete to be written so replicas can be emptied correspondingly. That synchronization rule does not turn the data into durable storage, and failover to a replica can still expose assumptions about rebuild timing and application state.

If the data must survive server loss, put the authoritative record in a durable system. A cache can be repopulated from InnoDB, an event stream or another source of truth. High availability of ephemeral state is a separate design problem from durable database storage.

6. Deliberately wrong: use MEMORY for permanent security tokens

The failure mode is not merely “rows vanish on restart.” It also affects incident response and recovery: a backup cannot restore row contents that were never durable, and a failover/restart can invalidate application assumptions at once. The repair is to store durable token/session records in InnoDB when persistence is required, or to define the cache as explicitly reconstructable/expirable and make application behavior correct when the cache is empty.

7. Checkpoint and cleanup

Lesson 4 moves from ephemeral local memory to the opposite extreme: tables whose rows may actually live in external files or remote DBMSs through the optional CONNECT engine.

7. MEMORY consumes a shared server budget and still needs concurrency testing

A MEMORY table competes with the rest of mariadbd for host RAM. That makes capacity planning different from a dedicated application cache with its own process/container limit. A table limit such as max_heap_table_size bounds individual table growth, but several MEMORY tables plus connection buffers, InnoDB buffer pool, temporary work and operating-system cache can still pressure the host. Measure total process/host memory instead of concluding that a table is safe because it has not hit its own limit.

Deletion also deserves attention: MariaDB documents that memory allocated to a MEMORY table is released by DROP TABLE, TRUNCATE TABLE or rebuilding the table; deleting individual rows does not necessarily return that memory to the operating system immediately. A high-churn cache can therefore have a very different steady-state footprint from a one-time load test.

Concurrency is another reason to test rather than assume. MEMORY uses table-level locking. A tiny read-mostly lookup may behave very well, while a hot shared queue with many writers can serialize and create latency spikes. Run representative concurrent read/write tests before accepting the engine. If the workload needs durable state, fine-grained concurrent updates, eviction/TTL policies, cluster-wide availability or predictable memory isolation, a different boundary is usually clearer.

8. Rebuild design is part of the cache contract

If a MEMORY table is acceptable, document exactly how it is repopulated after restart. For ServiceHub, that might be a startup job that derives current technician presence from recent heartbeat events, or the application may simply tolerate an empty cache and refill entries lazily. The rebuild path needs its own correctness test: duplicate inserts, concurrent refill, stale source data and partial failure should not turn a disposable cache into an outage. A cache that cannot be safely rebuilt is not really disposable.

9. Measure cache misses at the application boundary too

Database counters alone cannot tell you whether an empty MEMORY cache harms users. Record cache-hit/miss behavior in the application, rebuild duration, database CPU and request latency before and after a restart. If a cold restart creates an unacceptable request storm against the durable source, add bounded warmup, backpressure or lazy refill logic rather than increasing the MEMORY table limit blindly.

Authoritative references

• MariaDB Documentation — MEMORY Storage Engine
• MariaDB Documentation — MariaDB Memory Allocation