13.4. The `MEMORY` (`HEAP`) Storage Engine

The MEMORY storage engine creates tables with contents that are stored in memory. Formerly, these were known as HEAP tables. MEMORY is the preferred term, although HEAP remains supported for backward compatibility.

The MEMORY storage engine associates each table with one disk file. The file name begins with the table name and has an extension of .frm to indicate that it stores the table definition.

To specify that you want to create a MEMORY table, indicate that with an ENGINE table option:

CREATE TABLE t (i INT) ENGINE = MEMORY;

The older term TYPE is supported as a synonym for ENGINE for backward compatibility, but ENGINE is the preferred term and TYPE is deprecated.

As indicated by the engine name, MEMORY tables are stored in memory. They use hash indexes by default, which makes them very fast, 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 server restarts.

This example shows how you might create, use, and remove a MEMORY table:

mysql> 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;

MEMORY tables have the following characteristics:

Space for MEMORY tables 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. MEMORY tables also have none of the problems commonly associated with deletes plus inserts in hashed tables.
MEMORY tables can have up to 64 indexes per table, 16 columns per index and a maximum key length of 3072 bytes.
The MEMORY storage engine supports both HASH and 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 7.5.3, “How MySQL Uses Indexes”.
If a 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 this problem.
MEMORY tables can have nonunique keys. (This is an uncommon feature for implementations of hash indexes.)
Columns that are indexed can contain NULL values.
MEMORY tables use a fixed-length row-storage format. Variable-length types such as VARCHAR are stored using a fixed length.
MEMORY tables cannot contain BLOB or TEXT columns.
MEMORY includes support for AUTO_INCREMENT columns.
MEMORY supports INSERT DELAYED. See Section 12.2.5.2, “INSERT DELAYED Syntax”.
Non-TEMPORARY MEMORY tables are shared among all clients, just like any other non-TEMPORARY table.
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:
- MEMORY tables are never converted to disk tables. If an internal temporary table becomes too large, the server automatically converts it to on-disk storage, as described in Section 7.8.4, “How MySQL Uses Internal Temporary Tables”.
- The maximum size of MEMORY tables is limited by the max_heap_table_size system variable, which has a default value of 16MB. To have larger (or smaller) MEMORY tables, you must change the value of this variable. The value in effect for CREATE TABLE is the value used for the life of the table. (If you use ALTER TABLE or TRUNCATE TABLE, the value in effect at that time becomes the new maximum size for the table. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value.) You can set the size for individual tables as described later in this section.
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 rows that have been deleted will be re-used for new rows only within the same table. To free up the memory used by rows that have been deleted, use ALTER TABLE ENGINE=MEMORY to force a table rebuild.

To free all the memory used by a MEMORY table when you no longer require its contents, you should execute DELETE or TRUNCATE TABLE to remove all rows, or remove the table altogether using DROP TABLE.
If you want to populate a MEMORY table when the MySQL server starts, you can use the --init-file option. For example, you can put statements such as INSERT INTO ... SELECT or LOAD DATA INFILE into this file to load the table from a persistent data source. See Section 5.1.2, “Server Command Options”, and Section 12.2.6, “LOAD DATA INFILE Syntax”.
A server's MEMORY tables become empty when it is shut down and restarted. However, if the server is a replication master, its slave are not aware that these tables have become empty, so they returns out-of-date content if you select data from these tables. To handle this, 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 automatically, thus synchronizing the slave to the master again. Note that even with this strategy, the slave still has outdated data in the table during the interval between the master's restart and its first use of the table. However, if you use the --init-file option to populate the MEMORY table on the master at startup, it ensures that this time interval is zero.
The memory needed for one row in a MEMORY table is calculated using the following expression:
```
SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) × 4)
+ SUM_OVER_ALL_HASH_KEYS(sizeof(char*) × 2)
+ ALIGN(length_of_row+1, sizeof(char*))
```
ALIGN() represents a round-up factor to cause the row length to be an exact multiple of the char pointer size. 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 MEMORY 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 MEMORY tables created by all clients.) The following example creates two MEMORY tables, with a maximum size of 1MB and 2MB, respectively:

mysql> 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 will revert to the server's global max_heap_table_size value if the server restarts.

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 max_heap_table_size value, which still acts as a constraint on maximum table size. For maximum flexibility in being able to use MAX_ROWS, set max_heap_table_size at least as high as the value to which you want each MEMORY table to be able to grow.

Additional Resources

A forum dedicated to the MEMORY storage engine is available at http://forums.mysql.com/list.php?92.

Previous / Next / Up / Table of Contents

User Comments

Posted by Shelby Moore on January 16 2005 8:36pm

[Delete] [Edit]

I think the slowdown documented above is entirely unnecessary and the slowdown is not directly correlated to cardinality:

"...The degree of slowdown is proportional to the degree of duplication...You can use a BTREE index to avoid this problem."

Only a very simple "MTF" optimization needs to be made to the HEAP storage engine:

http://bugs.mysql.com/bug.php?id=7817

BTREEs are much slower than hashing (about 5 to 6 times at least), and are necessary only when non-equality (range) indexing is required. See the research paper quoted at above link for benchmarks.

