CODEWITHSUNDEEP
mysqlmemory
guy
guy

Reputation: 213

Why does switching to the MEMORY engine increases the table size?

Why does changing the engine from MyISAM to MEMORY increase the table size so much? I'm trying to change a table that contains 13 millions records to MEMORY and I have 6GB Memory, and in Myisam the size is 1.5GB and i want to be around this size using the MEMORY engine so it fits my memory limit.

I also defined this

{tmp_table_size=5.5G
max_heap_table_size=5.5G}

and I still get a "table is full" error, so my only guess is that when converting to MEMORY engine the table size needs to be around 10-15GB.

Thank you

Upvotes: 1

Views: 436

Answers (1)

Arnaud Le Blanc
Arnaud Le Blanc

Reputation: 99919

The MEMORY (or HEAP) table engine supports only the fixed-row format.

As a result a VARCHAR(255) column is stored like a CHAR(255): it consumes 255 bytes in each row, even if it contains only 10 characters, plus a few bytes for meta data (e.g. for storing the length). If the column is in UTF-8, it consumes 3 bytes per character, i.e. 3*255 per row, plus meta data.

See Physical Characteristics of MEMORY Tables at https://dev.mysql.com/doc/refman/5.5/en/memory-storage-engine.html :

MEMORY tables use a fixed-length row-storage format. Variable-length types such as VARCHAR are stored using a fixed length.

Upvotes: 1

Related Questions