# MEMORY

Contents of the MEMORY storage engine (previously known as HEAP) are stored in memory rather than on disk.

It is best-used for read-only caches of data from other tables, or for temporary work areas.

Since the data is stored in memory, it is highly vulnerable to power outages or hardware failure, and is unsuitable for permanent data storage. In fact, after a server restart, `MEMORY` tables are recreated (because the definition file is stored on disk), but they are empty. It is possible to re-populate them with a query using the `--init-file` server startup option.

Variable-length types like [VARCHAR](/docs/server/reference/data-types/string-data-types/varchar.md) can be used in MEMORY tables. [BLOB](/docs/server/reference/data-types/string-data-types/blob.md) or [TEXT](/docs/server/reference/data-types/string-data-types/text.md) columns are not supported for MEMORY tables.

## Memory Usage

The maximum total size of MEMORY tables cannot exceed the [max\_heap\_table\_size](/docs/server/server-management/variables-and-modes/server-system-variables.md#max_heap_table_size) system server variable. When a table is created this value applies to that table, and when the server is restarted this value applies to existing tables. Changing this value has no effect on existing tables. However, executing a `ALTER TABLE ... ENGINE=MEMORY` statement applies the current value of `max_heap_table_size` to the table. Also, it is possible to change the session value of `max_heap_table_size` before creating a table, to make sure that tables created by other sessions are not affected.

The `MAX_ROWS` table option provides a hint about the number of rows you plan to store in them. This is not a hard limit that cannot be exceeded, and does not allow to exceed `max_heap_table_size`. The storage engine uses max\_heap\_table\_size and MAX\_ROWS to calculate the maximum memory that could be allocated for the table.

Memory allocated to a MEMORY table is freed by running [DROP TABLE](/docs/server/server-usage/tables/drop-table.md) or [TRUNCATE TABLE](/docs/server/reference/sql-statements/table-statements/truncate-table.md), or rebuilding with [ALTER TABLE tbl\_name ENGINE = MEMORY](/docs/server/reference/sql-statements/data-definition/alter/alter-table.md). When rows are deleted, space is not automatically freed.

## Replication

The data in MEMORY tables are lost when a server restarts. To reflect this result in [replication](/docs/server/ha-and-performance/standard-replication.md), the first time a server uses a MEMORY table after a restart, it writes a TRUNCATE statement for that table with a “generated by server” comment to the binary log, so its replicas are also emptied.

{% hint style="warning" %}
In non-circular replication, restarting a replica will diverge from its primaries in both MEMORY table data and [GTID](/docs/server/ha-and-performance/standard-replication/gtid.md). The contents and replication must be resynchronized to fix this issue. Alternatively, if consistency is not required (e.g., for cache data), then the replica should have a different Domain ID than its primaries to satisfy [GTID strict mode](/docs/server/ha-and-performance/standard-replication/gtid.md#gtid_strict_mode).

If the MEMORY tables are not required for replication at all, then [excluding them from replication](/docs/server/ha-and-performance/standard-replication/replication-filters.md) avoids this problem entirely.
{% endhint %}

## Index Type

The MEMORY storage engine permits indexes to be either B-tree or Hash. Hash is the default type for MEMORY. See [Storage Engine index types](/docs/server/ha-and-performance/optimization-and-tuning/optimization-and-indexes/storage-engine-index-types.md) for more on their characteristics.

A MEMORY table can have up to 64 indexes, 16 columns for each index and a maximum key length of 3072 bytes.

## Example

The following example shows how to create a `MEMORY` table with a given maximum size, as described above.

```sql
SET max_heap_table_size = 1024*516;

CREATE TABLE t (a VARCHAR(10), b INT) ENGINE = MEMORY;

SET max_heap_table_size = @@max_heap_table_size;
```

## See Also

* [Performance of MEMORY tables](/docs/server/reference/product-development/server-development/quality/benchmarks-and-long-running-tests/benchmarks/performance-of-memory-tables.md)

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/server-usage/storage-engines/memory-storage-engine.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.