MySQL 5.5 Reference Manual

13.9. The `MEMORY` Storage Engine
Prev	Chapter 13. Storage Engines	Next

13.9. The `MEMORY` 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.

Table 13.12. MEMORY Storage Engine Features

Storage limits	RAM	Transactions	No	Locking granularity	Table
MVCC	No	Geospatial data type support	No	Geospatial indexing support	No
B-tree indexes	Yes	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 product ^[c]Implemented in the server, rather than in the storage product

Important

Developers looking to deploy applications that use the MEMORY storage engine should consider whether MySQL Cluster is a better choice. A typical use case for the MEMORY engine involves these characteristics:

Operations such as session management or caching
In-memory storage for fast access and low latency
A read-only or read-mostly data access pattern (limited updates)

However, 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. Also, MEMORY does not preserve table contents across server restarts.

MySQL Cluster offers the same features as the MEMORY engine with higher performance levels, and provides additional features not available with MEMORY:

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
Support for variable-length data types (including BLOB and TEXT) not supported by MEMORY

For a white paper with more detailed comparison of the MEMORY storage engine and MySQL Cluster, see Scaling 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 how existing MEMORY users can migrate to MySQL Cluster.

The MEMORY storage engine associate 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;

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.3.1, “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.4.3.3, “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.

Prev	Up	Next
13.8.2. `MERGE` Table Problems	Home	13.10. The `EXAMPLE` Storage Engine

docs.sk

comprehensive documentation repository

13.9. The `MEMORY` Storage Engine

Additional Resources