Movatterモバイル変換

Table 18.4 MEMORY Storage Engine Features

• When to Use MEMORY or NDB Cluster

• Partitioning

• Performance Characteristics

• Characteristics of MEMORY Tables

• DDL Operations for MEMORY Tables

• Indexes

• User-Created and Temporary Tables

• Loading Data

• MEMORY Tables and Replication

• Managing Memory Use

• Additional Resources

Developers looking to deploy applications that use theMEMORY storage engine for important, highly available, or frequently updated data should consider whether NDB Cluster is a better choice. A typical use case for theMEMORY engine involves these characteristics:

NDB Cluster offers the same features as theMEMORY engine with higher performance levels, and provides additional features not available withMEMORY:

MEMORY tables cannot be partitioned.

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 forMEMORY tables, they are not necessarily faster thanInnoDB 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 ofMEMORY tables from multiple sessions.

Depending on the kinds of queries performed on aMEMORY 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.

TheMEMORY storage engine does not create any files on disk. The table definition is stored in the MySQL data dictionary.

MEMORY tables have the following characteristics:

To create aMEMORY table, specify the clauseENGINE=MEMORY on theCREATE TABLE statement.

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 for single-value lookups, and very useful for creating temporary tables. However, when the server shuts down, all rows stored inMEMORY tables are lost. The tables themselves continue to exist because their definitions are stored in the MySQL data dictionary, but they are empty when the server restarts.

This example shows how you might create, use, and remove aMEMORY 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 ofMEMORY tables is limited by themax_heap_table_size system variable, which has a default value of 16MB. To enforce different size limits forMEMORY tables, change the value of this variable. The value in effect forCREATE TABLE, or a subsequentALTER TABLE orTRUNCATE TABLE, is the value used for the life of the table. A server restart also sets the maximum size of existingMEMORY tables to the globalmax_heap_table_size value. You can set the size for individual tables as described later in this section.

TheMEMORY storage engine supports bothHASH andBTREE indexes. You can specify one or the other for a given index by adding aUSING 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, seeSection 10.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.

If aMEMORY 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 aBTREE 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 containNULL values.

MEMORY table contents are stored in memory, which is a property thatMEMORY tables share with internal temporary tables that the server creates on the fly while processing queries. However, the two types of tables differ in thatMEMORY tables are not subject to storage conversion, whereas internal temporary tables are:

To populate aMEMORY table when the MySQL server starts, you can use theinit_file system variable. For example, you can put statements such asINSERT INTO ... SELECT orLOAD DATA into a file to load the table from a persistent data source, and useinit_file to name the file. SeeSection 7.1.8, “Server System Variables”, andSection 15.2.9, “LOAD DATA Statement”.

When a replication source server shuts down and restarts, itsMEMORY tables become empty. To replicate this effect to replicas, the first time that the source uses a givenMEMORY table after startup, it logs an event that notifies replicas that the table must be emptied by writing aTRUNCATE TABLE statement for that table to the binary log. When a replica server shuts down and restarts, itsMEMORY tables also become empty, and it writes aTRUNCATE TABLE statement to its own binary log, which is passed on to any downstream replicas.

When you useMEMORY tables in a replication topology, in some situations, the table on the source and the table on the replica can differ. For information on handling each of these situations to prevent stale reads or errors, seeSection 19.5.1.22, “Replication and MEMORY Tables”.

The server needs sufficient memory to maintain allMEMORY tables that are in use at the same time.

Memory is not reclaimed if you delete individual rows from aMEMORY 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 aMEMORY table when you no longer require its contents, executeDELETE orTRUNCATE TABLE to remove all rows, or remove the table altogether usingDROP TABLE. To free up the memory used by deleted rows, useALTER TABLE ENGINE=MEMORY to force a table rebuild.

The memory needed for one row in aMEMORY 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 thechar pointer size.sizeof(char*) is 4 on 32-bit machines and 8 on 64-bit machines.

As mentioned earlier, themax_heap_table_size system variable sets the limit on the maximum size ofMEMORY tables. To control the maximum size for individual tables, set the session value of this variable before creating each table. (Do not change the globalmax_heap_table_size value unless you intend the value to be used forMEMORY tables created by all clients.) The following example creates twoMEMORY 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 revert to the server's globalmax_heap_table_size value if the server restarts.

You can also specify aMAX_ROWS table option inCREATE TABLE statements forMEMORY 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 themax_heap_table_size value, which still acts as a constraint on maximum table size. For maximum flexibility in being able to useMAX_ROWS, setmax_heap_table_size at least as high as the value to which you want eachMEMORY table to be able to grow.

A forum dedicated to theMEMORY storage engine is available athttps://forums.mysql.com/list.php?92.