MySQL--the MEMORY Storage engine--official documentation

Original address: http://dev.mysql.com/doc/refman/5.7/en/memory-storage-engine.html

The MEMORY Storage Engine (formerly known as HEAP ) creates special-purpose tables with contents that is stored in memory. Because the data is vulnerable to crashes, hardware issues, or power outages, with use these tables as temporary As or read-only caches for data pulled from the other tables.

Table 15.4 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	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 this use MEMORY of the 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 MEMORY tables 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 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 MEMORY of the storage engine and MySQL Cluster, see Scaling Web Services wi th MySQL Cluster:an alternative to the MySQL Memory Storage Engine. This white paper includes a performance study of the both technologies and a step-by-step guide describing how existing users can migrate to MySQL Cluster.

Performance characteristics

MEMORYPerformance is constrained by contention resulting from single-thread execution and table lock overhead when processing up Dates. This limits scalability if load increases, particularly for statement mixes that include writes.

Despite the in-memory processing For memory  tables, they is 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 multiple sessions.

Depending on the kinds of queries performed in a MEMORY table, you might create indexes as either the default hash data str Ucture (for looking up single values based to 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 is more efficient.

Physical characteristics of MEMORY Tables

MEMORYthe storage Engine associates each table with one disk file, and which stores the table definition (not the data). The file name begins with the table name and have an extension of .frm .

MEMORYTables has the following characteristics:

• Space for MEMORY tables are 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 is put in a linked list and is reused when you insert new data into the table. MEMORYtables also has 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 is VARCHAR stored using a fixed length.

• MEMORYTables cannot contain BLOB or TEXT columns.

• MEMORYIncludes support for AUTO_INCREMENT columns.

• non- TEMPORARY MEMORY Tables is shared among all clients, and just like any other non- TEMPORARY table.

DDL Operations for MEMORY Tables

To create a MEMORY table, specify the clause on the ENGINE=MEMORY CREATE TABLE statement.

CREATE TABLE T (i INT) ENGINE = MEMORY;

As indicated by the engine name, MEMORY tables is stored in memory. They use hash indexes by default, which makes them very fast for single-value lookups, and very useful for creating tempor ary tables. However, when the server shuts down, all rows stored in MEMORY tables is lost. The tables themselves continue to exist because their definitions .frm is stored in files on disk, but they is empty wh En the server restarts.

This example shows how to 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;   
   

The maximum size MEMORY max_heap_table_size of tables is limited by the system variable, which have a default value of 16MB. To enforce different size limits MEMORY for tables, change the value of this variable. CREATE TABLEthe value of effect for, 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 of existing MEMORY tables to the global max_heap_table_size value. You can set the size of individual tables as described later in this section.

Indexes

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 sections 8.3.1, "How MySQL Uses Indexes".

MEMORYTables can has up to indexes per table, columns per index and a maximum key length of 3072 bytes.

If a MEMORY table hash index has a high degree of key duplication (many index entries containing the same value), updates t o the table, affect key values and all deletes is significantly slower. The degree of this slowdown are proportional to the degree of duplication (or, inversely proportional to the index cardinal ity). You can use a BTREE index to avoid this problem.

MEMORYTables can have nonunique keys. (This is a uncommon feature for implementations of hash indexes.)

Columns that is indexed can contain NULL values.

user-created and temporary Tables

MEMORYTable contents was stored in memory, which was a property that MEMORY tables share with internal temporary tables that the S Erver creates on the fly while processing queries. However, the types of tables differ in that MEMORY tables is not subject to storage conversion, whereas internal tempor ary Tables is:

• If an internal temporary table becomes too large, the server automatically converts it to on-disk storage, as described in Section 8.4.4, "How MySQL Uses Internal temporary Tables".

• user-created MEMORY tables is never converted to disk tables.

Loading Data

To populate a MEMORY table when the MySQL server is starts, you can use the --init-file option. For example, you can put statements such as INSERT INTO ... SELECT or into this file to load the table from LOAD DATA INFILE a persistent data source. See sections 5.1.3, "Server Command Options", and section 13.2.6, "LOAD DATA INFILE Syntax".

MEMORY Tables and Replication

A server ' sMEMORYTables become empty when it was shut down and restarted. If the server is a replication master, it slaves is not aware that these tables has become empty, so you see Out-of-dat E Content If you select data from the tables on the slaves. To synchronize Master and slaveMEMORYTables, when aMEMORYTable is used on a master for the first time since it was started, aDELETEStatement is written to the master's binary log, to empty the table on the slaves also. The slave still have outdated data in the table during the interval between the master's restart and its first use of the T Able. To avoid this interval if a direct query to the slave could return stale data, use the--init-fileOption to populate theMEMORYTable on the master at startup.

Managing Memory Use

The server needs sufficient memory to maintain all tables, that is in use at the MEMORY same time.

Memory is not reclaimed if you delete individual rows from a MEMORY table. Memory is reclaimed only if the entire table is deleted. Memory that's previously used for deleted rows are re-used for new rows within the same table. To free all of the memory used MEMORY by a table when longer require it contents, execute DELETE or to TRUNCATE TABLE remove all Rows, or remove the table altogether using DROP TABLE . To free up the memory used by deleted rows, use to force ALTER TABLE ENGINE=MEMORY a table rebuild.

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 being an exact multiple of the char pointer size. are sizeof(char*) 4 on 32-bi T 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. The Global max_heap_table_size  value unless you intend the value To is used Formemory  tables created by all clients.) The following example creates Two memory  tables, with a maximum size of 1MB and 2MB , respectively:

SET max_heap_table_size = 1024*1024;CREATE TABLE t1 (id INT, UNIQUE(id)) ENGINE = MEMORY;SET max_heap_table_size = 1024*1024*2;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 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 does enable the table to grow beyond max_heap_table_size the value, which still acts as a constraint on maximum table size. For maximum flexibility with being able to use MAX_ROWS , set at least as high as the max_heap_table_size value to which you want each MEMORY TA Ble to is able to grow.

Additional Resources

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

MySQL--the MEMORY Storage engine--official documentation