Monday, June 26, 2017

Memory Storage Engine

The Memory storage engine, also known historically as the Heap storage engine as the name implies is an in memory only table that does not provide data persistence. The Memory storage engine is actually used internally by the mysql kernel when a temporary table is required.

Key features

• Very fast, in memory
• Non transactional
• Support the hash index by default
• Btree indexes are also supported
• Ideal for primary key lookups

Limitations

• Does not support transactions
• Table level locking on DML and DDL statements
• Data is not persistent
• Does not support TEXT/BLOB data types
• Fixed row width
• No ability to limit the total amount of memory for all Memory tables

Important Parameters

• max_heap_table_size – Defines the maximum size of a single Memory table.
• tmp_table_size – Defines the maximum size of the table when used for internally temporary tables.
• init_file – Defines a SQL file of commands that are executed when the MySQL instance is started. Used as a means to seed Memory tables.

Memory tables are confined to a maximum table size as defined by the max_heap_table_size system variable. In the following example we can demonstrate the table full error message, and correct the maximum size, based on our size of the comparison Fixed MyISAM table size to show the data in a Memory table

References