How much memory will a MEMORY table take up?

Question

Let's say I have a MyISAM table with a data length of 4.8GB and an index length of 6.2GB. So, a total data size of eleven gig. How much memory would this require, were I to convert it to a MEMORY table? 11 gig, or more?

Answer 1

The exact memory requirement of a row is calculated from the following formula:

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*)) 

[src]

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.

So, on a 64-bit machine, replace sizeof(char*) with 8.

You can get an estimate of the length_of_row from the Information Schema:

SELECT TABLE_ROWS, AVG_ROW_LENGTH FROM information_schema.tables WHERE  table_schema='foo' AND table_name='bar';

Then you add up all your BTREE keys and then HASH keys. Note that it might be worth it space-wise to convert any keys to HASH, as they require less memory.

I was going to mention the limitation of maximum MEMORY size dependent on max_heap_table_size, but gbn beat me to it.

Answer 2

It can't be a MEMORY table (selective quote below) if it exceeds "max_heap_table_size". This is max 4GB for 32 bit

The maximum size of MEMORY tables is limited by the max_heap_table_size system variable, which has a default value of 16MB. To enforce different size limits for MEMORY tables, change the value of this variable

You can set this per table by setting max_heap_table_size per session.

But 12GB or so is a lot of memory for a caching table...

Answer 3

@gbn and @DTest provided great answers already on using MEMORY tables in terms of indexes and size limits.

Here is yet another perspective to keep in mind:

The MEMORY storage engine

• uses full table locking for INSERTs, UPDATEs, and DELETEs
• Cannot perform concurrent INSERTs
• uses the hash indexes instead of BTREE indexes by default
• can use BTREEs indexes, but must be explicitly specified at CREATE TABLE time
• has no transaction support
• Single row queries are just great against MEMORY tables, especially using HASH indexes
• Ranged queries and sequential access are just horrific unless you use BTREE explicitly (more memory consumption required)

Even though you have data in RAM, mysqld will always hit the .frm file to check for the table existing as a reference point, thus always incurring a little disk I/O. Proportionally, heavy access to a MEMORY storage engine table will have noticeable disk I/O.

You must also remember to strike a proper balance of MEMORY table usage with

• Database caches
  • MyISAM Key Cache
  • InnoDB Buffer Pool)
• OS caches
• OS operation