So consider the above advice to use BTREEs to solve performance issues as incorrect because they are 5 - 6 times slower. BTREEs are a way to get 5 - 6 times slower performance than a correctly optimized HASH indexing. BTREEs may be faster in some cases than an *UN*optimized HASH index.

As for the issue of slowdown correlation to cardinality, see comment "16 Jan 9:32pm" in above link.

Posted by Shelby Moore on January 16 2005 1:24am

[Delete] [Edit]

Current HASH key implementation is unoptimized and much slower than it needs to be for the case where most queries result in non-match:

http://bugs.mysql.com/7936

In this case, it is possible that BTREE is faster until HASH is optimized.

Posted by Stein Haugan on March 2 2007 1:39pm

[Delete] [Edit]

Insertion into HASH indexed columns is somewhat vulnerable to degenerate cases of "bad" data sets, which can cause insertion to be painfully slow (two orders of magnitude slower than a "normal" data set). See the examples (with suggestions for application-level fixes) below:

Create a table n:

mysql> create temporary table n (n int unsigned auto_increment primary key);

mysql> insert into n select NULL from SQ_SIMILAR2; -- a 1-million-row-table
Query OK, 1115156 rows affected (4.40 sec)
Records: 1115156 Duplicates: 0 Warnings: 0

Ok, now we have numbers 1-1e6 in table n.

mysql> create temporary table sq (sq int unsigned, key sq) engine memory;

Ok, now we're set. Look at the timings in the two insert statements:

mysql> insert into sq select floor(n/64*1024)*n from n;
Query OK, 1115156 rows affected, 65535 warnings (2.80 sec)
Records: 1115156 Duplicates: 0 Warnings: 1098773

mysql> truncate table sq;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into sq select floor(n/(64*1024-1))*n from n;
Query OK, 1115156 rows affected (2 min 59.34 sec)
Records: 1115156 Duplicates: 0 Warnings: 0

In other words, a slow-down factor of 64! Obviously something weird is
going on that throws the adaptive cache algorithm to the ground!

Part of the problem can be solved by e.g. random reordering before
inserts (after truncating the table, of course):

mysql> insert into sq select floor(n/(64*1024-1))*n from n order by rand();
Query OK, 1115156 rows affected (52.64 sec)
Records: 1115156 Duplicates: 0 Warnings: 0

Now we're down to "only" a factor of about 20. But we can do even better:

mysql> insert into sq select floor(n/(64*1024-1))*n from n order by n desc;
Query OK, 1115156 rows affected (2.60 sec)
Records: 1115156 Duplicates: 0 Warnings: 0

Whee! Great.

Our actual data were a little different. The table SQ_SIMILAR2 contains
1.1 million non-unique numbers - about 180,000 distinct values between 1
and 1.1 million - in a, well, special [by accident] order. Here are some
timings (table sq is truncated before each insert):

mysql> insert into sq select SQ_SIMILAR2 from SQ_SIMILAR2;
Query OK, 1115156 rows affected (4 min 39.07 sec)
Records: 1115156 Duplicates: 0 Warnings: 0

I.e. a little worse than the test case above. Random ordering seems a tiny
bit worse. And ordering in ascending order is really, really bad:

mysql> insert into sq select SQ_SIMILAR2 from SQ_SIMILAR2 order by SQ_SIMILAR2;
Query OK, 1115156 rows affected (8 min 31.24 sec)
Records: 1115156 Duplicates: 0 Warnings: 0

Yikes, a slow-down factor of 182 compared to the floor(n/64*1024)*n
example above. Sorting in descending order gets back within the realm of
the reasonable again:

mysql> insert into sq select SQ_SIMILAR2 from SQ_SIMILAR2 order by SQ_SIMILAR2 $
Query OK, 1115156 rows affected (4.54 sec)
Records: 1115156 Duplicates: 0 Warnings: 0

But with non-unique data, can you do better? Try this:

mysql> insert into sq select distinct SQ_SIMILAR2 from SQ_SIMILAR2;
Query OK, 181272 rows affected (0.61 sec)
Records: 181272 Duplicates: 0 Warnings: 0

mysql> insert into sq select SQ_SIMILAR2 from SQ_SIMILAR2;
Query OK, 1115156 rows affected (1.50 sec)
Records: 1115156 Duplicates: 0 Warnings: 0

Alltogether only 2.11 sec, half the time of the descending sort order,
although further table manipulations are necessary to delete the spurious
duplicates that have been created.

Posted by Eric Walker on December 6 2007 8:45am

[Delete] [Edit]

When joining a column in a MEMORY table against one in an InnoDB table, the kind of indexes on the columns is important.

In my case, when a column on a MEMORY table was of type HASH and the corresponding column in the InnoDB table of type BTREE, the query optimizer was not able to make use of the indexes and queries were taking a long time. A fix in this instance was to convert the default HASH index on the MEMORY table column to BTREE.

Add your own comment.

Top / Previous / Next / Up / Table of Contents

13.4. The MEMORY (HEAP) Storage Engine

Additional Resources

User Comments

13.4. The `MEMORY` (`HEAP`) Storage Engine