MEMORY storage engine (formerly known as
HEAP) creates special-purpose tables with
contents that are stored in memory. Because the data is vulnerable
to crashes, hardware issues, or power outages, only use these tables
as temporary work areas or read-only caches for data pulled from
MEMORY Storage Engine
|Storage limits||RAM||Transactions||No||Locking granularity||Table|
|MVCC||No||Geospatial data type support||No||Geospatial indexing support||No|
|B-tree indexes||Yes||T-tree indexes||No||Hash indexes||Yes|
|Full-text search indexes||No||Clustered indexes||No||Data caches||N/A|
|Index caches||N/A||Compressed data||No||Encrypted data[a]||Yes|
|Cluster database support||No||Replication support[b]||Yes||Foreign key support||No|
|Backup / point-in-time recovery[c]||Yes||Query cache support||Yes||Update statistics for data dictionary||Yes|
[a] Implemented in the server (via encryption functions), rather than in the storage engine.
[b] Implemented in the server, rather than in the storage engine.
[c] Implemented in the server, rather than in the storage engine.
When to Use MEMORY or MySQL Cluster.
Developers looking to deploy applications that use the
MEMORY storage engine for important, highly
available, or frequently updated data should consider whether
MySQL Cluster is a better choice. A typical use case for the
MEMORY engine involves these characteristics:
Operations involving transient, non-critical data such as session management or caching. When the MySQL server halts or restarts, the data in
MEMORYtables is lost.
In-memory storage for fast access and low latency. Data volume can fit entirely in memory without causing the operating system to swap out virtual memory pages.
A read-only or read-mostly data access pattern (limited updates).
MySQL Cluster offers the same features as the
MEMORY engine with higher performance levels, and
provides additional features not available with
Row-level locking and multiple-thread operation for low contention between clients.
Scalability even with statement mixes that include writes.
Optional disk-backed operation for data durability.
Shared-nothing architecture and multiple-host operation with no single point of failure, enabling 99.999% availability.
Automatic data distribution across nodes; application developers need not craft custom sharding or partitioning solutions.
For a white paper with more detailed comparison of the
MEMORY storage engine and MySQL Cluster, see
Web Services with MySQL Cluster: An Alternative to the MySQL Memory
Storage Engine. This white paper includes a performance
study of the two technologies and a step-by-step guide describing
MEMORY users can migrate to MySQL
MEMORY performance is constrained by contention
resulting from single-thread execution and table lock overhead when
processing updates. This limits scalability when load increases,
particularly for statement mixes that include writes.
Despite the in-memory processing for
tables, they are not necessarily faster than
InnoDB tables on a busy server, for
general-purpose queries, or under a read/write workload. In
particular, the table locking involved with performing updates can
slow down concurrent usage of
MEMORY tables from
Depending on the kinds of queries performed on a
MEMORY table, you might create indexes as either
the default hash data structure (for looking up single values based
on a unique key), or a general-purpose B-tree data structure (for
all kinds of queries involving equality, inequality, or range
operators such as less than or greater than). The following sections
illustrate the syntax for creating both kinds of indexes. A common
performance issue is using the default hash indexes in workloads
where B-tree indexes are more efficient.
MEMORY storage engine associates each table
with one disk file, which stores the table definition (not the
data). The file name begins with the table name and has an extension
MEMORY tables have the following characteristics:
MEMORYtables is allocated in small blocks. Tables use 100% dynamic hashing for inserts. No overflow area or extra key space is needed. No extra space is needed for free lists. Deleted rows are put in a linked list and are reused when you insert new data into the table.
MEMORYtables also have none of the problems commonly associated with deletes plus inserts in hashed tables.
MEMORYtables use a fixed-length row-storage format. Variable-length types such as
VARCHARare stored using a fixed length.
MEMORYincludes support for
MEMORYtables are shared among all clients, just like any other non-
To create a
MEMORY table, specify the clause
ENGINE=MEMORY on the
CREATE TABLE t (i INT) ENGINE = MEMORY;
As indicated by the engine name,
are stored in memory. They use hash indexes by default, which makes
them very fast for single-value lookups, and very useful for
creating temporary tables. However, when the server shuts down, all
rows stored in
MEMORY tables are lost. The tables
themselves continue to exist because their definitions are stored in
.frm files on disk, but they are empty when the
This example shows how you might create, use, and remove a
CREATE TABLE test ENGINE=MEMORY->
SELECT ip,SUM(downloads) AS down->
FROM log_table GROUP BY ip;mysql>
SELECT COUNT(ip),AVG(down) FROM test;mysql>
DROP TABLE test;
The maximum size of
MEMORY tables is limited by
variable, which has a default value of 16MB. To enforce different
size limits for
MEMORY tables, change the value
of this variable. The value in effect for
CREATE TABLE, or a subsequent
ALTER TABLE or
TRUNCATE TABLE, is the value used for
the life of the table. A server restart also sets the maximum size
MEMORY tables to the global
max_heap_table_size value. You can
set the size for individual tables as described later in this
MEMORY storage engine supports both
BTREE indexes. You
can specify one or the other for a given index by adding a
USING clause as shown here:
CREATE TABLE lookup (id INT, INDEX USING HASH (id)) ENGINE = MEMORY; CREATE TABLE lookup (id INT, INDEX USING BTREE (id)) ENGINE = MEMORY;
For general characteristics of B-tree and hash indexes, see Section 8.3.1, “How MySQL Uses Indexes”.
MEMORY tables can have up to 64 indexes per
table, 16 columns per index and a maximum key length of 3072 bytes.
MEMORY table hash index has a high degree of
key duplication (many index entries containing the same value),
updates to the table that affect key values and all deletes are
significantly slower. The degree of this slowdown is proportional to
the degree of duplication (or, inversely proportional to the index
cardinality). You can use a
BTREE index to avoid
MEMORY tables can have nonunique keys. (This is
an uncommon feature for implementations of hash indexes.)
Columns that are indexed can contain
MEMORY table contents are stored in memory, which
is a property that
MEMORY tables share with
internal temporary tables that the server creates on the fly while
processing queries. However, the two types of tables differ in that
MEMORY tables are not subject to storage
conversion, whereas internal temporary tables are:
If an internal temporary table becomes too large, the server automatically converts it to on-disk storage, as described in Section 8.4.4, “Internal Temporary Table Use in MySQL”.
MEMORYtables are never converted to disk tables.
To populate a
MEMORY table when the MySQL server
starts, you can use the
option. For example, you can put statements such as
INSERT INTO ...
DATA INFILE into this file to load the table from a
persistent data source. See Section 5.1.3, “Server Command Options”, and
Section 13.2.6, “LOAD DATA INFILE Syntax”.
MEMORY tables become empty when it is
shut down and restarted. If the server is a replication master, its
slaves are not aware that these tables have become empty, so you see
out-of-date content if you select data from the tables on the
slaves. To synchronize master and slave
tables, when a
MEMORY table is used on a master
for the first time since it was started, a
DELETE statement is written to the
master's binary log, to empty the table on the slaves also. The
slave still has outdated data in the table during the interval
between the master's restart and its first use of the table. To
avoid this interval when a direct query to the slave could return
stale data, use the
option to populate the
MEMORY table on the master
The server needs sufficient memory to maintain all
MEMORY tables that are in use at the same time.
Memory is not reclaimed if you delete individual rows from a
MEMORY table. Memory is reclaimed only when the
entire table is deleted. Memory that was previously used for deleted
rows is re-used for new rows within the same table. To free all the
memory used by a
MEMORY table when you no longer
require its contents, execute
TRUNCATE TABLE to remove all rows,
or remove the table altogether using
TABLE. To free up the memory used by deleted rows, use
ALTER TABLE ENGINE=MEMORY to force a table
The memory needed for one row in a
is calculated using the following expression:
max_length_of_key+ sizeof(char*) * 4) + SUM_OVER_ALL_HASH_KEYS(sizeof(char*) * 2) + ALIGN(
ALIGN() represents a round-up factor to cause the
row length to be an exact multiple of the
sizeof(char*) is 4 on 32-bit
machines and 8 on 64-bit machines.
As mentioned earlier, the
max_heap_table_size system variable
sets the limit on the maximum size of
tables. To control the maximum size for individual tables, set the
session value of this variable before creating each table. (Do not
change the global
max_heap_table_size value unless
you intend the value to be used for
created by all clients.) The following example creates two
MEMORY tables, with a maximum size of 1MB and
SET max_heap_table_size = 1024*1024;Query OK, 0 rows affected (0.00 sec) mysql>
CREATE TABLE t1 (id INT, UNIQUE(id)) ENGINE = MEMORY;Query OK, 0 rows affected (0.01 sec) mysql>
SET max_heap_table_size = 1024*1024*2;Query OK, 0 rows affected (0.00 sec) mysql>
CREATE TABLE t2 (id INT, UNIQUE(id)) ENGINE = MEMORY;Query OK, 0 rows affected (0.00 sec)
Both tables revert to the server's global
max_heap_table_size value if the
You can also specify a
MAX_ROWS table option in
CREATE TABLE statements for
MEMORY tables to provide a hint about the number
of rows you plan to store in them. This does not enable the table to
grow beyond the
value, which still acts as a constraint on maximum table size. For
maximum flexibility in being able to use
max_heap_table_size at least as
high as the value to which you want each
table to be able to grow.
A forum dedicated to the
MEMORY storage engine is
available at http://forums.mysql.com/list.php?92.