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